BULK INSERT 后特殊字符显示不正确 [英] Special characters displaying incorrectly after BULK INSERT

问题描述

我正在使用 BULK INSERT 导入 CSV 文件.CSV 文件中的一列包含一些包含分数的值(例如 1m½f).

I'm using BULK INSERT to import a CSV file. One of the columns in the CSV file contains some values that contain fractions (e.g. 1m½f).

我不需要对分数进行任何数学运算，因为这些值仅用于显示目的，所以我将列设置为 nvarchar.BULK INSERT 有效，但是当我查看 SQL 中的记录时，分数已被替换为分符号 (¢)，因此显示的文本为 1m¢f.

I don't need to do any mathematical operations on the fractions, as the values will just be used for display purposes, so I have set the column as nvarchar. The BULK INSERT works but when I view the records within SQL the fraction has been replaced with a cent symbol (¢) so the displayed text is 1m¢f.

我很想了解为什么会发生这种情况以及有关如何解决该问题的任何想法.BULK INSERT 命令是:

I'm interested to understand why this is happening and any thoughts on how to resolve the issue. The BULK INSERT command is:

BULK INSERT dbo.temp FROM 'C:Tempfile.csv' 
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '
' );

推荐答案

您需要使用 CODEPAGE = 'ACP' 来BULK INSERT，它从 Windows 转换字符串数据代码页 1252 到 SQL Server 代码页.

You need to BULK INSERT using the CODEPAGE = 'ACP', which converts string data from Windows codepage 1252 to SQL Server codepage.

BULK INSERT dbo.temp FROM 'C:Tempfile.csv' 
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '
', CODEPAGE = 'ACP');

如果您要在足够新的 SQL Server 版本上引入 UTF-8 数据:

If you are bringing in UTF-8 data on a new enough version of SQL Server:

[...] , CODEPAGE = '65001');

您可能还需要指定 DATAFILETYPE = 'char|native|widechar|widenative'.

这篇关于BULK INSERT 后特殊字符显示不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！