topic ScriptError: Cannot read BIFF file - Any ideas? in QlikView

ScriptError: Cannot read BIFF file - Any ideas?

Wed, 02 May 2012 09:45:07 GMT

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:

Script still loads fields after error:

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

ScriptError: Cannot read BIFF file - Any ideas?

ekech_infomotio — Wed, 02 May 2012 10:36:15 GMT

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

ScriptError: Cannot read BIFF file - Any ideas?

Wed, 02 May 2012 10:57:30 GMT

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.

ScriptError: Cannot read BIFF file - Any ideas?

Wed, 02 May 2012 11:07:50 GMT

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

Re: ScriptError: Cannot read BIFF file - Any ideas?

Mon, 30 Sep 2013 19:06:35 GMT

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

Re: ScriptError: Cannot read BIFF file - Any ideas?

qlikmpate0 — Tue, 22 Oct 2013 12:10:07 GMT

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

Re: ScriptError: Cannot read BIFF file - Any ideas?

Thu, 20 Nov 2014 13:30:16 GMT

Could you pls share the macro you used?

Re: ScriptError: Cannot read BIFF file - Any ideas?

Tue, 12 May 2015 18:50:04 GMT

Hi ekech_infomotion ,

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

Re: ScriptError: Cannot read BIFF file - Any ideas?

Tue, 12 May 2015 19:21:52 GMT

Edgar,

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

Re: ScriptError: Cannot read BIFF file - Any ideas?

MikeH1983 — Tue, 16 Jun 2020 15:01:31 GMT

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.