Import multiple Excel files/sheets into SQL Table

Question

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?

union SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="C:\0.xlsx"; Extended properties=Excel 8.0')...Sheet2$ — twoleggedhorse
– twoleggedhorse, Commented Apr 17, 2014 at 11:19
@twoleggedhorse I expect you will find locking issues with this. — Anthony Horne
– Anthony Horne, Commented Apr 17, 2014 at 11:23

Anthony Horne · Accepted Answer · 2014-04-17 11:24:09Z

1

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'

answered Apr 17, 2014 at 11:24

Anthony Horne

2,5202 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anthony Horne Over a year ago

When you are done, it would be nice to know what, if any, speed differences there are.

kravits88 Over a year ago

Haven't run any tests but seems like the same speeds for both subjectively. About 7 seconds per sheet either way. Also I ended up using 'Microsoft.ACE.OLEDB.12.0' as the provider.

Collectives™ on Stack Overflow

Import multiple Excel files/sheets into SQL Table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related