Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
kmswetha
Contributor

Source file format change

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.

1 Solution

Accepted Solutions
Highlighted
agustinbobba
Contributor

Re: Source file format change

Hi!

I am trying to recreate your scenary,

I have a directory with severals Files.

a.png

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

b.png

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.

View solution in original post

12 Replies
Highlighted
agustinbobba
Contributor

Re: Source file format change

Hi Swe,

Maybe you can use '*' on the load.

Load *

FROM

[file.xlsx]

(ooxml, embedded labels, table is sheet1);

best regards,

Agustin.

Highlighted
kmswetha
Contributor

Re: Source file format change

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.

Highlighted
Chanty4u
Esteemed Contributor III

Re: Source file format change

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

Highlighted
agustinbobba
Contributor

Re: Source file format change

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

Highlighted
kmswetha
Contributor

Re: Source file format change

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

Highlighted
agustinbobba
Contributor

Re: Source file format change

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

Highlighted
kmswetha
Contributor

Re: Source file format change

Hi,

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

Highlighted
agustinbobba
Contributor

Re: Source file format change

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

Highlighted
kmswetha
Contributor

Re: Source file format change

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.