Solved: Re: Source file format change - Qlik Community

kmswetha · ‎2016-04-06

Hi,

Have a query on handling the .xlsx structure formate change.

Currently on daily basis we are reading a excel file. After certain time additional fields are added to excel file.

How to handle this while reading into the Qlikview when we load specifiying the filed names.

agustinbobba · ‎2016-04-06

Hi!

I am trying to recreate your scenary,

I have a directory with severals Files.

All with the same format, only the F05 has different format.

Well.. as I comment above I loop the filelist.

For each file I load all the fields with '*' then I use NoofFields to know how many fields has this file.
With this number of field I can choose which kind of load I need, in this example I have 4 files with 3 fields and 1 file with 5 fields.
I use some logic to concatenate the tables asking if i already load or not.


FOR Each var_FileName in Filelist ('C:\Test\*.xlsx')
  QUALIFY *;
  Aux:
  Load
  *
  From [$(var_FileName)]
  (ooxml, embedded labels, table is Sheet1);
  UNQUALIFY *;

  var_NoOfFields = NoOfFields('Aux');
  _tableName = 'Table';
  SET _TableExist = "not(isnull(TableNumber('$(_tableName)')))";
  LET _concat = if($(_TableExist), 'CONCATENATE ($(_tableName))', ''); 

  drop Table Aux;

  IF $(var_NoOfFields) = 3 THEN
      [$(_tableName)]:
       $(_concat)
       LOAD A, 
            B, 
            C
       FROM
       [$(var_FileName)]
       (ooxml, embedded labels, table is Sheet1);

  ELSEIF  $(var_NoOfFields) = 5 THEN
       [$(_tableName)]:
       $(_concat)
       LOAD A, 
           B, 
           C, 
           D, 
           E
       FROM
       [$(var_FileName)]
       (ooxml, embedded labels, table is Sheet1);
  ENDIF

NEXT var_FileName

I hope this can help you..

Kind regard

Agustin.

View solution in original post

agustinbobba · ‎2016-04-06

Hi Swe,

Maybe you can use '*' on the load.

Load *
FROM
[file.xlsx]
(ooxml, embedded labels, table is sheet1);

best regards,

Agustin.

kmswetha · ‎2016-04-06

Thanks Agustin.

But however there is a existing code which I cannot touch.

Now i have to read this new files along with old files. Just that in new files additional fileds are added.

Say earlier A,B,C fields existis in old files. where as new files have A,B,C,D,E.

But all the files exists in the same folder.

Chanty4u · ‎2016-04-06

u can do concatenate ....by that it will do cath all the data .

agustinbobba · ‎2016-04-06

Well,

You can load the new file separately and concatenate both, trying to maintain the same name of the old files.

Old:

Load

A,

B,

C

From OldFiles.xlxs

Concatenate(Old)

Load

A,

B,

C,

D,

E,

From NewFiles.xlxs

something like this I think

Best regard,

Agustin

kmswetha · ‎2016-04-06

I Augstin, Am looking for logic which says new file and oldfile?

agustinbobba · ‎2016-04-06

You can identify which is new and old with the name of the file? or only you know beacause has more field?

kmswetha · ‎2016-04-06

Hi,

Based on time stamp i can verify new or old. But how to read the file name from the directory?Any idea?

agustinbobba · ‎2016-04-06

The way you can read the file name is with the function filename() in the load, returns the name of the table file currenrly being read.

Load *,

filename( ) as Filename

from excel.xlxs

kmswetha · ‎2016-04-06

This is after reading the excel.xlsx. But am looking for reading a file name from the directory. Say there are 100s of files. Would like to read those file name and get the timestamp out of it.