Import Excel file data

Question

Hi,

On my server, I do not have MS Office installed. I've installed MS Data Access Engine 2010 redistributable on my server.

But, I am able to import Excel Files(.xlsx) using the import wizard in SQL Server, using the driver ACE.12.0. But when I try to import the data using OPENROWSET in my query, using the same driver ACE.12.0, it fails.

Why is this behavior? Why, the Import Wizard successfully extracts the data while OPENROWSET Fails?

I've given all the necessary permissions to the 'temp' directory. But, still it fails.

Please guide me if there is any settings/configurations I need to do in-order OPENROWSET to work.

Thanks,

Shivkumar Gotur

Answer 1

Hello,

You have forgotten 2 informations which are essential to try to help you :

- the full and exact error message you are getting when you are using OPENROWSET

- how you are using your OPENROWSET ( the statements ) ?

We are waiting for your feedback to try to help you more efficiently

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hi

The query I am using:

select * from OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\abc.xlsx;IMEX=1;HDR=YES', 'SELECT * from [Sheet1$]')

And the error Message:

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)".

Regards,

Shivkumar Gotur

Answer 3

Hello,

I am far to be a specialist of OLEDB and Excel but after some searches ,  I found that a possible cause of your problem could be that the excel file is in the root of the C: drive. Are you sure that you have the permission to access Database=c:\abc.xlsx

Even on my old XP SP3 , it is difficult to access to a file situated in c:

See http://social.msdn.microsoft.com/Forums/is/sqldatabaseengine/thread/9150f93d-3e2f-4cb0-a507-f141da367609  ( "the service account needs also access permission for that  file" according Olaf Helper )

Other possibly useful links :

http://connect.microsoft.com/SQLServer/feedback/details/284113/import-excel-data-with-openrowset-on-production-server

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

Don't hesitate to post again for more help or explanations

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

Hi,

I checked all these links... but nothing works. And again the service account has the necessary access permissions on the folder which has the excel file...

The thing is, if I use Import/Export wizard , I am able to extract the data . But only with OPENROWSET it fails. What is the difference between these two techniques? Is there any way, I can use Import wizard functionality in my Stored Procedure/Query?

Thanks,