ODBC import error on Kendo UI Excel file

[Chris]

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

[Chris]

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....

[Alex Hajigeorgieva]

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

 

[Chris]

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

[Alex Hajigeorgieva]

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

 

[Roberto]

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.

[Chris]

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

[Roberto]

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.

 

[Alex Hajigeorgieva]

Hello all,

Thank you for sharing your observations regarding the styles. files. You could perform this sequence of zipping a file and replacing the styles. 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. 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