J'ai environ 100 files xlsx, tous avec 1 à 7 feuilles chacune. Chaque file et feuille a les mêmes colonnes que le tableau dans lequel je veux importer tout.
Je peux l'utiliser avec succès:
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\0.xlsx', 'SELECT * FROM [sheet1$]' )
ou
SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="C:\0.xlsx"; Extended properties=Excel 8.0')...Sheet1$
SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0', 'Source de données = "C: \ 0.xlsx";SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="C:\0.xlsx"; Extended properties=Excel 8.0')...Sheet1$
Mais comment puis-je importer plusieurs feuilles d'un file?
Serveur lié
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'
EXEC sp_addlinkedserverEXEC 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'
@server = 'ExcelServer1',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'
@srvproduct = 'Excel',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'
@provider = 'Microsoft.Jet.OLEDB.4.0',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'
@datasrc = 'C: \ Test \ excel-sql-server.xls',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'
@provstr = 'Excel 8.0; IMEX = 1; HDR = YES;'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'
EXEC sp_dropserverEXEC 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'
@server = N'ExcelServer1 ',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'