select of data from excel into sql server

Amir,

I am not sure if this is the issue or not, but it looks like you are missing a slash (i.e. '\') in this line of code:

'EXCEL 12.0;DATABASE=D:example.XLSX;',

 I also do not remember Excel 2007 that well, but have you tried this type of syntax:

'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',

HTH.

Thank you,
Jeremy Kadlec

Is your Windows 2008 and SQL Server 2008 R2 running 64 bit?  

There are 64 bit drivers available; see http://www.microsoft.com/download/en/details.aspx?id=13255 to download. 

Ray,

Great point! It could be a driver compatibility issue as well.

Thank you,
Jeremy Kadlec

yes i am using windows 2008 and sql server 2008 R2 64 bit. but i have office 2007 32 bit. and when i try to install 64 bit driver it dose not allow me to install.

Jeremy,

i try to it using '\' but still give the same error.

Amir,

What error do you get when you try to install the 32 bit driver?  Or do you not have permissions to install software on the machine?

Thank you,
Jeremy Kadlec

i did not get any error installing 32 bit driver it install successfully. but with 32 bit i think it does not work. when i im trying to install 64 bit driver i get error:

you cannot install the 64-bit bersion of microsoft access database enging 2010 because you currently have 32-bit ofice product installed. 

i want to using 32 bit excel with the same query and pull the record.

i tried the same code and setting mention in my question on 32 bit sql server and 32 bit office i was working fine. i im trying to use it with 64 bit of sql server and 32 bit of office. is there any way ????

thanks,

Amir.

Amir,

I do not have time right now to test a scenario with SQL Server 64 bit and Office 32 bit, but I have a sneaking su***ion it does not work.

Let me see if I can test this in the near term.

Thank you,
Jeremy Kadlec

Jeremy,

Thanks for your kind response.

amir,

I've too struggled a LOT with this issue and here's the answer to this.

In order to access Excel/Access files from SQL Server X64 here's what's important. Before even trying there can't be anything Office x86 related installed on the Box. If it is please uninstall.

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

Then follow the directions here : http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

Now you can use either :

SELECT * FROM OPENROWSET

(

       'Microsoft.ACE.OLEDB.12.0'

      ,'Excel 12.0;Database=D:\TEMP\Excel_File.xlsx'

      ,[Sheet1$]

)

OR use

SELECT * FROM OPENDATASOURCE

(

 'Microsoft.ACE.OLEDB.12.0'

,'Data Source=\\Server\Share\Access_DB.mdb'

)...[Table_Name]

Now it works. Should you really need Office installed, try it and see what's happening. I haven't dared yet. Post back with your results.