Unable to Load the CSV file, using BCP command in SQL 2008 1

[VIJSQL01]

Hi,

I am trying to load large data which is available in .CSV file format. I am getting below error,

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 20. Verify that the field terminator and row terminator are specified correctly.
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)".

Below is the BCP utility i am using,

BULK INSERT dbo.CSI_ZIPCODE_BE
FROM '\\10.48.164.48\Datamove1\REAS\Case Schiller Index\Load Test\CSI_Bulk_Export_by_ZIP_201312.CSV'
WITH
(

FIELDTERMINATOR = ','
, ROWTERMINATOR = '\n');

I tried to skip the first row as it contains header by using following query. Still the same issue occurs,

BULK INSERT dbo.CSI_ZIPCODE_BE
FROM '\\10.48.164.48\Datamove1\REAS\Case Schiller Index\Load Test\CSI_Bulk_Export_by_ZIP_201312.CSV'
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ','
, ROWTERMINATOR = '\n');

Also would like to mention here that, the total length of the data in the column header for the column #20 is 16 and i have defined the data type for that column as varchar(100).

Please let me know, what might be the problem for this.

 

[gmmastros]

Since your error message indicates that the data is too large, I suspect you have a problem with your row terminator.

I suggest that you open the file with a hex editor and examine the actual row terminator.

\n is carriage return and line feed. Your actual row terminator may be just one of those. For example, it could by just a carriage return in which case you could use \r instead. It could also just be a line feed which is a little more problematic.

Anyway... I suggest you change:

ROWTERMINATOR = '\r'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VIJSQL01]

Great! thanks a ton for providing the solution. This works for me.
I tried to use the same BCP command again today(having \n as row terminator), I was able to load the data without any issues.
This actually surprised me and wondering why it wasn't happening yesterday and why is it today. Totally confused.