Read And Concate Multiple Excel Sheets

[mario-sarkis]

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.

[Marcos_rv]

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;