SQL Bulk Insert with FIRSTROW parameter skips the following line

I can’t seem to figure out how this is happening.

Here’s an example of the file that I’m attempting to bulk insert into SQL server 2005:

***A NICE HEADER HERE***
0000001234|SSNV|00013893-03JUN09
0000005678|ABCD|00013893-03JUN09
0000009112|0000|00013893-03JUN09
0000009112|0000|00013893-03JUN09

Here’s my bulk insert statement:

BULK INSERT sometable
FROM 'E:filefromabove.txt
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR= '|',
ROWTERMINATOR = 'n'
)

But, for some reason the only output I can get is:

0000005678|ABCD|00013893-03JUN09
0000009112|0000|00013893-03JUN09
0000009112|0000|00013893-03JUN09

The first record always gets skipped, unless I remove the header altogether and don’t use the FIRSTROW parameter. How is this possible?

Thanks in advance!

Answer

I don’t think you can skip rows in a different format with BULK INSERT/BCP.

When I run this:

TRUNCATE TABLE so1029384

BULK INSERT so1029384
FROM 'C:Datatestso1029384.txt'
WITH
(
--FIRSTROW = 2,
FIELDTERMINATOR= '|',
ROWTERMINATOR = 'n'
)

SELECT * FROM so1029384

I get:

col1                                               col2                                               col3
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
***A NICE HEADER HERE***
0000001234               SSNV                                               00013893-03JUN09
0000005678                                         ABCD                                               00013893-03JUN09
0000009112                                         0000                                               00013893-03JUN09
0000009112                                         0000                                               00013893-03JUN09

It looks like it requires the ‘|’ even in the header data, because it reads up to that into the first column – swallowing up a newline into the first column. Obviously if you include a field terminator parameter, it expects that every row MUST have one.

You could strip the row with a pre-processing step. Another possibility is to select only complete rows, then process them (exluding the header). Or use a tool which can handle this, like SSIS.