How to do a Bulk Insert when the text file has a header row

The Bulk Insert works if I remove the header row.
What is the syntax if I wish to ignore or skip the header row?

Thanks,
SET @sql = 'BULK INSERT SplitsWork from ''' + @SourcePath + @SourceFile + ''' ' +      
		   'WITH (FIELDTERMINATOR  = ''\t'', ROWTERMINATOR= ''\n'',LASTROW=0,FIRSTROW=0) ';

Open in new window

DovbermanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
0
 
DovbermanAuthor Commented:
Thanks for the reference.

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

I got the following error:
Cannot bulk load. When you use the FIRSTROW and LASTROW parameters, the value for FIRSTROW cannot be greater than the value for LASTROW.

I will include the header row, then delete it from the table.

These changes work:

SET @sql = 'BULK INSERT SplitsWork from ''' + @SourcePath + @SourceFile + ''' ' +      
               'WITH (FIELDTERMINATOR  = ''\t'', ROWTERMINATOR= ''\n'',FIRSTROW=1) ';
Resulting data after the insert.

I can delete where field1 value = 'Symbol'
Symbol      Date      Ratio
CLM      20081223      1-4
CRF      20081223      1-2
DOY      20090130      4-1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.