Post reply

Using OPENROWSET to import Excel

  • September 10, 2009 at 12:08 pm

    #98107

    Hello,

    I have a requirement to upload excel files to SQL 2005 64-bit Enterprise Edition database. The excel file is going to have varying number of columns and hence i want to use a SELECT * INTO temptable....OPENROWSET function.

    The scenario is the excel files are going to be dropped on the Application server and i am going to have to use UNC path in the OPENROWSET function to read the files.

    Apparently there are few issues doing this, i am getting the error "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." I have read that this due to the 64-bit version of the DB Engine.

    Are there any alternate ways of uploading excel file with varying columns to a SQL Server db using T-SQL?

    Any help will be greatly appreciated!

    Thanks in advance!

    Amol

    Amol Naik

  • September 10, 2009 at 1:54 pm

    #1051940

    You could use SSIS and read the schema of the excel file.

    http://www.mssqltips.com/tip.asp?tip=1674

    Looks fairly complicated. I have't done it.

    This quote looks important to note.

    'You could also use a Script task to write ADO.NET code to insert the Excel data into a table. The Data Flow doesn't support a dynamic column list.'

    Emily

  • September 11, 2009 at 2:04 am

    #1052124

    That's one of the things I don't like about the 64 bit version... no JET drivers.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 11, 2009 at 6:52 am

    This was removed by the editor as SPAM

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

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