Import Multiple Excel files to SQL Server

Question

I have about 40+ excel files that I would like to import to SQL Server 2012. The import wizard is a great tool but it only allows me to import one file at a time.

I want to avoid using SSIS because the import should be relatively easy - basically it is just a direct copy-paste with the first row in the Excel files being the column names, and the Excel Files name = Table Name.

Is there any easy way to achieve this?

Edited: Since the columns will change quite frequently, I would like to avoid creating the tables manually. The wizard is great because it will create the table for me automatically.

Can you elaborate a little bit more? Is that a tool, function, or a software? — C.J.
– C.J., Commented Jan 9, 2015 at 20:05
What you need is an SSIS packages to loop through all the execl files and import data into sql server, here is a great example of how to do it with a sample project How to loop through files in a specified folder, load one by one and move to archive folder using SSIS — M.Ali
– M.Ali, Commented Jan 9, 2015 at 20:14
I answered a similar question here: stackoverflow.com/questions/10122538/… — rchacko
– rchacko, Commented Aug 7, 2018 at 7:42

HaveNoDisplayName · Accepted Answer · 2015-01-09 20:11:33Z

1

You can do it using OpenRowSet. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name.

Read excel using the following SELECT statement together with the OPENROWSET function

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

To import this to a SQL Server table without using DTS, since you are able to read the data using the SELECT statement, you can simply add the INTO clause to insert the records into a new SQL Server table.

SELECT * 
INTO [dbo].[Addresses_NewTable]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

Reference article:- http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx

edited Jan 9, 2015 at 20:11

answered Jan 9, 2015 at 20:06

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

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

4 Comments

C.J. Over a year ago

I'm getting an error message: Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

HaveNoDisplayName Over a year ago

To create a linked server that accesses an Excel spreadsheet, you have to execute sp_addlinkedserver. follow technet.microsoft.com/en-us/library/ms175866(v=sql.105).aspx

C.J. Over a year ago

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82 The server 'EXCEL' already exists. Msg 7302, Level 16, State 1, Line 2 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

C.J. Over a year ago

When I executed sp_addlinkedserver, it was successful. But then I'm still getting the Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".. When I tried to re-exec again, it says the link already exist

Peter Hall · Accepted Answer · 2018-09-12 09:35:31Z

1

A solution I used is to combine multiple Excel files into one with multiple sheets. This can be done by running a VBA script (for example How to merge Excel files with VBA - thanks to Svetlana Cheusheva) then use the SSMS import wizard to load multiple sheets into multiple tables. In my case, I have 160 files, but they are not very big — 100 to 10000 rows — and it worked perfectly for me.

edited Sep 12, 2018 at 9:35

Peter Hall

59.7k17 gold badges168 silver badges229 bronze badges

answered Sep 12, 2018 at 6:41

Kai M

111 bronze badge

Comments

Xedni · Accepted Answer · 2015-01-09 21:13:56Z

0

It may be more research than you want to put into your problem, but you might also look into using BIML to script out the tedious and numerous similar packages.

http://www.sqlservercentral.com/stairway/100550/ http://www.mssqltips.com/sqlservertip/3094/introduction-to-business-intelligence-markup-language-biml-for-ssis/

answered Jan 9, 2015 at 21:13

Xedni

4,7913 gold badges22 silver badges38 bronze badges

Comments

user11416435 · Accepted Answer · 2019-04-26 14:48:58Z

0

Use Kutools for Excel to merge the worksheets across workbooks into one worksheet in a new workbook and then load that.

https://www.extendoffice.com/product/kutools-for-excel/merge-excel-wordbooks.html#a4

That's what I'm doing.

answered Apr 26, 2019 at 14:48

user11416435

1

1 Comment

Johan Over a year ago

Welcome to Stack Overflow! While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

Collectives™ on Stack Overflow

Import Multiple Excel files to SQL Server

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related