Join all Excel files in folder

[Anonymous]

Hello.

I have a following problem.

Suppose I have two data sources, which are folders with Excel files. There are multiple spreadsheets in both folders, in which there are different columns, which share common key in a following manner:

Folder1:

Dataset_one_1.xlsx

key | value

1       7

2       14

3       9

4       2

5       6

Folder2:

Dataset_two_1.xlsx

key| text

1      one

2      two

Dataset_two_2.xlsx

key| text

3      three

4      four

5      five

I want to join these two data sources, and attempt to achieve so looks like this:

first_source:
LOAD
    key,
    value
FROM [lib://my_source_one/Dataset_one*.xlsx]
(ooxml, embedded labels, table is Sheet1);






Left Join (first_source)
second_source:
LOAD
    key,
    "text"
FROM [lib://my_source_two/dataset_two*.xlsx]
(ooxml, embedded labels, table is Sheet1);

So far so good.

When I try to investigate loaded data and create a table to validate load procedure, I see following picture:

For some reason, rows from second file are not present. How do I fix that? This is a test example, the structure of an actual dataset is far more complicated and may contain tens of Excel files in both directories.

Thanks in advance!

[andrey_krylov]

Hi Anton. This script loads several files into second_source and joins them separately, as they are loaded. You have to load them first and then join.

[juraj_misina · Accepted solution]

Just to expand on Andrey's suggestion:

first_source:  
LOAD  
    key,  
    value  
FROM [lib://my_source_one/Dataset_one*.xlsx]  
(ooxml, embedded labels, table is Sheet1);  

noconcatenate
second_source:  
LOAD  
    key,  
    "text"  
FROM [lib://my_source_two/dataset_two*.xlsx]  
(ooxml, embedded labels, table is Sheet1);  

Left Join (first_source)  
LOAD * resident second_source;
Drop Table second_source;

[Anonymous]

Thank you very much for your replay!

However, if I apply your solution, two tables become joined in a strange way:

If I remove noconcatenate keyword, everything begin to work as expected.

[andrey_krylov]

Hi Anton, that's all right, noconcotenate shouldn't be used and Juraj add it mechanically. As in your case before separate tables are created which leads to this result.