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.
可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
回答1:
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
回答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.
回答3:
SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=No;Database=c:\path\to\file.xls',
'SELECT * FROM [Sheet1$A5:J995]'
);