SQL Server Portal

It is but a common practice to migrate data from Excel to SQL Server; mostly so, when we implement a new system and the initial data is required from the customer.
I mostly use OPENROWSET to migrate data instead of import/export tool to avoid multiple steps.

Given below is the script to migrate data from Excel 2003:

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 4.0;Database=D:\test.xls','SELECT * FROM [sheet1$]')

But, when the same code is used for Excel 2007 & above, it gives the following error:

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 4.0;Database=D:\test.xlsx','SELECT * FROM [sheet1$]')

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

Lets solve this issue step by step.

Step 1 :
Download Microsoft Access Database Engine 2010

Step 2 :
Install Microsoft Access Database Engine 2010 in your machine.

Step 3 :
Open SQL Server Management Studio. (If already open, close and re-open)

Step 4 :
Enable Ad hoc queries.

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 5 :
Close the excel sheet that you need to import (if open).

Step 6 :
Give the correct path, file name and excel sheet name with $ sign in the script given below and execute the query.
This information must be correct.

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\test.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

If your first row has a column name then “HDR=YES“, else it should be No.

In case you need this query result in temporary tables, given below is the script to insert Excel records into temp table.

SELECT * into Temp FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\test.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [sheet1$]');

Note : You can use the same technique for Excel 2003 files as well.

Read Full Post »