Bulk load data conversion error (truncation)

Question

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,[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

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

Solution 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:

ROWTERMINATOR = '\n'
ROWTERMINATOR = '\r'
ROWTERMINATOR = '\n\r'
ROWTERMINATOR = '\r\n'

Solution 3

System.Data.SqlClient.SqlException (0x80131904): Bulk load data conversion error (truncation) for row 97, column 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.
• AND the row terminator e.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.

Solution 4

i had the header as first row. after i removing it, it was fine.

View more solutions

Comments

[Wil]

Often if the rowterminator is incorrect or inconsistent it can cause it to combine the rows until it hits the recognized terminator or even eof. I've had that happen before.

[laughsloudly]

The ROWTERMINATOR of 0x0A did it for me, not the \n or \r combinations.

[Tim Sanders]

Searching SO for awhile now. The '0x0A' was the one that did it for me. This solution provides multiple solutions related to the error and would potentially solve the error.