批量加载数据转换错误（截断）

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,[e-mail],,"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

关于是什么导致此记录出现此错误的任何想法

3 个解决方案

#1

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 FIELDTERMINATOR to '","'. Alternately, replace the commas with something unlikely to be in the comments (like ~) and set FIELDTERMINATOR = '~'.

它将注释字段中的逗号作为分隔符，因为分隔符不一致。最好的解决方案是确保所有字段都用双引号括起来并将FIELDTERMINATOR设置为'“，”'。或者，将逗号替换为注释中不太可能的内容（例如〜），并设置FIELDTERMINATOR ='〜'。

#2

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'

#3

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 size of column(e.g. VARCHAR(255)) is if it is sufficient to import data or not.
• 列的数据类型大小（例如VARCHAR（255））是否足以导入数据。
• AND the row terminator e.g.
  • ROWTERMINATOR = '0x0A'
  • ROWTERMINATOR ='0x0A'
  • ROWTERMINATOR = '\n'
  • ROWTERMINATOR ='\ n'
  • ROWTERMINATOR = '\r\n'
  • ROWTERMINATOR ='\ r \ n'
• 和行终止符，例如ROWTERMINATOR ='0x0A'ROTERTERMINATOR ='\ 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.
  • 确保数据中没有出现所选字段终止符。如果有可能，请将其替换为其他字符，例如|在文件中。
• FIELDTERMINATOR确保数据中没有出现所选的字段终止符。如果有可能，请将其替换为其他字符，例如|在文件中。