SQL 批量加载数据转换错误（截断）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7520731/
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 (truncation)
提问by Matt Elhotiby
I am getting this error
我收到此错误
Bulk load data conversion error (truncation) for row 1, column 12 (is_download)
here is the csv...it only has one row
这是 csv...它只有一行
30,Bill,Worthy,sales,,709888499,[email protected],,"Im a a people person., to work together for this new emerging env.HTTP://applesoftware.com","Bill and Son of Co","Contact Us: Contact Form",0
here is my bulk insert statement...
这是我的批量插入语句...
SE SalesLogix
GO
CREATE TABLE CSVTemp
(id INT,
firstname VARCHAR(255),
lastname VARCHAR(255),
department VARCHAR(255),
architecture VARCHAR(255),
phone VARCHAR(255),
email VARCHAR(255),
download VARCHAR(255),
comments VARCHAR(MAX),
company VARCHAR(255),
location VARCHAR(255),
is_download VARCHAR(255)
)
GO
BULK
INSERT CSVTemp
FROM 'c:\leads\leads.csv'
WITH
(
DATAFILETYPE = 'char',
BATCHSIZE = 50,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTemp
GO
The problem is most of the time it works great but in some situations (this being one of them) I get the errors
问题是大部分时间它都很好用，但在某些情况下（这是其中之一）我得到了错误
ANy ideas on what is causing this record to have this error
关于导致此记录出现此错误的原因的任何想法
采纳答案by Wil
It's picking up the commas within the comments field as delimiters, because the delimiters are not consistent. The best solution is to insure that all fields are wrapped with double quotes and set FIELDTERMINATORto '","'. Alternately, replace the commas with something unlikely to be in the comments (like ~) and set FIELDTERMINATOR = '~'.
它将注释字段中的逗号作为分隔符，因为分隔符不一致。最好的解决方案是确保所有字段都用双引号括起来并设置FIELDTERMINATOR为'","'. 或者，将逗号替换为不太可能出现在注释中的内容（如~）和 set FIELDTERMINATOR = '~'。
回答by Aaron Bertrand
In addition to Wil's comments, it seems like it is seeing all 12 columns, so it may just be that your rowterminator is incorrect. First, make sure that the program that puts these files together is in fact putting a carriage return at the end of the last line; I've had to correct many programs where this wasn't the case. Once you are sure there is a carriage return there, you may have to experiment to see what type of carriage return it is. Sometimes it is char(10) only, sometimes char(13) only, and sometimes it may have both but be in the wrong order. So experiment with:
除了 Wil 的评论之外，它似乎看到了所有 12 列，因此可能只是您的 rowterminator 不正确。首先，确保将这些文件放在一起的程序实际上是在最后一行的末尾放了一个回车；我不得不纠正许多并非如此的程序。一旦您确定那里有回车符，您可能需要尝试看看它是什么类型的回车符。有时只有 char(10)，有时只有 char(13)，有时可能两者都有，但顺序错误。所以实验：
ROWTERMINATOR = '\n'
ROWTERMINATOR = '\r'
ROWTERMINATOR = '\n\r'
ROWTERMINATOR = '\r\n'
回答by itsrizi
System.Data.SqlClient.SqlException (0x80131904): Bulk load data conversion error (truncation) for row 97, column 33
System.Data.SqlClient.SqlException (0x80131904)：第 97 行第 33 列的批量加载数据转换错误（截断）
For the above error, you can check
对于上述错误，您可以检查
- Data type sizeof column(e.g. VARCHAR(255)) is if it is sufficient to import data or not.
- AND the row terminatore.g.
- ROWTERMINATOR = '0x0A'
- ROWTERMINATOR = '\n'
- ROWTERMINATOR = '\r\n'
- FIELDTERMINATOR
- Make sure the selected field terminator does not occur with in data. If there is a chance of it, then replace it with some other character e.g. | in the file.
- 列的数据类型大小（例如 VARCHAR(255)）是否足以导入数据。
- 和行终止符，例如
- ROWTERMINATOR = '0x0A'
- ROWTERMINATOR = '\n'
- ROWTERMINATOR = '\r\n'
- 现场终结者
- 确保所选字段终止符不会出现在数据中。如果有机会，则将其替换为其他字符，例如 | 在文件中。
回答by karthik babu
i had the header as first row. after i removing it, it was fine.
我有标题作为第一行。卸掉之后就好了
