Multiple files Data Table Lookup

Help Activities
, ,
1

Hello,

I really need some help merging my data table.

I’ve already got 1 data table (dt_1) that looks like the following(6 rows *13 columns)
Name Jan Feb Mar Apr … Dec
B
C
A
E
D

I’ve also got 12 separate excel files in a folder, each file containing the following data in one month
(Example)
File Name: Jan
Name Car Bike Scooter
A 1 2 3
B 0 8 3
C 2 10 2
D 0 2 21
E 1 1 1

My final goal is to use my dt_1 to produce 3 excel files in 3 categories (Cars, Bike, Scooter), which would look like the following format.
File Name : Cars
Name Jan Feb Mar Apr … Dec
B 0 … … … … …
C 2 … … … … …
A 1 … … … … …
E 1 … … … … …
D 0 … … … … …

File Name : Bike
Name Jan Feb Mar Apr … Dec
B 8 … … … … …
C 10 … … … … …
A 2 … … … … …
E 1 … … … … …
D 2 … … … … …

File Name : Scooter
Name Jan Feb Mar Apr … Dec
B 3 … … … … …
C 2 … … … … …
A 3 … … … … …
E 1 … … … … …
D 21 … … … … …

2

dt1

image
image1059×174 2.84 KB

3

12 Separate excel files (example File in Jan)
image

4

My Goal 3 files in 3 different categories
Category1 : Car

image
image1110×175 4.54 KB

5

Hi @Vincent_22
You can do this by using for each loop

1 Like
6

@Vincent_22

You can acheive this using join datatable activity…

Once you join…for example you join car dt1 with jan file…then use a filter datatable and use column tab in the wizard and remove jan, bike and scooter columns

Then rename column car to jan…

Similarly repeat the steps for each file …

For getting each file you can use for each file in folder

Hope this helps

Cheers

1 Like