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$]');
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]'
);
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