sql server - bulk insert error

Question

I am using bulk insert and getting below error:

Note: The data in the load file is not beyong the configured column length

Running Command:

bulk insert load_data from 'C:\temp\dataload\load_file.txt' with (firstrow = 1, fieldterminator = '0x09', rowterminator = '\n',MAXERRORS = 0, ERRORFILE = 'C:\temp\dataload\load_file')

Contents of load file:

user_name   file_path   asset_owner   city      import_date    
admin       C:\         admin         toronto   04/12/2012

Error:

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 6 (validated).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Is column 6 by chance the last field in the row? It sounds like the field or rowterminator may be incorrect. I've seen this in the past. Since it doesn't find the terminator, it thinks the field/row extends on to the next field or row. Just a guess. — brian
You don't appear to have 6 columns... Also, formatting is a little different than you might otherwise expect here on SO, so I tweaked your data so that it lines up... which unfortunately resulted in the delimiter tabs going missing (although personally I prefer non-whitespace characters for this anyways). — Clockwork-Muse

Bruce Bruce · Accepted Answer · 2012-12-11T20:05:17

The number of columns was incorrect. I had recently changed the table schema but forgotten to do a refresh on the table.

sql server - bulk insert error

Running Command:

Contents of load file:

Error:

2 Answers