bulk insert with firstrow skips first record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

bulk insert with firstrow skips first record

Author

Topic

s627y
Starting Member

2 Posts

Posted - 2012-01-19 : 11:30:24

I have a CSV file like this:
*ABCD FOR:001111 #RECS=00010479 FOR:2012/01/18-2012/01/19 ON:01/19/2012 03:30:43
120118000005,0064,0000,0000,9999,0000,END
120118000029,0110,0000,0000,9999,0000,END
.....

I created a stored procedure to bulk insert like:
bulk insert dbo.temp from 'f:\documents\test.csv' with (firstrow=2, fieldterminator=',', rowterminator='\n')

I get:
Msg 4866, Level 16, State 8, Line 5
The bulk load failed. The column is too long in the data file for row 1, column 232. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 5
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

I opened the file in Programmer's Notepad, verified that line breaks are LF. I saved the CSV file in windows format, and ran the above SQL again, it ran successfully, but skips the header and the first record.

If I manually delete the header line, and ran sql without firstrow, everything is good.

How can I automate this process? What am I doing wrong? thank you!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-19 : 11:38:58

There are no commas on the header row so it doesn't consider it as one to be skipped. It will look for both the field and row terminators for any lines to be skipped. My suggestion is to copy all the data lines to a new file and don't use the firstrow parameter.

You can do something like this using the command line or calling via xp_cmdshell:

findstr "," F:\documents\test.csv >F:\documents\new_test.csv

Then BULK INSERT the new file.

s627y
Starting Member

2 Posts

Posted - 2012-01-19 : 12:01:35

Thanks Rob, now I understand why it kept failing. I will try your method. thank you.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 12:04:10

It has been sometime since I have worked with BULK INSERT, but would a format file alleviate this issue?

EDIT... Thinking deeper, I cannot see how it would.

Best wishes OP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources