T-SQL OpenRowSet from CSV missing header row

Question

I have a CSV that doesn't have a HEADER row. Using OpenRowSet assumes the first row is the HEADER row. How do I prevent this from happening? I know there are 18,000 records in my CSV file, but when I open with OpenRowSet, I only get 17,999, and the first row in the CSV is labeled as the column headers.

TIA

Answer 1

add the firstrow option for openrowset.

for example:

select *
  from openrowset(bulk n'd:\test-csv.csv'
      , formatfile = n'd:\test-csv.fmt'
      , firstrow=1 
      , format='csv'
      ) as eg;  

As Alex K. mentioned, using HDR=NO will help when using a connection string to a source without a header row.

select * 
  from openrowset('Microsoft.Jet.OLEDB.4.0'
    , 'Excel 8.0;Database=d:\test.xls;HDR=No'
    , 'SELECT * FROM [Sheet1$]'
    ) as eg.