openrowset for excel: can we skip several rows?

sql sql-server openrowset
Daniel Wu picture Daniel Wu · Feb 8, 2011 · Viewed 17.2k times · Source

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

Answer

Anon picture Anon · Jan 18, 2012

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

Related questions

INSERT INTO from two different server database

I am trying to copy the data of testdabse.invoice table to basecampdev.invoice table. testdabse is a local database while basecampdev is in the server. My query for copying data to another table doesn't work, it says Invalid object …

Read More
Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null

I am trying to export from my Table data into Excel through T-SQL query. After little research I came up with this INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=G:\Test.xls;', 'SELECT * FROM [Sheet1$]') SELECT * …

Read More
Using a Variable in OPENROWSET Query

I'm having trouble with this query: SELECT * FROM OPENROWSET( 'SQLNCLI', 'DRIVER={SQL Server};', 'EXEC dbo.sProc1 @ID = ' + @id ) Gives an error: Incorrect syntax near '+'. Anyone know why I'm getting this error?

Read More