none
Importing Excel 2007 and/ or DBF files into SQl server 2005

    Question

  • Greetings,

    I'm having a tough time importing some of my legacy database into sql.
    I have a number of dbase (IV) files I need to get into SQL. I have tried building a SSIS package with either an foxpro oledb connection or a jet 4.0 one, none of them work bec. of inconsistencies in the data format in my tables (e.g. date fields, etc).

    I have tried to save the .dbfs as excel 2007 files, taking advantage of the larger space that comes with '07. Problem is you can't use the import/export wizard with 2007 for some reason and I haven't been able to create a package with the access 12 oledb as I have read.

    I have to get some crucial data out of that old system and into the new one and I can't seem to be able to import them properly.

    Any hints on what I should do ? (maybe I'm doing something awfully wrong)

    Thank you for taking the time to answer my question,
    Val
    Saturday, May 12, 2007 2:25 PM

Answers

  • Hi Jay,

     

    for Excel you will need to set the Extended Properties in the connection form. Set it to "Excel 12.0" and that should take care of this problem.

     

    Thanks,

    Bob

     

     

    Wednesday, February 27, 2008 6:52 AM
    Owner
  • Try by looking at this : http://msdn2.microsoft.com/en-us/library/aa337084.aspx and also  you'll need to configure your connection manually to connect. Set up a Jet OLEDB Connection - point to your folder containing the DBase files. Click the "All" button and change the "Extended Properties" to "DBASE IV".

    Sunday, May 13, 2007 8:15 AM
  •  Nick in Orange wrote:
    What do I do if I don't even see the choice in step 1 for a data source? I am running SQL 2005 (9.00.3054.00)


    use the excel connection manager instead of the ole db connection manager.  "Extended Properties" appears in the connection string property.
    Saturday, March 08, 2008 9:57 AM
    Moderator

All replies

  • If you have SP2 installed, you should be able to load data from Excel 2007 format using the I/E wizard. Do not use the Excel connection, but use the new OLE DB provider for Office 2007. You will need to set extended properties to "Excel 12.0".

     

    Let me know if you need more assistance.

     

    Thanks.

    Sunday, May 13, 2007 6:03 AM
    Owner
  • Try by looking at this : http://msdn2.microsoft.com/en-us/library/aa337084.aspx and also  you'll need to configure your connection manually to connect. Set up a Jet OLEDB Connection - point to your folder containing the DBase files. Click the "All" button and change the "Extended Properties" to "DBASE IV".

    Sunday, May 13, 2007 8:15 AM
  •  

    Hello Bob, hopefully you are still monitoring this thread!

     

    I am having the same problem with Excel 2007 and the import export wizard. My preference is to use the wizard over the SSIS package designer as I import MANY different xlsx files every week; creating a package is not as quick as the wizard.

     

    I am running SQL Server 9.00.3054.00 (SP2) and Office 2007 12.0.6214 (SP1). 

     

    Following are my steps:

    1. Right click on the database name in the Object Explorer
    2. Select Tasks
    3. Select Import Data
    4. Click the Data Source list box and select "Microsoft Office 12...OLE DB Provider"
    5. Click the Properties button
    6. Enter the path and file name (c:\test.xlsx) in the Data Source text box
    7. Click OK button
    8. Click Next button

    The following error is produced: 

     

    TITLE: SQL Server Import and Export Wizard
    ------------------------------

    The operation could not be completed.

    ------------------------------
    ADDITIONAL INFORMATION:

    Cannot open database ''.  It may not be a database that your application recognizes, or the file may be corrupt. (Microsoft Office Access Database Engine)


     

    I have read in many places that the provider should work for Excel as it does for Access. I have not found that to be true. My assumption is that I am doing something incorrectly, but perhaps not.

     

    Thanks very much for any help.

     

    Jay

    Tuesday, February 26, 2008 10:49 PM
  • Hi Jay,

     

    for Excel you will need to set the Extended Properties in the connection form. Set it to "Excel 12.0" and that should take care of this problem.

     

    Thanks,

    Bob

     

     

    Wednesday, February 27, 2008 6:52 AM
    Owner
  • Yes!!  That's it, mate. Thanks very much!

     

    To clarify for furture readers:

    1. Select "Microsoft Office 12.0 Access Database Engine OLE DB Provider" as the Data Source
    2. Click Properties
    3. Click on the "All" tab of the Data Link Properties form
    4. In the 'Data Source' property, enter the Excel 2007 workbook's path and file name (type it out as neither an ellipsis nor browse button are provided)
    5. In the 'Extended Properties' property, enter "Excel 12.0"
    6. Click OK and continue as you normally would

    Best regards!

     

    Wednesday, February 27, 2008 4:39 PM
  • What do I do if I don't even see the choice in step 1 for a data source? I am running SQL 2005 (9.00.3054.00)
    Friday, March 07, 2008 9:04 PM
  •  

    The provider is installed automatically with Office 2007.

     

    I do not believe it is available as a separate download. Of course, I could be very wrong on that.

     

    Best of luck.

    Friday, March 07, 2008 10:50 PM
  •  Nick in Orange wrote:
    What do I do if I don't even see the choice in step 1 for a data source? I am running SQL 2005 (9.00.3054.00)


    use the excel connection manager instead of the ole db connection manager.  "Extended Properties" appears in the connection string property.
    Saturday, March 08, 2008 9:57 AM
    Moderator
  •  Duane Douglas wrote:
     Nick in Orange wrote:
    What do I do if I don't even see the choice in step 1 for a data source? I am running SQL 2005 (9.00.3054.00)


    use the excel connection manager instead of the ole db connection manager.  "Extended Properties" appears in the connection string property.


    for clarification, the ssis excel connection manager doesn't work for excel 2007 (excel 12).

    please see the following link for information about connecting to excel 12: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2919756&SiteID=17
    Saturday, March 08, 2008 10:21 AM
    Moderator