Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

ScriptError: Cannot read BIFF file - Any ideas?

Hi All,

I have written a script that loads specific fields from all .xls files within a folder:

Load Date,
     Budget,
     Forecast,
     [Outlook This Week],
     [Outlook Last Week],
      filename() as
NameFrom C:\testLOADALL\*.xls
(biff, embedded labels, header is 2 lines, table is Sheet1$);

This probem is, when I run this script I get a 'Cannot read BIFF file' error however the correct fields are still loaded from all .xls files in the folder:

scripterror.bmp

Script still loads fields after error:

scripterror2.bmp

Does anybody know why this error is happening yet I am getting the correct result? I modified my script to read .xlsx ooxml files and I didn't receive an error however all of the files I need to load are .xls.

Any help/thoughts appreciated,

Matt

1 Solution

Accepted Solutions
ekech_infomotio
Partner - Creator II
Partner - Creator II

Hi Matt,

seems that one of your .xls isn't a "real" xls-File. Some programs export csv-files and call them .xls...

I remember having this kind of problem at my previous job. I solved it by writing a little EXCEL-Macro which opened all files in the path and saved them explicitly as .xls (Excel 97-2003).

regards,

Edgar

View solution in original post

9 Replies
ekech_infomotio
Partner - Creator II
Partner - Creator II

Hi Matt,

seems that one of your .xls isn't a "real" xls-File. Some programs export csv-files and call them .xls...

I remember having this kind of problem at my previous job. I solved it by writing a little EXCEL-Macro which opened all files in the path and saved them explicitly as .xls (Excel 97-2003).

regards,

Edgar

Not applicable
Author

I agry with Edgar. Coming to your next Q.. Script will load after error If you press OK. If you would have press Cancel It would have closed and would have asked you To Load with old data.

Swapnil.

Not applicable
Author

Thanks for your response Edgar and Swapnil.

I tried creating an Excel .xls file and the script worked fine with it so it looks like I will have to create a macro like you suggested.

Matt

Not applicable
Author

Edgar,

Do you have any suggestions on how to write the Excel macro? Also, will the macro need to be written in each individaul excel file? I have multiple excel files within a folder  that I'll need to convert to 'real' .XLS files

Thanks!

Liz

qlikmpate0
Creator II
Creator II

How do you know your file is not an xls file?

Not applicable
Author

Could you pls share the macro you used?

Not applicable
Author

Hi ekech_infomotion ,

Can you please share the macro which actually converts to csv from xls.

Not applicable
Author

Edgar,

Do you have any suggestions on how to write the Excel macro? Can you please explain me .

MikeH1983
Contributor III
Contributor III

I had this problem with trying to automate the refresh of a saved source XLS file.

I receive the file automatically by email. I was using PowerAutomate to save the email attachment to my QlikSense source directory in my file system via on-premises gateway. This worked, but the Qlik script would not read the file, with the BIFF error below. Opening the document and closing it without saving somehow fixed it. 

My solution was to instead have the PowerAutomate flow save the email attachment to a file on Sharepoint. Then next step, fetch the content from the newly created sharepoint file and save this to the File System. Somehow, the process gets the file format right when taking it from Sharepoint, but not from an email attachment.

Just FYI in case it helps anyone.