CODEWITHSUNDEEP
excelqlikview
Sushil
Sushil

Reputation: 8488

How to read column headers from excel files into Qlikview

My requirement is that, I have large number of excel files which I need to reload. But before loading, I need to check if all the column headers in all these files are correct (These are data files and in same format. All should have same column headers).

So, my question is how can I read column headers from excel in qlikview for each file and match with a reference array of column headers?

My Try: As I am very new to Qlikview and new to scripting, so couldn't get far. All I can do now is get all files one by one in my code from the directory where it is stored but not sure how to read column headers from them and compare it.

Thanks for any help

Upvotes: 0

Views: 5270

Answers (1)

i_saw_drones
i_saw_drones

Reputation: 3506

Reading headers

If you just wish to compare a list of column headers (i.e. names) then it is possible to read them from your Excel file. For example, if we have an Excel workbook like the following:

Example of an Excel Workbook

Then we can read the headers in with the following script:

FIRST 1
LOAD 
    A, 
    B, 
    C
FROM
[TestData.xlsx]
(ooxml, no labels, table is Sheet1);

What this does is to read only the first row (FIRST 1) from the workbook. As the no labels option is specified in the file parameters the first row of the workbook is treated just as another line of data.

This then results in the following:

Example of results from script

Comparing headers

Depending on where your reference column headers are stored, one method of comparison would be to create a concatenated "key" and compare it with the headers loaded in the above script.

Below is an example script, where the reference headers are set via a string constant:

SET ReferenceHeaders = 'Column1|Column2|Column3';

DataHeaders:
FIRST 1
LOAD
    A & '|' & B & '|' & C as HeaderKey;
LOAD 
    A, 
    B, 
    C
FROM
[TestData.xlsx]
(ooxml, no labels, table is Sheet1);

LET LoadedHeaders = peek('HeaderKey',0,'DataHeaders');

IF LoadedHeaders = ReferenceHeaders THEN 
    // Load table etc.
ENDIF

The peek statement allows you to obtain a single record from your table, in this case we are just reading the value of the HeaderKey's first row. As this table only contains one row, this will give us the headers we loaded as a string.

We can then compare this to the reference headers and then take appropriate action should they match.

However, this assumes that the headers in your data files is in the same order as your reference headers and as such is the simplest method to implement. If the columns are out of order then the process becomes more difficult.

Upvotes: 4

Related Questions