ODBC import error on Kendo UI Excel file

5 posts, 0 answers
  1. Chris
    Chris avatar
    3 posts
    Member since:
    Sep 2016

    Posted 29 Sep 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
    3 posts
    Member since:
    Sep 2016

    Posted 29 Sep 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. Kendo UI is VS 2017 Ready
  4. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    114 posts

    Posted 04 Oct 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.
  5. Chris
    Chris avatar
    3 posts
    Member since:
    Sep 2016

    Posted 04 Oct 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

  6. Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    114 posts

    Posted 07 Oct 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.
Back to Top
Kendo UI is VS 2017 Ready