[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 666
  • Last Modified:

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

0
Dovberman
Asked:
Dovberman
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now