Import Excel Spreadsheet to Database Tables

  • peterhe

    SSChampion

    Points: 11362

    Comments posted to this topic are about the item Import Excel Spreadsheet to Database Tables

  • Servercentral-1016448

    SSC Enthusiast

    Points: 166

    Thanks for your article. Do you also have a solution for sheet xml imports if the sheet name is change randomly or with a certain string?

  • Madhivanan-208264

    SSCertifiable

    Points: 7405

    For 32 bit version, here are some methods used to import/export to EXCEL from SQL Server table

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926


    Madhivanan

    Failing to plan is Planning to fail

  • Jim Cheong

    SSC Journeyman

    Points: 90

    I recently came across this problem when we moved from Windows 32bit to Windows 64bit, using SQL 2005/2008 64bit. There are a lot of openrowset/opedatasource for older Excel 2003 xls.

    I've finally found a free solution. It seems like Microsoft has replacement Office driver for "Microsoft.Jet.4.0" with its "Microsoft.ACE.OLEDB.12.0".

    For 32 bits download the 2007 Office System Driver:Data Connectivity Components

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    For 64 bits download the 2010 beta (Release June 2010)

    http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=ja&displaylang=en

    Installation of both doesn't require reboot. The 32/64 bits odbc will have the ACE driver for Access/Excel/dbase/cvs/txt after installation and the SQL Provider drivers "Microsoft.ACE.OLEDB.12.0" will be in your 32/64bit SQL Engine.

    After installation, in SQL Engine, run the following

    USE [master]

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

    GO

    The "Microsoft.ACE.OLEDB.12.0" driver is backward compatible to Excel 2003, so you really can open xls files with this driver.

    On a separate note, I've installed Office 2010 beta application, and it did not come with Microsoft.Jet.4.0, so my question is, is Microsoft replacing "Microsoft.Jet.4.0" with "Microsoft.ACE.OLEDB.12.0" on all their future development and slowly phrasing out the jet?

  • jvos.gm

    SSC Journeyman

    Points: 91

    Great article, do you have a solution for importing a large csv file aswell? I tried your solution but keep on getting the “Invalid Excel worksheet. No data in the worksheet” error.

  • Servercentral-1016448

    SSC Enthusiast

    Points: 166

    For import of csv files i would go for someting like this:

    SELECT *

    INTO tabelA

    FROM OPENROWSET('MSDASQL',

    'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Data;','select * from tabelB.csv')

  • rcouldry

    SSC Enthusiast

    Points: 101

    Great article - I'm looking for clues to solve the following problem.

    Right now, my scheduled SSIS package (SQL08) to import Excel spreadheet data into SQL 08 works when the Excel spreadsheet is closed. It also works when the spreadsheet is open if I run the job manually from the Import Data wizard.

    However, the scheduled SSIS package fails with the 'Jet engine cannot open file' error when the spreadsheet is open.

    Is it possible to schedule an SSIS package to import data from an open Excel file to SQL Server?

    Many thanks for any tips.

  • evolutional

    SSC Enthusiast

    Points: 119

    Jim Cheong (2/17/2010)


    I recently came across this problem when we moved from Windows 32bit to Windows 64bit, using SQL 2005/2008 64bit. There are a lot of openrowset/opedatasource for older Excel 2003 xls.

    I've finally found a free solution. It seems like Microsoft has replacement Office driver for "Microsoft.Jet.4.0" with its "Microsoft.ACE.OLEDB.12.0".

    For 32 bits download the 2007 Office System Driver:Data Connectivity Components

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    For 64 bits download the 2010 beta (Release June 2010)

    http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

    Useful, thanks Jim

  • Rod Weir

    Old Hand

    Points: 323

    I wrestled with this for quite awhile. I ended up doing a very manual thing. I blogged about it here

    http://www.prd-software.com/blogs/entryid/60/importing-or-converting-microsoft-excel-data-into-sql-server.aspx

    Hope it helps someone.

    Rod

  • RichB

    SSCrazy Eights

    Points: 9651

    So have you also got a nice bit of TSQL that will parse a directory of 1000s of excel sheets, open them all and save them as xml?

  • Tom Garth

    SSCertifiable

    Points: 6173

    Great article!

    Really good info from Jim Cheong too!

    I know I'll be upgrading some old Excel work sooner rather than later, and the current lack of a 64 bit Jet driver has had me wondering what best to do.

    Thanks!

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Atradius

    SSC Eights!

    Points: 829

    Cannot download your stored procedure - link is broken?

  • peterhe

    SSChampion

    Points: 11362

    The link in the article does not work anymore. Please use the links in the "Resources" in the end of the article.

  • Denis W. Repke

    SSC-Addicted

    Points: 426

    I tried your code on a WinXP 64-bit workstation connected to SQL Server 2008 64-bit. I always get this error:

    Cannot bulk load. The file "C:\TestBook.xml" does not exist. at line 1

    Msg 50000, Level 16, State 1, Procedure uspImportExcelSheet, Line 497

    Cannot bulk load. The file "C:\TestBook.xml" does not exist.

    No matter where I put the "TestBook.xml" file ("C:" or a network drive), it cannot find the file.

    Am I missing something?


    Denis W. Repke

  • sckemp1

    Grasshopper

    Points: 16

    When running this script, I get:

    SET @WorkSheet=NULL; SELECT @WorkSheet=CAST(BulkColumn as xml) FROM OPENROWSET (BULK 'M:\HTS\Export\currency.xls', SINGLE_BLOB ) A;

    XML parsing: line 0, character 0, unrecognized input signature at line 1

    Msg 50000, Level 16, State 1, Procedure uspImportExcelSheet, Line 497

    XML parsing: line 0, character 0, unrecognized input signature

    Tried a few xls files, all gave the same error.

Viewing 15 posts - 1 through 15 (of 70 total)

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