CODEWITHSUNDEEP
sql-servercsv
Surya Ram
Surya Ram

Reputation: 91

Bulk Insert failing in SQL Server "Column is too long"

I am trying to run the following command in SQL Server and its not working:

bulk insert dbo.Sales
from 'C:\Users\sram1\Downloads\SalesFile20161103\SALECOPY.TXT'
with 
(
    FIRSTROW = 1,
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '0x0a'
)

Here is the error message that is printed:

Msg 4866, Level 16, State 1, Line 131
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

Msg 7399, Level 16, State 1, Line 131
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 131
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I looked throughout StackOverflow, and saw that I should change the rowterminator, and I have tried both '0x0a' and '\r\n'. My data is tab separated, but it appears that in some cases, the tab is 2 spaces, other times it is more, other times it is less. Is this perhaps the root of the problem? If so, how do I fix it?

Upvotes: 2

Views: 3692

Answers (1)

James K. Lowden
James K. Lowden

Reputation: 7837

My data is tab separated, but it appears that in some cases, the tab is 2 spaces

No a tab character can't be two spaces. tab separated doesn't mean "data lines up when displayed on the screen". It means there's an ASCII tab character between each column value.

If this is a one-time thing, you might import your data into Excel, and export it as tab-delimited. If it's a regular thing, you'll want to learn how to examine the file to look for nonprinting characters, change line endings, and fix up delimiters.

HTH.

Upvotes: 1

Related Questions