SQL Bulk Insert with FIRSTROW parameter skips the following line

bulkinsert, sql, sql-server-2005

Solution

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:\Data\test\so1029384.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.

Problem

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!

Original source