CODEWITHSUNDEEP
sqlsql-serveropenrowset
Daniel Wu
Daniel Wu

Reputation: 6003

openrowset for excel: can we skip several rows?

I will use the following sql to read data from excel, but sometimes I need to skip first several rows. e.g the real data begins from line 5, so I need to skip the first 4 rows, is that doable?

 SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=c:\daniel\test.xls',
    'SELECT * FROM [sheet1$]');

Upvotes: 12

Views: 20593

Answers (2)

Andriy M
Andriy M

Reputation: 77717

This will number the rows being obtained, with no specific order (as luck would have it):

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownum
  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=c:\daniel\test.xls',
    'SELECT * FROM [sheet1$]')
) s
WHERE rownum > 4;

You may want to specify some order, if you see fit, by changing the rownum definition like this:

ROW_NUMBER() OVER (ORDER BY specific_column_list) AS rownum

Upvotes: 5

Anon
Anon

Reputation: 10908

Use a range [sheet1$A5:Z] instead of the entire sheet [sheet1$]

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=c:\daniel\test.xls',
    'SELECT * FROM [sheet1$A5:Z]'
);

Upvotes: 18

Related Questions