Source file format change

[kmswetha]

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]

Maybe you can do a loop to know how many files you have. Then with this number do a loop again to load each file first to proccesing if the file is new or old, and with a if statement do diferent load.

You can attach an examples of this files and which part of the code you can't change.

[agustinbobba · Accepted solution]

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.

1. For each file I load all the fields with '*' then I use NoofFields  to know how many fields has this file.
2. 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.
3. 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.

[kmswetha]

Hi Agustin,

Its almost working but different structure files are forming different table.

This is not Concatenating. Because of which sync tables are forming. Would need everything in a table.

Used force concatenate at starting of the script but it doesn't concatenate.