none
import Data using Excel 2003 on 64 Bit SQL Server

    Question

  • I wonder if you happen to run into following issue with 64 bit SQL Server when we try to import from Excel 2003:


    We have a lot of functionality relying on following statement:

    select * into TableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=1;Database=fullfilename',Sheet1$)"

    which works nicely from 32 bit SQL Server 2005 to import Excel 2003.

    Now we need to do the same from 64 bit SQL2008, this driver doesn't work and new "ACE" drivers also seem to fail.

    Do you know if  there any driver available to do what we need.

    I appreciate any feedaback you may have.

     

    Tuesday, August 03, 2010 4:04 PM

Answers

All replies

  • Hi,

    Could you please elaborate a bit? There is no 64-bit version of the Jet Drivers available. We can use the 64-bit version of the ACE Driver/Provider to retrieve data from an Excel spreadsheet on a 64-bit box.

    Please see:

    How to get a x64 version of Jet?
    http://blogs.msdn.com/b/psssql/archive/2010/01/21/how-to-get-a-x64-version-of-jet.aspx

    What do you mean by “new ACE drivers also seem to fail”? Did you receive any error messages? As it’s a 64-bit SQL Server, we need to download and install the64-bit “Microsoft.ACE.OLEDB.12.0” from the following link:

    Microsoft Access Database Engine 2010 Redistributable
    http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

    After that, we can use the following statement to retrieve data from the Excel 2003 spreadsheet:

    SELECT * FROM OPENROWSET(
     'Microsoft.ACE.OLEDB.12.0',
     'Excel 12.0 Xml;Database=D:\Book1.xls;HDR=YES',
     'SELECT * FROM [Categories$]')
    

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by vinod kushwaha Thursday, August 05, 2010 7:02 PM
    Wednesday, August 04, 2010 5:26 AM
  • Hi Guys

     

    I've been playing with this driver for a while and I'm not yet satifisied this is a stable driver, myself and according to quite a lot of other internet forums like this, have experienced serious problems with this driver. You may find it works fine for a while, but randomly it will stop working and if you are using this to insert data say from an Excel file or CSV file then you will end up will a process on your SQL server that cannot be killed (it will be left in a permanent state of KILLED/ROLLBACK for weeks if you leave it).

     

    This is not a permissions issue, trust me, it would appear most likely to be an issue with the driver - aside from the fact that it screws the process and leaves it in a state of perpetual KILLED/ROLLBACK if you try and kill it.

     

    I thought this might be the answer to 64 bit woes of Microsoft Office file connectivity via SQL 2005. Which might I say has made 64 bit SQL and the lack of 64 bit Windows drivers to access CSV or Microsofts own Access/Excel files via 64 bit SQL server a joke until this driver! I for one seriously lost faith in SQL server 64 bit because of this, I know its a fault with Windows Server 2003 64 bit and it not the JET driver but come on Microsoft do you want to have SQL server taken seriously over its competitors?

    So SSIS an Excel files on a 64 bit server? Say hello to running it from a command line in via WOW... For anyone interested, here is the command line you can use to execute your 32bit package... unless your lucky enough to have SQL 2008 where you've got a tick box to run in 32bit mode.

     

    C:\Windows\SYSWOW64\CMD.EXE /C ""C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /FILE "C:\somepackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E"

     

    Don't forget all those quotes, that stung me the first time!!!

     

    So unless you can work around this problem and are free to restart your server - I invite anyone else to come up with another solution - then steer clear of this driver or use with caution!!

     

    If anyone has figured any way round this, please let me know! I will eternally be your friend!! :-)

    Monday, August 16, 2010 10:17 AM
  • Hi Padigan I'm just writing to confirm what you said. For simple excel file around couple of thousands of row the process run fine but when we get closed to 100 of thousands of row, the process run for ever. The funny thing is it works sometimes and other times it just gets stocked. I just choose to enable the 32bit mode in IIS 7.5 and stick with the 32 bit version of ACE.
    Wednesday, January 05, 2011 4:03 PM