topic Re: loading xls vs xlsx files in QlikView

loading xls vs xlsx files

Mon, 24 Nov 2014 10:27:48 GMT

Hi,

I am facing an issue while loading xlsx files, I get an error saying "Cannot read from BIFF file".

However, when I save it as xls, it works fine.

So, I tried writing a macro which opens the excel files and replaces xlsx with xls. This did as it was supposed to do, however did not solve the issue as I still get the same error. Also, I will have to deploy the app on a server which will have it's own issues of running a macro.

I have been wondering about the relevance of this approach, as in the coming years, xls files might become irrelevant and outdated and as such saving files as a older file format shouldn't be the fix.

I wanted to know, if there are any alternative ways to handle this and views on the above approach.

Thanks

Re: loading xls vs xlsx files

Gysbert_Wassenaar — Mon, 24 Nov 2014 10:38:59 GMT

If you load .xls files you need to use biff as file format. If you want to load .xlsx files then you need to use ooxml as file format

XLS:

LOAD FROM [myfile.xls]

(biff, embedded labels, table is Sheet1$);

XLSX:

LOAD FROM [myfile.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: loading xls vs xlsx files

ashfaq_haseeb — Mon, 24 Nov 2014 10:54:05 GMT

Hi,

Check this http://community.qlik.com/thread/143259

Please donot create multiple threads for the same

Re: loading xls vs xlsx files

Colin-Albert — Mon, 24 Nov 2014 10:58:36 GMT

As Gysbert Wassenaar says, the format for Xls and xlsx files differs with the BIFF and OOXML line.

If you are unsure whether users will be saving the file as xls or xlsx, the easiest option is to set your load script to load both formats and the auto-concatenation in QlikView will combine both sets of files into a single table.

Re: loading xls vs xlsx files

Anonymous — Mon, 24 Nov 2014 11:26:55 GMT

Hi YB

just try like this insted of XLS or XLSM or XLSX just use XLS*

Regards

Harsha

Re: loading xls vs xlsx files

ashwanin — Mon, 24 Nov 2014 12:49:51 GMT

Hi,

I think your xlsx file is password protected or can be read only.

and when you save it xls the protection removed.

Try to save it in xlsx but with different name. or check you are getting the same error or not.

Re: loading xls vs xlsx files

marcus_sommer — Mon, 24 Nov 2014 12:55:55 GMT

A simple method to handle such uncertain file-extensions is to load these files in a loop through the folder per filelist and in a second step you used a variable for the file-format. This should be have the following main-logic:

for each vFile in filelist(YourFolder\FilePrefix_*.xls*)

vFileFormat = if(subfield('$(vFile)', '.', -1) = 'xls', 'biff ...', 'ooxml ...'

Load * From '$(vFile)' '$(vFileFormat)'

If you searched for "for each ... " or filelist/dirlist you will find many examples.

- Marcus