Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mario-sarkis
Creator II
Creator II

Read And Concate Multiple Excel Sheets

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.

 

 

1 Reply
Marcos_rv
Creator II
Creator II

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
 

NEXT

 

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;