SQL 第 1 行第 4 列（年份）的批量加载数据转换错误（指定代码页的类型不匹配或无效字符）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13056929/
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) for row 1, column 4 (Year)
提问by zoe
I'm getting the conversion error when I try to import a text file to my database. Below is the error message I received:
当我尝试将文本文件导入我的数据库时出现转换错误。以下是我收到的错误消息：
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year).
第 1 行第 4 列（年份）的批量加载数据转换错误（指定代码页的类型不匹配或无效字符）。
Here is my query code:
这是我的查询代码：
CREATE TABLE Students
(
StudentNo Integer NOT NULL Primary Key,
FirstName VARCHAR(40) NOT NULL,
LastName VARCHAR(40) NOT NULL,
Year Integer,
GPA Float NULL
);
Here is the sample data from text file:
这是来自文本文件的示例数据：
100,Christoph,Van Gerwen,2011
101,Anar,Cooke,2011
102,Douglis,Rudinow,2008
I think I know what the problem is..Below is my bulk insert code:
我想我知道问题是什么..下面是我的批量插入代码：
use xta9354
bulk insert xta9354.dbo.Students
from 'd:\userdata\xta9_Students.txt'
with (fieldterminator = ',',rowterminator = '\n')
With the sample data, there is no ',' after the Year attribute even tho there is still another attribute Grade after the Year which is NULL
对于示例数据，在 Year 属性之后没有“,”，即使在 Year 之后还有另一个属性 Grade 为 NULL
Can someone please tell me how to fix this?
有人可以告诉我如何解决这个问题吗？
回答by Kermit
Try using a format filesince your data file only has 4 columns. Otherwise, try OPENROWSETor use a staging table.
尝试使用格式文件，因为您的数据文件只有 4 列。否则，请尝试OPENROWSET或使用临时表。
myTestFormatFiles.Fmtmay look like:
myTestFormatFiles.Fmt可能看起来像：
9.0 4 1 SQLINT 0 3 "," 1 StudentNo "" 2 SQLCHAR 0 100 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 100 "," 3 LastName SQL_Latin1_General_CP1_CI_AS 4 SQLINT 0 4 "\r\n" 4 Year "
(source: microsoft.com)
（来源：microsoft.com）
This tutorialon skipping a column with BULK INSERTmay also help.
Your statement then would look like:
您的语句将如下所示：
USE xta9354
GO
BULK INSERT xta9354.dbo.Students
FROM 'd:\userdata\xta9_Students.txt'
WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt')
回答by Roger
In my case, I was dealing with a file that was generated by hadoop on a linux box. When I tried to import to sql I had this issue. The fix wound up being to use the hex value for 'line feed' 0x0a. It also worked for bulk insert
就我而言，我正在处理一个由 linux 机器上的 hadoop 生成的文件。当我尝试导入到 sql 时，我遇到了这个问题。最终的修复是使用十六进制值作为“换行符”0x0a。它也适用于批量插入
bulk insert table from 'file'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a')
回答by PPJN
We use the bulk insert as well. The file we upload is sent from an external party. After a while of troubleshooting, I realized that their file had columns with commas in it. Just another thing to look for...
我们也使用批量插入。我们上传的文件是从外部发送的。经过一段时间的故障排除后，我意识到他们的文件中有包含逗号的列。只是另一件事要寻找......
回答by Perry
Added MSSQLSERVER full access to the folder, diskadmin and bulkadmin server roles.
添加了 MSSQLSERVER 对文件夹、diskadmin 和 bulkadmin 服务器角色的完全访问权限。
In my c# application, when preparing for the bulk insert command,
在我的 c# 应用程序中，在准备批量插入命令时，
string strsql = "BULK INSERT PWCR_Contractor_vw_TEST FROM '" + strFileName + "' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')";
And I get this error - Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 8 (STATUS).
我收到此错误 - 第 1 行第 8 列（状态）的批量加载数据转换错误（指定代码页的类型不匹配或无效字符）。
I looked at my logfile and found that the terminator becomes ' ' instead of '\n'. The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error:
我查看了我的日志文件，发现终止符变成了 ' ' 而不是 '\n'。链接服务器“(null)”的 OLE DB 提供程序“BULK”报告了错误。提供者没有提供有关错误的任何信息：
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". Query :BULK INSERT PWCR_Contractor_vw_TEST FROM 'G:\NEWSTAGEWWW\CalAtlasToPWCR\Results\parsedRegistration.csv' WITH (FIELDTERMINATOR = ',', **ROWTERMINATOR = ''**)
So I added extra escape to the rowterminator - string strsql = "BULK INSERT PWCR_Contractor_vw_TEST FROM '" + strFileName + "' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\\n')";
所以我向行终止符添加了额外的转义符 - string strsql = "BULK INSERT PWCR_Contractor_vw_TEST FROM '" + strFileName + "' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\\n')";
And now it inserts successfully.
现在它插入成功。
Bulk Insert SQL - ---> BULK INSERT PWCR_Contractor_vw_TEST FROM 'G:\NEWSTAGEWWW\CalAtlasToPWCR\Results\parsedRegistration.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
Bulk Insert to PWCR_Contractor_vw_TEST successful... ---> clsDatase.PerformBulkInsert
回答by Ratnesh Sharma
The above options works for Google big query file also. I exported a table data to goodle cloud storage and downloaded from there. While loading the same to sql server was facing this issue and could successfully load the file after specifying the row delimiter as
上述选项也适用于 Google 大查询文件。我将表数据导出到 goodle 云存储并从那里下载。将相同的内容加载到 sql server 时遇到了这个问题，并且可以在将行分隔符指定为后成功加载文件
ROWTERMINATOR = '0x0a'
Pay attention to header record as well and specify
还要注意标题记录并指定
FIRSTROW = 2
My final block for data file export from google bigquery looks like this.
我从 google bigquery 导出数据文件的最后一个块看起来像这样。
BULK INSERT TABLENAME
FROM 'C:\ETL\Data\BigQuery\In\FILENAME.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '0x0a',--Files are generated with this row terminator in Google Bigquery
TABLOCK
)
