Having an issue with BCP Command

[VIJSQL01]

hi,

I am trying to load a table in SQL 2008 by using a data present in .csv file by using BCP command. I am unable to do this, as it is throwing following 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 15. 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)".

But as an workaround, i opened the same file in excel and saved as .csv again from my end. After doing this, i am able to load the data to my table.

I dont understand, why the hell it should throw an error message, when i am trying to load the data initially. Why is not, second time. The file which i am trying to load for the first time, is the Output of SAS program, which is run with Linux OS.

Can you please help me to identify this problem? what am i doing wrong here?

Thanks in advance for your help.

 

[stefanhei]

Hi,
in Linux and other Unix-like OSes a line is terminated by linefeed (\n), in Windows by Carriage return + Linefeed (\r\n). When you opened the file with Excel and saved it again the line terminators were changed to the format Windows-utilities expect.
You'll have to create a format file for bcp that specifies the row terminator as \n to overcome this issue.

 

[VIJSQL01]

hi,

Thanks for the reply. Unfortunately, my issue is not yet resolved with the solution provided.

Infact, i am already using-

\n

as the line terminator. I am giving you the BCP command which i am using for the loading, for your reference. Hope, this will help you to look into the problem more specifically.

SET @SQLBulkInsert_State = 'BULK INSERT dbo.REAS_State_BE FROM ''\\10.48.164.48\Datamove1\REAS_QA_DATA\REAS'+substring(@ProcessingMonth,3,4) +'\HPI_Bulk_Export_by_STATE_'+substring(@ProcessingMonth,1,6)+'.csv'' 
WITH (FirstRow = 1, FIELDTERMINATOR = '','''+',ROWTERMINATOR = ''\n'''+')'

As always, thanks for providing the help.

 

[VIJSQL01]

between, i was wondering, according to above suggestion, how to create format file for bcp command?
Can anybody throw some light on this?

 

[gmmastros]

Creating a format file (with examples):

http://msdn.microsoft.com/en-us/library/ms191516.aspx

-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]

Thank you. one more help please......i am little confused, which option to use among the below. I have .csv file to load the data and the data types are varchar in the table.

A. Creating a non-XML format file for native data

B. Creating a non-XML format file for character data

C. Creating a non-XML format file for Unicode native data

D. Creating a non-XML format file for Unicode character data

 

[VIJSQL01]

hi ,

i am using following syntax to create a format file.

bcp REAS.REAS_STATE_BE_LinuxTest format nul -c -x -f someFile..xml -T

But its throwing me following error. i dont understand. please help.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

 

[djj55]

Does your filename need to be in quotes? I have not done this for a while so I do not remember.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!