Bulk Insert skipping second row

Question

Bulk Insert skipping second row

Kevin Benson

SSC-Addicted

Points: 492
More actions
November 29, 2007 at 11:11 am

#62983

I have a text file with the following format (the original file is attached):
H|16012|XXXX,INC|73794651|00|46500||
D|322191|ACT|14||11|HART,JILLL|110000015044|1|500|11162007|500|00|500|00|00|2|2||
D|322360132|ACT|14||11|STEWART,ANTHONYC|110000020543|1|3000|11132007|3000|00|3000|00|00|2|2||
D|322415|ACT|14||11|PRUETT,JAMEST|110000052942|12|2000|11162007|2000|00|2000|00|00|2|2||
D|322426|ACT|14||11|JOHNSON,CHRISTOPHER|110000053541|2|2000|11132007|2000|00|2000|00|00|2|2||
D|322561262|ACT|14||10|GRANDISON,BRANDONM|110000053740|1|24500|11132007|24500|00|24500|00|00|2|2||
The first row is a header row and all the following rows are data I want to import. When I run the following:
BULK INSERT tmpUploadData_Reconciliation
FROM '\\myserver\ftp\data\RECON_PB_200711250409.txt'
WITH (
FIELDTERMINATOR = '|',
FIRSTROW = 2
)
only the last four rows of data are imported. When I set FirstRow to 1, the first row errors out, the second is skipped, and the rest are imported. If I set FirstRow to 3 only the last three rows are imported. The extra spaces at the end of each line seem to be inconsequential. The problem occurs whether they are there or not. The row delimiter is the same for all rows (line feed (ascii 10) and carriage return (ascii 13)). I tried playing around with the row delimiter. Not specifying one or using give the results noted above. Using \r or \r return no rows.
Any suggestions would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 1

The problem is that the header row does not have the same number of delimiters as the rest of the file. Unfortunately, Bulk Insert counts delimiters as well as EOL.

There's 5 ways around this and they all pretty much stink...

1. Have the people who provide the file fix it (uh huh... we all know how well that works)

2. Manually open the file with something like WordPad or TextPad (maybe even notepad) and manually delete the header row.

3. Write a preprocessor in VBS or some-such to remove the offending header and call it from T-SQL using xp_CmdShell or manually from a cmd prompt. (suprisingly fast. I use this quite often if I can use zp_CmdShell)

4. Bulk Insert the rows into a single wide column... delete the header row from the table. Export the data using BCP. Reimport the data (surprisingly fast. I use this quite often).

5. Bulk Insert the rows into a single side column and use a table wide split (requires a Tally table) to split the whole table.

I typically use options 3 and 4. Ill see if I can find the VBS code that strips the header...

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

Kevin Benson SSC-Addicted Points: 492 More actions · Answer 2

So even though I am asking it to skip the header row, Bulk Insert still requires that the omitted row have the same number of delimiters? Interesting.

If you could find that VB script that would be great. Thanks for your help.

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 3

Yeah... that's the disappointing truth...

I'm actually on vacation, right now... I'll see if I can find that script at work on Monday... it's in a piece of automation I built somewhere...

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