ODBC import error on Kendo UI Excel file

9 posts, 0 answers
  1. Chris
    Chris avatar
    4 posts
    Member since:
    Sep 2016

    Posted 29 Sep 2016 Link to this post

    Hi all

     

    I'm receiving Excel .xlsx files from a customer that (according to the file properties) is using Kendo UI to generated these files.

     

    These Excel files fail to import to my database using 'Microsoft Excel ODBC driver' version 15.00.4543.1 dated 23/01/2014.

    Error message: [Microsoft][ODBC Excel Driver] External table is not in the expected format.

     

     

    I've done a lot of internet searching with no successful work around.

    Has anyone else had this issue?

     

    The files open OK in Excel and if I save the unchanged file, the ODBC issue does not occur.

     

    I'm running Office 2016 + 'MS Access Runtime 2013' (unable to install Runtime 2016 with a clash with Office / Microsoft website reports version 2013 and 2016 are near identical).

     

    Any suggestions are appreciated.

    Thanks

    Chris

  2. Chris
    Chris avatar
    4 posts
    Member since:
    Sep 2016

    Posted 29 Sep 2016 Link to this post

    So here's a weird quirk - if I repeat the above import, the ODBC error message changes to: ODBC Excel Driver General error unable to open registry key temporary (volatile) ace DSN...

    Odd....
  3. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    193 posts

    Posted 04 Oct 2016 Link to this post

    Hello Chris,

    Could you please let me know which one of our libraries/tools you are using to import the .xlsx files to your database? Are you experiencing this issue with all .xlsx files? Could you provide a code snippet?

    I did some research on the topic and this behaviour seems to occur when the access rights to a folder are limited, an incorrect driver is used or the database connection string contains the wrong Excel version. An SO thread which has a lot of upvotes seems to address the issue:

    http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format

    Should you need further information regarding any of the Progress/Telerik/Kendo UI/DevCraft products, please feel free to get back to me with more information so I can assist in the most efficient manner.

    Kind Regards,
    Alex
    Telerik by Progress
     
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
  4. Chris
    Chris avatar
    4 posts
    Member since:
    Sep 2016

    Posted 04 Oct 2016 in reply to Alex Hajigeorgieva Link to this post

    Hi Alex

    Thanks for your reply and time spent researching the issue.

    The error is occurring in a 3rd party database tool which I don’t have access to the source code.

    I’ve emailed the developers for help on the subject and await their reply.

    Last week I ‘dissected’ the Kendo UI .xlsx file and found the following issues:

    - I discovered an .xlsx file is a bunch of zipped XMLs file.
    - In the main XML file ( \xl\worksheets\sheet1.xml as named by each Excel sheet), every cell of the Excel sheet is referenced.
    - In the Kendo UI file the cells are constructed in a repeating XML structure: <c r="H2" t="n"><v>13.2</v></c> <c r="I2" t="n"><v>10.4</v></c> <c r="J2" t="n"><v>17.1</v></c>
    - In a regular .xlsx file or even re-saving in Excel the Kendo UI file, the t="n" component does not exist.
    - If I remove these t="n" (find and replace of strings) I have no issues importing the data / no error messages.

    Do you know if the Kendo UI module constructs these XML blocks?

    Kind regards

    Chris

  5. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    193 posts

    Posted 07 Oct 2016 Link to this post

    Hello Chris,

    Thank you for digging deeper into the structure of the Kendo UI generated .xlsx files. The t="n" represents the cell type. In this case when t="n", it means that the type of data contained in the cell is a number:

    http://officeopenxml.com/SScontentOverview.php

    I think that the error stems from the database column format differing from the one described in the .xlsx number format.


    Regards,
    Alex
    Telerik by Progress
     
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
  6. Roberto
    Roberto avatar
    8 posts
    Member since:
    Mar 2015

    Posted 21 Dec 2016 Link to this post

    Hi,

    i have the same problem (http://www.telerik.com/forums/kendogrid-saveasexcel-file-is-not-accessible-with-oledb)

    my code:

    string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=file.xlsx;Extended Properties=Excel 12.0;";
    using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
    {
        conn.Open(); <--- at this point I get the exception "External table is not in expected format"

    Only with excel files generated by kendo grid.

    With excel files created manually, i have no problems.

  7. Chris
    Chris avatar
    4 posts
    Member since:
    Sep 2016

    Posted 21 Dec 2016 in reply to Roberto Link to this post

    Hi Roberto

    I gave up trying to find a Kendo or Microsoft solution and ended up scripting the following work around:

    1) An .xlsx file is just a zip file so change the .xlsx extension to .zip and browse the contents.

    2) Through experimentation discovered the Kendo styles.xml file (in folder xl) is the issue. Replace this file with a styles.xml file from any Excel created xlsx file.

    3) Change the extension back to .xslx and all worked fine.

     

    Try this and please let me know if this works for you.

    Chris

  8. Roberto
    Roberto avatar
    8 posts
    Member since:
    Mar 2015

    Posted 21 Dec 2016 in reply to Chris Link to this post

    Hi Chris,

    with this workaround the problem does not occur.

    But it is not a permanent solution, since this action must be performed by users who use the application, which can not be asked to do this procedure to use the file.

     

  9. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    193 posts

    Posted 26 Dec 2016 Link to this post

    Hello all,

    Thank you for sharing your observations regarding the styles.xml files. You could perform this sequence of zipping a file and replacing the styles.xml file on the server. Of course, this is not an optimal solution and I am hoping to be able to help you avoid these steps altogether.

    The Kendo UI Excel Export needs the styles.xml file and it is interpreted by MS Excel correctly. We are open to modifying the generated schema to meet the format expected by the Microsoft ODBC Driver as long as it does not cause any breaking changes.

    I have logged this as an issue/enhancement and you may comment on it with any further findings at:

    https://github.com/telerik/kendo-ui-core/issues/2566
     
    Kind Regards,
    Alex Hajigeorgieva
    Telerik by Progress
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top