topic Re: Loading xls or xlsx files in QlikView

Loading xls or xlsx files

Sat, 09 May 2015 10:53:21 GMT

Hi All,

While loading xls files, we use biff and while loading xlsx files, we use ooxml.

Is there a common format available for loading both xls and xlsx files

Re: Loading xls or xlsx files

robert_mika — Sat, 09 May 2015 11:18:26 GMT

Look here for explanation:

biff Vs. ooxml

Re: Loading xls or xlsx files

petter — Sat, 09 May 2015 11:24:11 GMT

When the files have the extension XLS or XLSX they have to be in that particular format BIFF or OOXML. So they go together always. You can't have a file name .XLS and not store it in BIFF-format. If you do it will be invalid/corrupt.

But you can translate your XLS-files into XLSX and then they turn from BIFF into OOXML and vice versa - the latter with the risk of losing functionality but probably not any data.

Re: Loading xls or xlsx files

marcus_sommer — Sat, 09 May 2015 12:01:50 GMT

If you have influence to the file-fomat you should avoid xlsx as format then it's far away from optimized and loading from these files is very slow compared to xls or txt/csv-files.

To handle different file-formats within a folder you could use filelist in a loop: Loops in the Script.

- Marcus

Re: Loading xls or xlsx files

Fri, 17 Mar 2017 12:02:21 GMT

Hi Asma,

You can use this function:

LET vPath = 'C:\user\test\documents\teste.xls';

LET vExtension = Mid('$(vPath)', Index('$(vPath)', '.xls'), len('$(vPath)'));

Re: Loading xls or xlsx files

Wed, 26 Jul 2017 18:22:35 GMT

Why is .xlsx slower than .xls?

Re: Loading xls or xlsx files

marcus_sommer — Wed, 26 Jul 2017 20:17:33 GMT

xls is a proprietary binary file-format and xlsx is an "open" xml file-format which is more structured and had more overhead for it. In my experience xlsx needs significantly longer run-times as xls whereby if possible I avoid both formats and store the data as simple txt-files.

- Marcus

Re: Loading xls or xlsx files

Wed, 26 Jul 2017 20:35:11 GMT

Ah gotcha, thank you!