topic Re: Help with reading Excel #Ref in QlikView

Help with reading Excel #Ref

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Recently I have a use case from user to see if Qlik can be used to detect excel formula that return #N/A or #REF with the below conditions:

conditions:

1)User do not want to change their formula to like if is #N/A then return "NA" or if is #REF return "#REF" in their excel spreadsheet.

2)We also do not want to use is null then assumed is #REF .

Worse case is we create another layer between QV and flat files with vba/python to solve the above use case.

I am wondering if Qlikview can be used to solve this problem?

Regards,

Re: Help with reading Excel #Ref

petter — Fri, 03 Nov 2017 09:06:51 GMT

Yes it is rather straightforward:

1) Make a copy of the XLSX-file and change the extension to ZIP

2) Use Windows explorer and extract all the files from the ZIP into a directory

3) Go into Load Script Editor and select [Table Files] button

4) Navigate to the subdirectory of the unzipped directory called "xl" and then into "worksheets" subdirectory

5) Open the xml-file of the worksheet you want to analyse

6) Press the [Finish]-button

you will have a field named "c/v" that you can filter on to find all #N/A and #REF! .... the field "c/r" will give the column reference in the spreadsheet.

Re: Help with reading Excel #Ref

Anonymous — Fri, 03 Nov 2017 09:37:41 GMT

Thank you sir,sounds like a good solution, let me try this out

Re: Help with reading Excel #Ref

Anonymous — Fri, 03 Nov 2017 09:44:47 GMT

hi @Petter Skjolden

Is it possible not to convert the excel to zip file first?

Is it possible to convert to zip in Qlikview?

This is because we do not want user to manually change the file type into zip

Is it possible to do the same for xls file?

regards,

Tian Kian

Re: Help with reading Excel #Ref

petter — Fri, 03 Nov 2017 13:55:19 GMT

1) No - QlikView can't directly read zip-files in this way.

2) Yes it is. Either by:

A) an EXECUTE statement in the load script. That means that you will have to have an unzip utility installed.

B) with a QlikView VBScript-function as documented here:

How to extract Sheet Names from an Excel XLSX-file without ODBC

3) These methods only work with XLSX. But you can also handle XLS with the use of COM automation either from PowerShell invoking it with EXECUTE or with VBScript-function in a fashion not unlike 2 B).

Re: Help with reading Excel #Ref

Anonymous — Sat, 04 Nov 2017 05:22:35 GMT

Thx man, i realise its too much of a hassle to do a workaround this way. IT guys have to install 7zip in the production machine etc. might be checking for #ref in the spreadsheet through vba.