将多个Excel文件/表导入SQL表 [英] Import multiple Excel files/sheets into SQL Table
本文介绍了将多个Excel文件/表导入SQL表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我可以成功使用这个:
pre>
SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database = C:\ 0.xlsx',
'SELECT * FROM [sheet1 $]'
)
或
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source =C: \\ 0.xlsx;
扩展属性= Excel 8.0')... Sheet1 $
但是如何从文件导入多张表?
解决方案
- 为Excel创建链接服务器文件
- 使用sp_tables_ex来发现提供程序返回的表(大部分应该是工作表名称,但可能取决于提供程序实现)
链接服务器
EXEC sp_addlinkedserver
@s erver ='ExcelServer1',
@srvproduct ='Excel',
@provider ='Microsoft.Jet.OLEDB.4.0',
@datasrc ='C:\Test\excel -sql-server.xls',
@provstr ='Excel 8.0; IMEX = 1; HDR = YES;'
EXEC sp_dropserver
@server = N'ExcelServer1'
@ droplogins ='droplogins'
I have about 100 xlsx files, all with 1-7 sheets each. Each file and sheet has the same columns as the table I want to import everything into.
I can use this successfully:
SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\0.xlsx',
'SELECT * FROM [sheet1$]'
)
or
SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="C:\0.xlsx";
Extended properties=Excel 8.0')...Sheet1$
But how can I import multiple sheets from a file?
解决方案
- Create linked server for the Excel file
- Use sp_tables_ex to discover the tables that the provider returns (mostly this should be the worksheet names but it may depend on the provider implementation)
Linked Server
EXEC sp_addlinkedserver
@server = 'ExcelServer1',
@srvproduct = 'Excel',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\Test\excel-sql-server.xls',
@provstr = 'Excel 8.0;IMEX=1;HDR=YES;'
EXEC sp_dropserver
@server = N'ExcelServer1',
@droplogins='droplogins'
这篇关于将多个Excel文件/表导入SQL表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文