Copy contents below specific columns and paste them if that column is found from a file

Help
,
1

Hi all,

I have made an workflow that until this point opens all excel files from a folder, iterates through all the files and copies only the headers in a separate “consolidated file” and pastes them all in one row starting from A1 cell (Example: A1, B1, C1, D1 and so on ).

Important notes:

Each file has mostly the same headers but each file will have at least 2, 3 different headers.
The expected number of files that need to be consolidated are above 50 or more

Now the next step that I want to do is to copy the contents that correspond to each file in that “consolidated file” but only copy the headers that are present in that specific file below their respective column (appended).

I have attached some files to better exemplify what I mean.

So basically my workflow until this point reads each file (" File 1; File 2 “) copies the headers from each file and pastes it in the file(” List of columns from File 1 and File 2 ").

And now I need to copy and paste the data in the file (“List of columns from File 1 and File 2”) as it’s shown in the file (" Expected result ")

Looking forward for a solution.

P.S I am quite new to UiPath so an example workflow for this particular case would be really appreciated.

Thanks a lot !

Mihnea

Expected result.xlsx (8.3 KB) File 1.xlsx (8.8 KB) File 2.xlsx (8.7 KB) List of columns from File 1 and File 2.xlsx (8.2 KB)

2

Your column names in each file are different (Columns A-D and the Columns1-3) while in your requirements you say that some will be the same. If they are different, are you just appending the headers to the end then adding the rows where they correspond to their headers in your target table?

I suspect here that you need to update your two example files so that File2 at least has some of the same column names as you state in your requirements.

3

@ronanpeter

Hi Ronan,

Sorry I find it difficult to explain exactly, my column name are indeed different because I have a workflow that removes the duplicates and sorts them so all the end columns are unique in the end.

The main takeaway that I require is how can I copy the contents from file 1 and file 2 file 3 appended to the to the target table respectively for each individual file.

Find attached another attempt to hopefully explain what I mean.
Excpected output V2.xlsx (9.9 KB)

Sorry for the delay,

Thanks a lot,

Mihnea

4

Ok. So you can merge all three files once you have read them to DataTables.

When you do that you end up with an output like this:

image
image1004×236 7.25 KB

Example workflow.

Main.xaml (5.7 KB)

However, in your example output - I do not understand the logic of mixing the table columns. Do you need to do that?

5

I actually need to merge like 80 files.

I have read the column headers from each of the 80 files I removed the duplicate columns because they will actually repeat, and sorted them alphabetically and I want to copy the relevant data from each file under the relevant unique column

This workflow was just an example for the purpose of exemplifying what I require that could apply for any number of files not just 3.

Thanks,

6

Ok. It sounds as if you have your target output table in place.

To get the data in, you should place all 80 files in a folder. Create a datatable which lists the path of each of these files. Something like this:

image
image1672×588 25.3 KB

You can then cycle through this datatable, grab each file, read it to its own datatable and use the merge datatable activity to place the data into your output datatable. This is possible because you can set the merge datatable activity so that it ignores any columns which are not in your target datatable schema:

image
image949×386 9.92 KB

Only columns which appear in the source AND the target will be merged.

8

Can you provide an example workflow of what you explained ?

9

MergeTables.zip (55.8 KB)

Sure. This does everything but arrange your columns. You can see I have three copies of each file so you’ll see the data repeated, but you will also see there are columns in each file which are not merged into the final output. This is because I have set the merge option to ignore them unless they appear in the source and target tables.

image
image900×527 10.8 KB

10

Hi @ronanpeter

Really appreciate your help and this works but now I ask if there is any way not to manually input the column names because they might change from month to month the files that I need to put in the folder might have different columns

Is there any way that the workflow can be adapted to do that based on a excel file which I have my workflow that extracts all the columns in the folder.

Should I use the read range activity and then build the data table and output it with a build data table activity ?

Thanks again for the support,

Mihnea

11

That’s right, just build the output datatable in excel then read it to the dtOutput variable.

You can also move the columns around if you wish in the design, the workflow will still merge the data.

MergeTables.zip (63.5 KB)

12

Hi Ronan, @ronanpeter

I have tested the workflow provided by you and it does what I asked and I really appreciate your help :grinning:.
However is there a way to make it so that I don’t have to manually add all the columns in the build data table activity (I have over 150 unique columns and this number may change monthly depending on my source files)

image
image564×674 17 KB

I have made a workflow that reads all the files and adds all the unique columns in an Excel file like below

image
image1755×60 4.85 KB

And now I want the workflow provided by you to read all these columns from this Excel file and the rest of the workflow to merge the data without me adding all these columns manually in the build data table every time the source files change.

And I want to also write the name of each individual file in the first column of the “Merged” file alongside the data so I know which data comes from what file.

Thank you so much for the support ! :+1:

Cheers,

Mihnea