• Status: Solved
• Priority: Medium
• Security: Public
• Views: 453

hi

I am BULK  uploading a CSV file into q SQL 2005 table

I am getting the following error
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 2 (name_1).

The reason is that one of the fields I am uploading contains the character / in the middle of a text field  - when I remove this character the upload is fine - I wish to upload this text field with the / character - how can I do this ?

my code is

Bulk Insert #File01

from 'c:\test.csv'

with
(
Fieldterminator = ',' ,
FIRSTROW = 2,
rowterminator = '\n'
)

regards
Mike
0
MECR123
1 Solution

Technology ConsultantCommented:
In the field name there is a /?
Or in the data somewhere?
0

Commented:
You need to "escape" the unwelcome character. I believe putting a backslash in front of it will help

Hth

Mike
0

@aarontomosky

Yes, there is a "/" in the middle of the name field, the Asker said as much in the Question...

@mecr123

Is the rowterminator really just a "\n" would expect rowterminator = '\r\n'

Have you tried "openrowset" ? Often a good test to see if it is simply readable in the first instance.

What if you insert into a copy of #File01 with a very large column for name_1, or is there plenty of length ?

Cannot recall a slash being a problem, will test out a couple of things...
0

Hi,

I just whipped up a small test and didn't get the error, so, double check those lengths in #file_1 and increase the field size...

CREATE TABLE #ee_CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40))
GO

/* select the two rows below and save into c:\ee\testcsv.csv (or whatever path + file you like)
1,mark,w/lls
2,m/a/r/k,wills
*/

BULK INSERT #ee_CSVTest
FROM 'c:\ee\testcsv.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM #ee_CSVTest
GO

/* returns
1	mark	w/lls
2	m/a/r/k	wills
*/


And, double check in Excel. Double clisk on the CSV and make sure columns are aligned correctly etc... At this stage, must be field length in your table.
0

Author Commented:
I am going to accept this solution - I had to extend my field length and this has resolved the issue - The field length was mentioned in this solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.