question

nidheesh avatar image
nidheesh asked

to import excel data to sql server

I am using sql server 2008r2(64 bit) and Excel 2010. I need to use distributed query to import data from excel to sql server. I used the below query to do the same. FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 12.0 Xml;HDR=YES;Database=D:\\Alia MBIDS\\test.xlsx' ,'SELECT * FROM [sheet1$]'); But still showing an error: > OLE DB provider "'Microsoft.ACE.OLEDB.12.0'" for linked server "(null)" returned message. > Msg 7303, Level 16, State 1, Line 2 > Cannot initialize the data source object of OLE DB provider "'Microsoft.ACE.OLEDB.12.0'" for linked server "(null)". Can anyone help me.
sql-server-2008-r2excel
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have you installed the ACE x64 drivers on the server?
2 Likes 2 ·
As @Pavel already mentioned, you need the drivers, which can be downloaded from [this link][1]. Additionally, please check the account of the SQL Server service. I remember when I first time tried openrowset on my local SQL Server 2008 R2, I could not get it going until I realised that the default account of the service (Network Service) does not have permissions to the location of the Excel source file, so changing the service to use a valid domain account with appropriate permissions did the trick. [1]: http://www.microsoft.com/en-us/download/details.aspx?id=13255
0 Likes 0 ·

1 Answer

· Write an Answer
ramesh 1 avatar image
ramesh 1 answered
here is the msdn link to import data from excel in all possible ways [ http://support.microsoft.com/kb/321686/en-us][1] [1]: http://support.microsoft.com/kb/321686/en-us
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.