none
Feeding Excel 2007 .XLSX doc to an SQL server

    Question

  • I need some way to read in an Excel 2007 file (xlsx or xls) to a SQL database.  What's the best method to do this?  Do I convert to XML first?  Can I read in an XLS file instead?  Or is there a more suitable format to convert to?  The Excel file contains a very large table too large to print (except in large format printing).  All I want is to store this data in a database that, when queried, returns the same data in a table, but in different ways.  And are there some sample C# code that I could use, at least to show how to read the file in and write it to the database?  I can do everything else.  I'm using SQL server 2008 Developer edition.  Thanks.

    • Edited by mdoc7 Wednesday, October 14, 2009 2:51 AM clarification
    Wednesday, October 14, 2009 2:49 AM

Answers

  • Hi,
    If u have the data in one sheet of the excel file you can import the data directly into a table of your sql database. For that you will have to right click on the database name in management studio, select tasks -> import data and then just follow the wizard.

    If the data is in mulitiple sheets you can do the transfer by executing separate import tasks. Make sure not to truncate or overwrite existing rows.

    Thanks,
    Zinat.
    • Marked as answer by mdoc7 Wednesday, October 14, 2009 10:21 PM
    Wednesday, October 14, 2009 4:39 AM

All replies

  • Update:

    I'm able to link to a test file (Excel file) to my database
    http://support.microsoft.com/kb/306397/EN-US/#appliesto
    but at some point in traversing the linked server, I got an exception, from using (perhaps the wrong) provider for the interface.  The instructions say to use Microsoft Jet 4.0 OLE DB Provider, but this provider isn't available in SQL Server Management Studio (SSMS) 2008.  The website details instructions for SSMS 2005; I have 2008.

    I used the instructions under the heading "Using SQL Server Management Studio or Enterprise Manager to configure an Excel data source as a linked server" in the webpage, under "more information" section. 

    Still seeking an answer to my original post...
    • Edited by mdoc7 Wednesday, October 14, 2009 4:38 AM correction
    Wednesday, October 14, 2009 4:34 AM
  • Hi,
    If u have the data in one sheet of the excel file you can import the data directly into a table of your sql database. For that you will have to right click on the database name in management studio, select tasks -> import data and then just follow the wizard.

    If the data is in mulitiple sheets you can do the transfer by executing separate import tasks. Make sure not to truncate or overwrite existing rows.

    Thanks,
    Zinat.
    • Marked as answer by mdoc7 Wednesday, October 14, 2009 10:21 PM
    Wednesday, October 14, 2009 4:39 AM
  • Success, I can see the data in the database from my Excel test file.  Thanks.  Much easier than I thought.

    New question:  suppose the test file contains graphics or pictures.  Will that cause problems or will they be simply filtered out?  (I don't need the graphics except they need to stay in the Excel file only)
    Wednesday, October 14, 2009 3:41 PM
  • tnx Zinat, was helpfull..............
    Friday, July 23, 2010 8:16 PM
  • Zinat,

    Is there a way to import the data using a query? I need to create a query so I can have it run once every hour. The excel file is populated by a web form, which then needs to update the database.

    Specs:

    Office 2003 or 2007, SQL Server 2008 (64-bit), Windows Server 2008

    I downloaded the Microsoft ACE OLEDB 12.0 Provider (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en), and then creating a linked server in SQL Server 2008 with the following query:

    exec sp_addLinkedServer
    @server= 'XLFile',
    @srvproduct = 'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\VISUAL_Contacts.xlsx',
    @provstr = 'Excel 12.0; HDR=Yes'

    It creates the linked server successfully, but when I attempt to query the linked server, I get this error:

    Msg 7302, Level 16, State 1, Line 1
    Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLFile".

    Any ideas would be greatly appreciated. Thank you in advance.

    Friday, September 24, 2010 7:41 PM