topic Re: XLSX Data not loaded until xlsx modified in QlikView

XLSX Data not loaded until xlsx modified

jaibau1993 — Sat, 16 Nov 2024 02:07:26 GMT

Hi all!

I am facing a weird issue. Let's see if someone can bring light to the problem.

I want lo load the following Excel file into QlikView (QV and Excel attached)

so I write the following code in my QlikView:

As you can see, the Excel file has data but when I run the above code it fails:

If I use the "Table Files..." button of the script inorder to build the load statement using the wizard, I realize that QlikView is seeing nothing from the Excel file. If I now open the Excel file and I save it (with no modification, just save the file) then everything works fine using the original code above provided.

I am using QV 11.20 SR12 in Windows 10 pro.

Some clue?

Thanks!

Jaime.

Re: XLSX Data not loaded until xlsx modified

jaibau1993 — Fri, 25 Oct 2019 06:28:44 GMT

Bump!

Any idea on this?

Re: XLSX Data not loaded until xlsx modified

marcus_sommer — Fri, 25 Oct 2019 08:58:58 GMT

It's a known issue - related to the ooxml file-format. AFAIK it's not absolutely clear defined from MS so that there is some room of interpretation - especially if the files aren't created from the Excel desktop client else from the various Excel online clients (expecially in combination with sharepoint) or any third-party tools.

I don't know how Qlik accessed the xlsx - with own build logic and/or logic/libraries from MS - but it seems not to be so error-tolerant as Excel and regocnized therefore no valid data. An opening and saving in Excel "repairs" it then again.

Workarounds for it could be to prompt some changes to the source, to automate the above mentioned opening/saving with any macro-batches, to unzip the xlsx and to load then directly from the various xml-files, trying to load the xlsx per odbc, probably some more ...

- Marcus

Re: XLSX Data not loaded until xlsx modified

jaibau1993 — Fri, 25 Oct 2019 10:42:16 GMT

Thanks Marcus!

We solved it temporaly by creating a batch file that open, save and close all xlsx files before Qlik script execution. I now see that we were correct in our approach 🙂

I'll keep it in mind for the future.

Regards,

Jaime.