loading xls vs xlsx files

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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);

[ashfaq_haseeb]

Hi,

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

Please donot create multiple threads for the same

[Colin-Albert]

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.

[Anonymous]

Hi YB

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

Regards

Harsha

[ashwanin]

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.

[marcus_sommer]

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)'

next

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

- Marcus