Selecting a specified pair of excel files and merging them

Hi everyone!

Could you please help me with the following issue:

In one folder I have a varying number of Excel files of two types (pairs). Let’s say type A and type B (the file name include differentiation) - in type A there are clients’ data and in type B there are their orders’ details. I would like to combine data from each „A” file and each „B” file and create a new Excel file with the output.

I’ll be very grateful for your tips!

1 Like

That would be hard to tell without knowing what is in each excel (columns) and what you need in the 3rd excel, but in general, you will read both excel as Data Tables and use Join Data Table to put them together as a new Data Table to then Write Range in a new file.

1 Like

@bcorrea Thanks for your quick reply!
It’s part of my university projects so the structure of excel is not defined.
For the time being, I mainly want to learn the pattern of “matching” to excel files.
I was going to make a different structure of excels - for instance:
In type A data like name, address, mail etc would be in rows, and this would be a header of final excel,
In type B data like product name, product id, price, quantity etc would be in columns
The final/combined excel would look like a sort of invoice.
However, I know that this way I will be more difficult so, for now, we can assume that in the type A excel there will be headers/columns names of datatable and in type B there will be records of datatable.

Till now I did the following steps:

  1. created variable “folderlist” and assigned to it Directory.GetFiles(folderpath,"*.xlsx")
  2. added activity: for each item in folderlist with read and append inside. (like below)

but this way it merges all spreadsheets from folder into one (and what I need is one excel with result for pair of each A and B file)

ok, inside your For Each before the Append Range you will put an IF to know if file is type A or B…

1 Like

@bcorrea
Thanks, I tried the way like below, but it doesn’t work the way I would expect.

Can you please give me any further tips.

Hi
Yes of course the requirement is fine
But where those pairs having same structure of columns in the table like same order of column, same number of columns

Cheers @Kamil_Cholewinski

1 Like

But what happened? any errors? duplicated columns?

Hi! sorry for my late reply but I couldn’t add post due to limited number of post during first 24h for new users.
I did manage to solve the issue.
Thank you very much for all your help!
If anyone needs a xaml with solution pls let me know and I’ll share it here.

Cheers!

1 Like