Bulk Insert - Row Terminator Problem?

Question

Bulk Insert - Row Terminator Problem?

Richard_Hadfield

Ten Centuries

Points: 1113
More actions
April 20, 2004 at 3:35 am

#107965

My file contains the following first few lines:
01/02/2004, 19/02/2004
O2O ,BEI ,I,ABLU,ONMLRX79030 ,1,PEAK O2O ,BEI ,I,ABLU,ONMLRX79030 ,2,STANDARD
I am using the following Stored Procedure:
SET @sqlquery = 'BULK INSERT tblMyTable
FROM ' + '''' + @Path + '''' + '
WITH
(
FIELDTERMINATOR = '+ ''''+ ',' + '''' + ',
FIRSTROW = 2,
ROWTERMINATOR = ' + '''' + '\n' + '''' +
')'
EXEC sp_ExecuteSQL @sqlquery
I don't want to insert the first line and hence have set the FIRSTROW parameter to 2. This, however, will also ignore the second line as well. If I set it to 1, it attempts the load the first line!
Anyone know how to get it to start from row 2?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Richard_Hadfield Ten Centuries Points: 1113 More actions · Answer 1

Just to shed some further light, I have put this file in a Hex editor and there is a valid line feed and carriage return after each line very confused now...

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

Richard,

It's a bug and it's in BCP as well as bulk insert. The only time the "First Row" option works correctly is when the rows to be ignored, the first row in your case, has exactly the same number of delimiters as the rest of the rows. Either edit the first row so that it's in the same format or delete the first row. Because I needed an automated process, I wrote a formless VB app to do this for me.

One thing you can try is setting the "m" option (I think that's the right one) to allow a couple of errors and forget the "first row" setting. That way, it'll mostly ignore the first row.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Richard_Hadfield Ten Centuries Points: 1113 More actions · Answer 3

Thanks Jeff, I had a feeling it might be after trying everything... The problem I have is that the file is about 1GB, mine too is an automated process so if I do anything with it I might as well not use BI / bcp as it will involve iterating through the whole thing anyway... I will try your solution.

Disappointing that such a bug exits, wish windows had equivalent functions such as Unix (head, tail etc)

Andrew Neil Newbie Points: 5 More actions · Answer 4

"wish windows had equivalent functions such as Unix (head, tail etc)" - try MKS Toolkit. I am sure there are other products that do the same.

Ideally windows should have these functions built in.

Richard_Hadfield Ten Centuries Points: 1113 More actions · Answer 5

Looked at that, quite impressive, $5000 price tag a bit steep

As this was an automated process I used ADO in the end to extract the first row and left bulk insert as was with no significant drop in processing time.

Only problem I might get is if they fix the bug!

Thanks for all the help