Concatenate or join excel files

[capriconuser]

I have 3 excel files with same column name but different data .. 

so i need to concatentate those tables or join those tables ?

when i try first excel files i do this 

LOAD
    ID,
    HostID,
    HostName,
    HostSince,
    HostResponseTime
FROM [lib://b-attachments/data.xlsx]
(ooxml, embedded labels, table is Jandata);

then on script on another section i do this 

    Left Join(Jandata)
         Load HostID,
         Count(ID) as NoList
         Resident Jandata
         Group by HostID;

so now i have 2 more excel files .. how i do that on script ?

[marcus_sommer]

I would probably concatenate the three tables first - additionally adding a source-information manually or maybe with something like filebasename() - and then joining the count to it (if the source is relevant for the count you need to add this field to the join-load, too.

- Marcus

[Vegar]

Try something like this below. First join then populate your final table. 

FOR each _file in list 'data.xlsx', 'data2.xlsx', 'data3.xlsx'

   TMP:
   LOAD
     ID,
     HostID,
     HostName,
     HostSince,
     HostResponseTime,
     'AutoconcatenateStopper' as DummyField
   FROM [lib://b-attachments/$(_file)]
   (ooxml, embedded labels, table is Jandata);

   Left Join(TMP)
   Load HostID,
      Count(ID) as NoList
      Resident TMP
   Group by HostID;

   //Autoconcatenate
   Final:
   LOAD
       ID,
      HostID,
      HostName,
      HostSince,
      HostResponseTime,
      NoList
   RESIDENT TMP;

   DROP TABLE TMP

NEXT _file

[capriconuser]

i did not understand this line

FROM [lib://b-attachments/$(_file)]

why you write this  $(_file) instead of excel file name ?

@Vegar 

[Vegar]

I was looping through your three files that I named 'data.xlsx', 'data2.xlsx' and 'data3.xlsx'. For each iteration the content of the variable _file will change according to the list.

FOR each _file in list 'data.xlsx', 'data2.xlsx', 'data3.xlsx'

   TMP:
   LOAD
      ... 
   FROM [lib://b-attachments/$(_file)]
   (ooxml, embedded labels, table is Jandata);

NEXT _file