import data from Excel to SQL Server on 64-bit machine RRS feed

  • Question

  • Hello everyone,

    I want to import all data from an Excel file to a SQL Server 2008 database table. There is 1 to 1 mapping between Excel column and the SQL Server database table column. I am wondering on 64-bit platform (64-bit Windows Server 2008 + 64-bit SQL Server 2008), what technology should I use? Because I am new to this area, appreciate if anyone could provide me some samples or tutorials. I ask this question in the context of 64-bit platform because I heard not all technologies work on both 32-bit and 64-bit.

    BTW: I am using VSTS 2008 + .Net 2.0 + C# + Windows Server 2008 x64 + SQL Server 2008 Enterprise 64-bit + ADO.Net + ASP.Net + IIS 7.0.

    thanks in advance,


    • Moved by Tom Phillips Thursday, January 13, 2011 3:40 PM TSQL question (From:SQL Server Database Engine)
    Thursday, January 13, 2011 2:35 AM


All replies

  • There is now a 64 bit JET driver available for SQL-Server 64 bit; however, you must be extra careful before installing it because it is not compatible with the 32 bit version: you cannot install it on a machine which has already the 32 bit driver installed. So, if you have already a copy of Office/Access 32 bit installed on this machine, you must first remove them before installing this 64 bit driver.

    You can find this driver for download at:


    For Access and Excel, the 'Ad Hoc Distributed Queries' option must be set to
    ON and, in come case, the N'AllowInProcess' and N'DynamicParameters' must
    also be set, see:


    A better option would to install a copy of SQL-Server Express 32 bit and use it as a proxy to do your stuff.  See for example:


    The above is a complex generic solution but in your case, probably that you will be able to whip up something much more simpler.

    Other possibilities would be to use any other external client like SSIS or even ASP to etablish a data transfert between your SQL-Server 64 bit and Excel.  In fact, you could even do this directly from Excel by using the ADO objects with VBA code.

    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Marked as answer by Ai-hua Qiu Friday, January 21, 2011 6:13 AM
    Thursday, January 13, 2011 3:02 AM
  • I created a SSIS package that does the job and then created SQL AGENT job to shcedule it

    In the command window of step properties I typed

    C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec  /FILE "N:\Microsoft SQL Server\MSSQL.1\MSSQL\SSIS\Excel\ssisname\WorkersToExcel\ssisname.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EW

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 13, 2011 6:38 AM