How to selectively import an XLS into an SQL Serve

Question

I have an XLS file. Let's assume that it has 1000 rows in all, with 10 columns. I would like to import rows 5 through 995 (say), and columns 1 though 7 and 10. How can I do this using an SQL query, either though OPENROWSET or BULK INSERT? I am using SQL Server 2008.

Answer 1

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=No;Database=c:\path\to\file.xls',
    'SELECT * FROM [Sheet1$A5:J995]'
);

Answer 2

I would approach this in a slightly different way.

I would import all the data into the database, into a temporary table. Then I would query that table, extracting the information that's needed.

Alternatively, you could look at a SSIS process to do this.

Answer 3

I had another idea.

You could also use a Linked Server which connects to the Excel document.
(This is probably an easier approach for you.)

See: http://support.microsoft.com/kb/306397