topic Re: Read and Get Excel Column Name in QlikView

Read and Get Excel Column Name

Anonymous — Wed, 07 Oct 2015 02:43:44 GMT

Hi i have a question,

I have an excel file like this:

I want to read the column name from the excel and store it into variable.

So the result i expect is

vVar1 = WO

vVar2 = RO

vVar3 = AO

vVar3 = W49

vVar15 = W08

anybody has a solution to solve this problem ?

Thanks in advance

Really appreciate your help

Re: Read and Get Excel Column Name

engishfaque — Wed, 07 Oct 2015 04:00:34 GMT

Dear Lejours88,

Here it is,

Script:

--------------------------

Temp_Table:

LOAD WO,

RO,

AO,

W49,

x1,

x2,

x3,

xn,

W08

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

LET vNoOfRows = NoOfRows('Temp_Table');

FOR i = 0 to $(vNoOfRows) - 1

LET vVar1 = Peek('WO', $(i), 'Temp_Table');

LET vVar2 = Peek('RO', $(i), 'Temp_Table');

LET vVar3 = Peek('AO', $(i), 'Temp_Table');

LET vVar4 = Peek('W49', $(i), 'Temp_Table');

LET vVar15 = Peek('W08', $(i), 'Temp_Table');

--------------------------

Please find attached QVW for your clear understanding.

Kind regards,

Ishfaque Ahmed

Re: Read and Get Excel Column Name

Wed, 07 Oct 2015 05:12:47 GMT

ExcelData:

LOAD Field FROM [File.xlsx] (ooxml, embedded labels);

Let vVariable1 = peek('Field',0,''ExcelData'); // Row 1

Let vVariable2 = peek('Field',1,''ExcelData'); // Row 2

For better understanding refer hic‌ solution at Store Excel Values in variables

Re: Read and Get Excel Column Name

Digvijay_Singh — Wed, 07 Oct 2015 05:29:35 GMT

Try this -

Table1:

First 1 LOAD ID,

Name,

[Creation Date],

[Phase ID],

[Phase Name],

[Phase Review ID],

[Phase Review Name],

[Phase Review Decision],

[Planned Phase Review Date],

[Actual Phase Review Date],

[Event Date]

FROM

(ooxml, embedded labels, table is Sheet1);

Let vTotalFields = NoOfFields('Table1');

For i = 1 to vTotalFields

Let vVar$(i) = FieldName($(i) ,'Table1') ;