批量加载数据转换错误（截断） [英] Bulk load data conversion error (truncation)

问题描述

我收到此错误

Bulk load data conversion error (truncation) for row 1, column 12 (is_download)

这里是csv ...只有一行

here is the csv...it only has one row

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

推荐答案

它在注释字段中拾取逗号作为分隔符，因为分隔符不一致。最佳解决方案是确保所有字段都用双引号包起来，并将 FIELDTERMINATOR 设置为'，'。或者，用注释中不太可能出现的逗号替换逗号（如〜）并设置 FIELDTERMINATOR ='〜' 。

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 = '~'.

这篇关于批量加载数据转换错误（截断）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！