Post reply

Import Excel data with T-SQL

  • December 2, 2009 at 4:19 am

    #385877

    Hi,

    On SQL2008 / WinServer 2008 : is it possible to import Excel data into a table using T-SQL?

    Is there a manual somewhere on how to achieve this?

    Thanks!

    Raymond

  • December 2, 2009 at 4:54 am

    #1086365

    Hi,

    you can use OPENROWSET to import from excel eg:

    SELECT *

    INTO tblMyTable

    FROM

    OPENROWSET('Microsoft.Jet.OLEDB.4.0'

    ,'Excel 8.0;Database=C:\myspreadsheet.xls'

    ,'SELECT * FROM [Sheet1$]'

    )

    James Howard

  • December 2, 2009 at 5:42 am

    #1086386

    Hi,

    Thanks for your answer. The code you sent works on my old SQL2005 server. But when I run it on my SQL2008 I get this error:

    Msg 7403, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

    How do I register this OLEDB?

    By the way, on internet I find rather disturbing posts that this is not supported by 64 bit SQL2008......

    Raymond

  • December 2, 2009 at 6:00 am

    #1086401

    Hi,

    Yes I have just seen similar messages now.... hmm...

    take a look at this link

    http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&DisplayLang=en

    Specifically the overview part that states "The Microsoft OLE DB Provider for ODBC (MSDASQL) is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. MSDASQL is an OLEDB provider that connects to ODBC, instead of a database. MSDASQL ships with the Windows operating system, and Windows Server 2008 & Windows Vista SP1 are the first Windows releases to include a 64-bit version of the technology.

    It seems they had planned not to support it but popular demand forced a rethink..

    "Previous messaging on MSDN indicated that a 64-bit version of MSDASQL would not be available. However, we have received numerous requests from customers for this technology and we are making it available to address the pain experienced without 64-bit MSDASQL"

    James Howard

  • December 2, 2009 at 6:14 am

    #1086419

    Hi James,

    Yeah, I came across that article too... it says:

    MSDASQL ships with the Windows operating system, and Windows Server 2008 & Windows Vista SP1 are the first Windows releases to include a 64-bit version of the technology.

    I'm running a brand new Windows Server 2008 version 6 (build 6002 SP2)!

    Is there a way to check that it indeed is installed on my server?

    Thx,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply