Merging two excel files based on sheetnames

[stefi]

Hi,
I’m having trouble merging two Excel files into one result file using sheetnames.

Background:
Both Excel files have many differently named sheets, but the amount and the names of the sheets are equal in both files.
Furthermore, the sheets have the same structure: Each sheet has only one column, always with the same header - but the column headers are different in both files: E.g. The column header from Excel file 1 is “Intern” and the column header from Excel file 2 is “Extern”.
Additionally, the contents of the columns (names and amounts) can differ in the two excel files, so they may not have the same row amount.

For merging I would like to use the sheet name as an identifier (like at the Join Data Table activity the column identifier) and would like to add for each sheet the column from Excel file 2 next to the column in Excel file 1.

Do you have any idea how to solve it? I haven’t found any activity that can handle this case (sheet as identifier).

[supermanPunch (Arpan)]

Hi @stefi ,

Could you provide us with Sample Input files and the Expected Output files for it ? You could Provide Screenshots as well.

It would be easier to understand the logic and provide/suggest solutions clearly.

[stefi]

Hi @supermanPunch

Thanks for your response!

Here are example screenshots for the Sample Input files - “Excel 1.xlsx” and “Excel 2.xlsx” side by side:
Sheet “100 TR 01-2022”:

Sheet “400 PF 04-2022”:

Here the example screenshots for the Expected Output file - “Result.xlsx” :
Sheet “100 TR 01-2022”:

Sheet “400 PF 04-2022”:

I hope this clarifies my issue.

[ppr (Peter Preuss)]

we can do it by following:

read range - 2nd WorkSheet - dt2
write range - using 1st Worksheet - dt2 - Range: B1

so it is writing the second sheet aside to the first worksheet data

otherwise we can construct a datatable and write it back to excel

[Rahul_Unnikrishnan]

Hello @stefi ,

Here is there any particular criteria for merging these 2 tables? Like some data matching required?
Else you can read one excel using Read range and using write range add it to the another excel.

Read Excel2 will give Datable , DT2
Use write range activity to Excel1, but make sure to provide the Range properly.

Also you can explore Merge Datatable acitvity.

[stefi]

Hi @ppr , hi @Rahul_Unnikrishnan ,

Thanks for your ideas! I just tried the Read & Write Range way.
Unfortunately, there occured two issues:

1. How can I create a separate “Result.xlsx”? In my solution the Result file is Excel1…
2. In each sheet of the Result file only the range of the Excel2 file’s last sheet (“400 PF 04-2022”) was added into each sheet of Excel1, see screenshots:
   
   
   
   
   
   
   
   
   
   

The particular criteria for merging these 2 tables should be the same named sheet.

This is the xaml file I used:
Main.xaml (11.0 KB)

And these are the Sample Input files:
Excel1_2022_04_28.xlsx (11.0 KB)
Excel2_2022_04_28.xlsx (11.1 KB)

Do you have an idea where the error is?

[ppr (Peter Preuss)]

you have iterated over all sheets from excel 2, but will only work with the last read data
therfore on the second loop only the last worksheet data is added to the B1 range

just have a review on this part bring excel2 sheets together with the corresponding excel1 sheet

[Rahul_Unnikrishnan]

Are the sheetname in excel 1 and 2 the same?? Or is it different??

If it’s same, you can get All the sheet names to a string array and loop though it.
In the loop you have to read from excel2 and write to excel1.

[stefi]

Many thanks for your help @ppr and @Rahul_Unnikrishnan now it works

[Rahul_Unnikrishnan]

@stefi Glad to hear that