SQL 批量加载数据转换错误（指定代码页的类型不匹配或无效字符）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33259735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk load data conversion error (type mismatch or invalid character for the specified codepage)
提问by slevin37
Im having an issue using a bulk insert command for bringing a .csv into a database. Here is a sample of the CSV I created
我在使用批量插入命令将 .csv 导入数据库时遇到问题。这是我创建的CSV示例
Here is the query i wrote:
这是我写的查询：
BULK INSERT TBL_Staging FROM 'C:\Users\testdata.csv' With (FieldTerminator = ',', RowTerminator= '\n', KEEPNULLS);
This is getting me the following error message(s):
这让我收到以下错误消息：
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (Id). Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (InvoiceHeaderId). Msg 4864, Level 16, State 1, Line 1
第 1 行第 1 列 (Id) 的批量加载数据转换错误（指定代码页的类型不匹配或无效字符）。消息 4864，级别 16，状态 1，第 1 行
第 2 行第 2 列 (InvoiceHeaderId) 的批量加载数据转换错误（指定代码页的类型不匹配或无效字符）。消息 4864，级别 16，状态 1，第 1 行
回答by Sev Roberts
The first row of your CSV data contains column headers, and the error message is because SQL Server is trying to insert your header row as data. Either remove the column header row or specify FIRSTROW=2
CSV 数据的第一行包含列标题，错误消息是因为 SQL Server 试图将标题行作为数据插入。删除列标题行或指定 FIRSTROW=2
BULK INSERT TBL_Staging FROM 'C:\Users\testdata.csv' With (FIRSTROW=2, FieldTerminator = ',', RowTerminator= '\n', KEEPNULLS);
For the benefit of others who might read this without seeing the asker's data - the FIRSTROW=2 parameter workaround only works if your header row really does have the same number of columns and delimiters as your data - which in this case it did - but otherwise would error.
为了其他可能在没有看到提问者数据的情况下阅读此内容的人的利益 - FIRSTROW=2 参数解决方法仅在您的标题行确实具有与您的数据相同数量的列和分隔符时才有效 - 在这种情况下确实如此 - 但否则会出错。
Edit:I notice that your data also has some values using a double-quote as a text qualifier around values containing a comma, so you are likely to see an error even after removing/skipping the header row. SQL Server BULK INSERT is notorious for not supporting CSV text qualifiers, leading to several different workarounds over the years, including but not limited to:
编辑：我注意到您的数据也有一些值，使用双引号作为包含逗号的值周围的文本限定符，因此即使在删除/跳过标题行后，您也可能会看到错误。SQL Server BULK INSERT 因不支持 CSV 文本限定符而臭名昭著，多年来导致了几种不同的解决方法，包括但不限于：
- Using a field delimiter other than the comma, one which doesn't occur in your data, such as |
- Specifying a 'format file' for the bulk insert
- 使用逗号以外的字段分隔符，该分隔符不会出现在您的数据中，例如 |
- 为批量插入指定“格式文件”
For more see these existing posts:
有关更多信息，请参阅这些现有帖子：
Bulk Insert Correctly Quoted CSV File in SQL Server
回答by Dese
Have you tried to remove the header from your file ? The way I see it, it treats the header line as a data line and returns an error because it is not in the right format. If the header is the problem, then preprocess your files to remove it, because:
您是否尝试从文件中删除标题？在我看来，它将标题行视为数据行并返回错误，因为它的格式不正确。如果标题是问题所在，则预处理您的文件以将其删除，因为：
"Skipping headers is not supported by the BULK INSERT statement"
“BULK INSERT 语句不支持跳过标题”
source https://msdn.microsoft.com/en-us/library/ms188365.aspx
