Read And Concate Multiple Excel Sheets

mario-sarkis — Fri, 01 Feb 2019 09:06:29 GMT

Dear ALL,

I have multiple Excel files looks like this :

for Example:

first Excel file : KANARI_20190129

Second Excel File : KANARI_20190130

Third file : Kanari_20190201

......

How can I read these files and concatenate?

any loop for this case?

Please Help

Thank you in advance.

Re: Read And Concate Multiple Excel Sheets

Marcos_rv — Tue, 05 Feb 2019 15:04:20 GMT

Try this :

FOR EACH vFile IN FILELIST('$(vPathSource)\*.XLS')

LET vNameFileAUX = UPPER(SubField('$(vFile)','\',-1));

LET vNameFile = SubField('$(vFile)','\',-1);

LET vFech     =    SubField(SubField('$(vNameFile)','_',-1),'.',1);


If SubStringCount('$(vNameFile)', 'KANARI') >= 1 THEN

           TABLE_AUX:
           LOAD
           '$(vNameSource)'      AS SOURCE,
           '$(vNameEntity)'      AS ENTITY,
           '$(vNameFile)'        AS FILE,
           '$(vPathSource)'      AS PATH,
            '$(vFech)'             AS FECH
AUTOGENERATE 1 ;

ENDIF

LET vCantTabFile = NoOfRows(‘TABLE_AUX’);

FOR i=0 TO $(vCantTabFile)-1

LET vEntity = Peek('ENTITY',$(i), ‘TABLE_AUX’);
LET vFile = Peek('FILE',$(i), ‘TABLE_AUX’);
LET vSource = Peek('SOURCE',$(i), ‘TABLE_AUX’);
LET vPath = Peek('PATH',$(i), ‘TABLE_AUX’);

IF $(i) = 0 then

Table:

LOAD *

FROM
$(vPath)\$(vFile)

Else

Concatenate(Table)

LOAD *

FROM
$(vPath)\$(vFile)

Endif;

NEXT;

Drop table TABLE_AUX;

topic Read And Concate Multiple Excel Sheets in QlikView

Read And Concate Multiple Excel Sheets

Re: Read And Concate Multiple Excel Sheets