2

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.

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
C.J.
  • 3,409
  • 8
  • 34
  • 51
  • You can do it using OpenRowSet – HaveNoDisplayName Jan 09 '15 at 20:04
  • Can you elaborate a little bit more? Is that a tool, function, or a software? – C.J. Jan 09 '15 at 20:05
  • By OpenRowSet , you will create the new table during import – HaveNoDisplayName Jan 09 '15 at 20:13
  • 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`](http://help.pragmaticworks.com/dtsxchange/scr/FAQ%20-%20How%20to%20loop%20through%20files%20in%20a%20specified%20folder,%20load%20one%20by%20one%20and%20move%20to%20archive%20folder%20using%20SSIS.htm) – M.Ali Jan 09 '15 at 20:14
  • I answered a similar question here: https://stackoverflow.com/questions/10122538/import-multiple-excel-files-into-sql-server-2008-r2-using-ssis-packages/51719984#51719984 – rchacko Aug 07 '18 at 07:42

4 Answers4

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.

Peter Hall
  • 53,120
  • 14
  • 139
  • 204
Kai M
  • 11
  • 1
0

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

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
  • I'm getting an error message: Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". – C.J. Jan 09 '15 at 20:14
  • To create a linked server that accesses an Excel spreadsheet, you have to execute sp_addlinkedserver. follow http://technet.microsoft.com/en-us/library/ms175866(v=sql.105).aspx – HaveNoDisplayName Jan 09 '15 at 20:16
  • 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. Jan 09 '15 at 20:21
  • 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 – C.J. Jan 09 '15 at 20:22
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/

Xedni
  • 3,662
  • 2
  • 16
  • 27
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.

user11416435
  • 1
  • 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. – Johan Apr 26 '19 at 15:17