Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
e0015367
Contributor III
Contributor III

Help with reading Excel #Ref

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,

TK

1 Solution

Accepted Solutions
petter
Partner
Partner

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

View solution in original post

5 Replies
petter
Partner
Partner

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

2017-11-03 10_04_11-Edit Script [C__Users_Petter_Documents_Test #NA and #REF.qvw_].png

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.

e0015367
Contributor III
Contributor III
Author

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

e0015367
Contributor III
Contributor III
Author

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

petter
Partner
Partner

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

e0015367
Contributor III
Contributor III
Author

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.