SQL Bulk Insert with FIRSTROW parameter skips the next line

Question

I can’t understand how this happens.

Here is an example file that I am trying to upload to SQL Server 2005:

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

Here is my main insert:

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

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

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

The first entry is always skipped if I do not delete the header at all and do not use the FIRSTROW parameter. How is this possible?

Thanks in advance!

Answer 1

I do not think you can skip lines in a different format using BULK INSERT / BCP .

When I ran 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 this requires '|' even in the header data, because it reads them to the first column - swallowing a new row in the first column. Obviously, if you include a field term parameter, it expects MUST be in each row.

You can break the line at the preprocessing stage. Another possibility is to select only complete lines and then process them (excluding the header). Or use a tool that can handle this, such as SSIS.

Answer 2

Perhaps check if the header has the same end of line as the actual data rows (as indicated in ROWTERMINATOR )?

Update: from MSDN :

The FIRSTROW attribute is not intended to skip column headers. Skipping headers are not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only on field terminators and does not check data in the fields of skipped rows.

Answer 3

It was easiest for me to read the entire row in one column, and then parse the data using XML.

 IF (OBJECT_ID('tempdb..#data') IS NOT NULL) DROP TABLE #data CREATE TABLE #data (data VARCHAR(MAX)) BULK INSERT #data FROM 'E:\filefromabove.txt' WITH (FIRSTROW = 2, ROWTERMINATOR = '\n') IF (OBJECT_ID('tempdb..#dataXml') IS NOT NULL) DROP TABLE #dataXml CREATE TABLE #dataXml (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, data XML) INSERT #dataXml (data) SELECT CAST('<r><d>' + REPLACE(data, '|', '</d><d>') + '</d></r>' AS XML) FROM #data SELECT d.data.value('(/r//d)[1]', 'varchar(max)') AS col1, d.data.value('(/r//d)[2]', 'varchar(max)') AS col2, d.data.value('(/r//d)[3]', 'varchar(max)') AS col3 FROM #dataXml d 

Answer 4

You can use the snippet below

 BULK INSERT TextData FROM 'E:\filefromabove.txt' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '|', --CSV field delimiter ROWTERMINATOR = '\n', --Use to shift the control to next row ERRORFILE = 'E:\ErrorRows.csv', TABLOCK ) 

Answer 5

Given how some corrupt data can track BCP imports into SQL Server from non-SQL data sources, I would suggest that you first complete all BCP imports into some tables from scratch.

for example

clipping table Address_Import_tbl

BULK INSERT dbo.Address_Import_tbl FROM 'E: \ external \ SomeDataSource \ Address.csv' C (FIELDTERMINATOR = '|', ROWTERMINATOR = '\ n', MAXERRORS = 10)

Make sure all columns in Address_Import_tbl are nvarchar () to make it as agnostic as possible and avoid type conversion errors.

Then apply all the fixes needed for Address_Import_tbl. Like removing an unwanted header.

Then run the INSERT SELECT query to copy from Address_Import_tbl to Address_tbl along with any data type conversions you need. For example, to import dates in SQL DATETIME.