none
Error Importing from .xlsx file to MSQL 2008

Answers

  • Hi Thehobbit,

     

    I downloaded the .xlsx file from your link trying to reproduce your problem, and I imported it to my SQL Server 2008 R2, it was successful imported. So I think the file is okay for import.

     

    According to the error message from "Report1.txt", "An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.", the possible cause for this issue should be the excel file is locked by other processes.

     

    As a possible solution, would you please kindly resave this file and name it to other file name to see if the issue will be fixed?

     

    Also it will be great if we can keep the ACE(Access Database Engine) up to date, would  you please download the latest ACE and install it? Please check the following information for details:

    Microsoft Access Database Engine 2010 Redistributable: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

     

    If there are more questions, please feel free to ask.

     

    Thanks,

    Grace


    Please kindly mark the answer if it is a workaround. Thanks a lot *^_^*
    • Marked as answer by TheHobbit81 Wednesday, October 20, 2010 9:05 AM
    • Edited by Grace Liu Thursday, October 21, 2010 1:16 AM
    Wednesday, October 20, 2010 6:50 AM

All replies

  • I have seen the error

    Description: "External table is not in the expected format.".
    (SQL Server Import and Export Wizard)

    Probably some characters are invalid in EXCEL file or do not match with SQL Server table

    Can you check it out?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 17, 2010 7:38 AM
    Moderator
  • Thanks for the reply Uri,

     

    Do you mean these special charatures? http://msdn.microsoft.com/en-us/library/aa226544(SQL.80).aspx

     

    Would having these affect the import in a way that would generate the aformentioned errors?

    *Edit* I have no "+" or "/", it says that each of my column heading contains "?" but there are none there. Most items have the "%" symbol there are also lots of "#" symbols and lots of "&" symbols.

    I just wanted to check before I did a massive "Find and Replace"

     

    Thanks,

    Paul

    • Edited by TheHobbit81 Monday, October 18, 2010 2:06 AM Update of info
    Monday, October 18, 2010 1:02 AM
  • I have some questions, r u trying to create a new table with the data or pushing to an existing table?

    If new, are you letting SSIS decide the data structure or you are specifying it?

    If existing, is table structure matching with the data in terms of number/type/size of columns. 

    Do you have a blank row at end of the valid data? (You can check this by pressing Ctrl+End - it should stop in last row last column of your valid data?

    In case it goes beyond the valid data row (blank row after the last valid data row). It is possible that SSIS throws an exception when it expects a numerical value but encounters a blank (on full blank row). Most likely that you have this problem. (if you have modified the excel file).

    Please check through. One of these are possible reasons of a invalid data / expected value not given kind of errors.


    Vasudev S.
    Monday, October 18, 2010 2:31 PM
  • Hi Vasudev,

     

    1. A new database

    2. SSIS is deciding the data structure, When I click the [Edit Mapping] button it shows that it will be using the "nvachar" Type for all fields except 'product_price' which has been set to "float"

    3. Always using a new database and will be for each new import as required (also, I'm not sure how to append data)

    4. No, there is no blank row. Originally there was and I removed them using Notepad

    Q. Could it be a problem with special charatures?

    ~ Paul

     

    Monday, October 18, 2010 10:44 PM
  • Hi Thehobbit,

     

    I downloaded the .xlsx file from your link trying to reproduce your problem, and I imported it to my SQL Server 2008 R2, it was successful imported. So I think the file is okay for import.

     

    According to the error message from "Report1.txt", "An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.", the possible cause for this issue should be the excel file is locked by other processes.

     

    As a possible solution, would you please kindly resave this file and name it to other file name to see if the issue will be fixed?

     

    Also it will be great if we can keep the ACE(Access Database Engine) up to date, would  you please download the latest ACE and install it? Please check the following information for details:

    Microsoft Access Database Engine 2010 Redistributable: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

     

    If there are more questions, please feel free to ask.

     

    Thanks,

    Grace


    Please kindly mark the answer if it is a workaround. Thanks a lot *^_^*
    • Marked as answer by TheHobbit81 Wednesday, October 20, 2010 9:05 AM
    • Edited by Grace Liu Thursday, October 21, 2010 1:16 AM
    Wednesday, October 20, 2010 6:50 AM
  • Thanks for the reply Grace.

     

    I downloaded the ACE and ran it.

    Opened the Excel file and saved it to my local HDD, renaming it in the process.

    I made sure that Excel was closed properly and tried the import again.

    Success!!!

    It has worked, import complete.

    Only problem is that now I've compleatly forgotton SQL, it's been about 8 years since I last used it. I can't even remember how to do a simple select statement to display my data. :(

    Time to hit Google.

    • Edited by TheHobbit81 Wednesday, October 20, 2010 11:05 AM Typo
    Wednesday, October 20, 2010 9:05 AM