Error while loading data from Excel to SQL SERVER table

Question

Hi Experts,

I've a requirement to load data from excel sheet to sql server table. I've to load data from .xlsx extension file to sql server 2012 table.

I've written below code for this...

-- Code start
USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' ,  N'AllowInProcess' ,  1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' ,  N'DynamicParameters' ,  1
GO
-- code end

-- test query 1: change the path to your excel file
select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0;Database=E:\Excel.xlsx'
, [Sheet1$]
)

-- test query 2: change the path to your excel file
select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0;Database=E:\Excel.xlsx'
, 'select top 2 * from [Sheet1$]'
)

when am running the above query getting the below error...

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

I've searched how to resolve this but after trying many options not able to fix this error.

Could you please help me in resolving this.

Thanks a lot.

Regards,

NoorBi

Answer 1

Hi NoorBi,

Are you trying to run this query using Windows Authentication? Do you have access to the 'E:\' where you placed your file?

Regards,

Brindha.

Answer 2

Yes, I am using my local machine for this. I am admin on my machine.

Answer 3

Is E: a local disk or a mapped network share?

Have you considered using SSIS instead? Alternatively write a program or a Excel macro that reads the Excel file and loads it into SQL Server?

You could also save the Excel file as a comma-separate file and load it with BCP, but this is really only worthwhile if it's a one-off.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Hi NoorBi,

Try creating a folder in E:\ and place your file in the new folder. Make sure that you have proper access to that folder. Sometimes local admin have issues in accessing the drive directly.

Regards,

Brindha.

Answer 5

If you don't have a specific business requirement for using openrowset, then I'd use SSIS to import this excel sheet.  MS documentation says openrowset "...is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB."  http://msdn.microsoft.com/en-us/library/ms190312.aspx

You can easily import this in Microsoft SQL Server Management Studio by right clicking on the database-->Tasks-->Import Data.

If you want to repeat this process many times, then you can save the SSIS package you created and set it as a step in a SQL Agent job to import on a schedule.

Answer 6

It looks like you have a linked server setup.

1.  Make sure you've downloaded and installed the ACE on the computer with SQL Server installed on it.

http://www.microsoft.com/en-us/download/details.aspx?id=13255

2.  Delete your linked server and recreate it.

EXEC sp_addlinkedserver @server = N'ExcelDataSource',
@srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'E:\test.xlsx',
@provstr=N'EXCEL 12.0' ;

Make sure you put the excel file on the server in the correct location. 

3.  After you have created the linked server add your account to it.  This can be done through the SMS GUI too.

exec sp_addlinkedsrvlogin
    @rmtsrvname = 'ExcelDataSource',
    @useself = 'False',
    @locallogin = YourAccount,
    @rmtuser = 'Admin',
    @rmtpassword = null

4.  Open a new query and type the following

select * from ExcelDataSource...[Sheet1$]

Answer 7

Thank you Brindha it worked.

I created new folder and place my file inside that folder and executed query..it worked.

But strange behavior isn't it?

Thanks a lot.

--NoorBi