topic Re: Source file format change in QlikView

Source file format change

kmswetha — Wed, 06 Apr 2016 13:12:28 GMT

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.

Re: Source file format change

agustinbobba — Wed, 06 Apr 2016 14:04:03 GMT

Hi Swe,

Maybe you can use '*' on the load.

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

best regards,

Agustin.

Re: Source file format change

kmswetha — Wed, 06 Apr 2016 14:16:59 GMT

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.

Re: Source file format change

Chanty4u — Wed, 06 Apr 2016 14:21:29 GMT

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

Re: Source file format change

agustinbobba — Wed, 06 Apr 2016 14:22:11 GMT

Well,

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

Old:

Load

From OldFiles.xlxs

Concatenate(Old)

Load

From NewFiles.xlxs

something like this I think

Best regard,

Agustin

Re: Source file format change

kmswetha — Wed, 06 Apr 2016 14:30:29 GMT

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

Re: Source file format change

agustinbobba — Wed, 06 Apr 2016 14:43:01 GMT

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

Re: Source file format change

kmswetha — Wed, 06 Apr 2016 14:44:09 GMT

Hi,

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

Re: Source file format change

agustinbobba — Wed, 06 Apr 2016 14:47:51 GMT

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

Re: Source file format change

kmswetha — Wed, 06 Apr 2016 14:50:24 GMT

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.

Re: Source file format change

agustinbobba — Wed, 06 Apr 2016 15:00:56 GMT

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.

Re: Source file format change

agustinbobba — Wed, 06 Apr 2016 18:52:20 GMT

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.

Re: Source file format change

kmswetha — Thu, 07 Apr 2016 11:02:43 GMT

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.