Bulk load data conversion error (truncation)

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

30,Bill,Worthy,sales,,709888499,bat@bat.com,,"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:leadsleads.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

Answer

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

Advertisement

Answer