Skip to content
Advertisement

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:

Here’s my bulk insert statement:

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

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!

Advertisement

Answer

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

When I run this:

I get:

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement