topic Re: Concatenate or join excel files in App Development

Concatenate or join excel files

capriconuser — Thu, 19 Mar 2020 08:51:07 GMT

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 ?

Re: Concatenate or join excel files

marcus_sommer — Thu, 19 Mar 2020 09:41:05 GMT

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

Re: Concatenate or join excel files

Vegar — Thu, 19 Mar 2020 09:54:25 GMT

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

Re: Concatenate or join excel files

capriconuser — Thu, 19 Mar 2020 10:16:05 GMT

i did not understand this line

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

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

@Vegar

Re: Concatenate or join excel files

Vegar — Thu, 19 Mar 2020 11:55:30 GMT

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