Import Excel 2010 into SQL Server

Question

I use Excel to collect & configure data, then import it into SQL Server 2012 for storage.

So far I've been using the SQL Server Import & Export Wizard, but it is a pain to manually set it up constantly. Since I'm using Express, of course it won't allow me to save, or even view, the actual commands to transfer the data.

I tried to set up a linked server, per How to use Excel with SQL Server linked servers and distributed queries, but get the following error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FLTST".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FLTST" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

I thought perhaps the Excel version number was the problem, since the web page is from 2005, so I tried with:

• Excel 8.0 (Excel 2002) as shown on the page
• Excel 12.0 (Excel 2007) which is what the wizard seems to use
• Excel 14.0 (Excel 2010) what I actually have

All of those gave me identical results.

Next I tried the distributed query as shown at Import excel file to SQL Server Express, (again with different variations of the provider string)

USE ExTest

SELECT * INTO TstTbl FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 14.0;Database=c:\ExTest.xlsm', [Contacts])

go

Which gives me the following error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Instead of going to SQL Server & pulling the data in, should I stay in Excel & push it over?

What am I doing wrong?

PS: Please don't tell me to convert it to a csv file! I'm trying to do fewer steps, not more!

Answer 1

Having similar issues as you have in your question I have done some research on this. My issue is not yet fully resolved but I think I might get you one step further. Although the question is old there is perhaps someone else who needs the help.

By running:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=P:\Path\File.xlsx','SELECT * FROM [Sheet1$]');
GO

I get the following error message:

Msg 15281, Level 16, State 1, Line 19 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

To resolve that I run the following:

sp_configure 'show advanced options', 1  
RECONFIGURE  
GO  
sp_configure 'ad hoc distributed queries', 1  
RECONFIGURE  
GO 

But I get a new error mesasge:

Msg 7302, Level 16, State 1, Line 19 Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

To rectify that I run:

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

But I get this error in stead:

Msg 7438, Level 16, State 1, Line 19 The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

In my case I have asked the IT department to install a 64 bit version of excel on the server and I hope that should be the end of the technical problems when importing from excel.

To clean up afterwards I disable the settings I just enabled:

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0
GO

sp_configure 'ad hoc distributed queries', 0
RECONFIGURE  
GO

sp_configure 'show advanced options', 0  
RECONFIGURE  
GO  

Answer 2

1. Create an SSIS package with Excel data source connection manager, destination is your SQL express, OLE DB destination
2. When you create Excel connection manager, you can just use one existing excel file
3. Define one user variable, like user::sourceFile, which is used to input excel file full path
4. After Excel connection manager is created, right click-> preperties-> find the "Expression", just give your [User::sourceFile] to the Expression
5. Just create one simple data flow from your source to destination
6. Save and debug your SSIS package, make sure all credential works and data can flow into destination table. Note: don't save sensitvie data in your package with ecrypted by machine key
7. Each time when you need to load a new file, use DTEXEC to execute package and override the parameter

good luck