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.

Solution

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.

Comments

[HaveNoDisplayName]

You can do it using OpenRowSet

[C.J.]

Can you elaborate a little bit more? Is that a tool, function, or a software?

[HaveNoDisplayName]

By OpenRowSet , you will create the new table during import

[M.Ali]

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

[rchacko]

I answered a similar question here: stackoverflow.com/questions/10122538/…

[C.J.]

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

[HaveNoDisplayName]

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.]

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.]

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

[Johan]

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.