Compare two CSV files and write common column values accordingly

Help
,
1

Hi all,
I trying to compare 2 CSV file and have an output in a 3rd CSV with all the relevant information.
However the data after reconciliation only show the ‘Country’ row.
How do i populate all the other rows as well?

Input
image

image

If Country matches ‘SG’

Current Output
image

Expected Output
image

Here are my project files.

Main.xaml (9.1 KB) project.json (634 Bytes)

2

@marct - I’m confused…there are so many SG’s in your table…how can you match?? Are you comparing Countries?

For this kind of problem - Please try something in excel first, and then try build a solution in Uipath.

3

Hi. I am only attempting to match the CSV of image 1 and 2 by matching the column Country.

Image 3 is the output i’m getting but however i want to get image 4 instead as the output.

This is just a dummy data as i’m working on customer details.

4

Hi @marct

I have three suggestions:

  1. The Output DT needs to be defined if you are adding rows to it. i.e., the column names and the order of the addition of column values need to match.

  2. We can keep the if logic simple (easy to comprehend) by using the Index variable which is auto incremented in a For Each Row activity. This allows us to use the
    DT.Rows( <row number> ).Item(" <Column Name> ").ToString

In your case the if condition can be simplified:
row("Country").ToString.Trim = dtBook1.Rows(Index).Item("Country").ToString.Trim

  1. When adding rows to a table, the length of the array should be the same as the number of columns in the Output DT.

Here is the required output you are looking for (Output.csv). You have to change the input and output csv file paths to reflect your required file paths. Currently, it uses dummy csv’s.
I have also added a negative test case to test the workflow.

SolutionComparetwoCSV.zip (2.6 KB)

Alternative (a better solution):
To use the Join Datatable activity to achive the same. It is much more elegant solution. All conditions would be specified within it and the workflow will be easier to read. :slight_smile:

Hope this helps!

5

Hi @jeevith

I followed your steps exactly but however, my output only consist of the header of the rows but there is no data in the columns.

Any idea what is causing the problem?

6

Hi @jeevith

This is the data i’ve replicated to the one i have as close as possible.

CSV (A)
image

CSV (B)
image

CSV (C) OUTPUT

image

Your help is appreciated

7

Hi @marct,

Why is it not working?
It wont work because in the initial question you have the Country Table as first input table and the solution uses that as to be checked table i.e., only check as far as the Country table length. So when the tables are interchanged the logic of a join will be different. Simple solution is to reverse the order of the table and keep all other things constant and it will work.

Both solutions will work provided we input the correct table to get in the For Each Row and values in the Add Data Row activity.

Solution to the new test data
I would advice you to go through both solutions to figure out the changes. I have updated some annotations. Here is the updated workflow with your new data and table order. Update-SolutionComparetwoCSV.zip (2.7 KB)

Two tips for you:

  • Write the test data in csv or attach excel file in forums. Much faster to answer question which also provide the data in a easy to copy format :slight_smile:

  • Work hard on understanding Datatable manipulations, it is a steep learning curve, but once you get hang of it, any automation project becomes fun. Just take any datatable and use all possible Datatable Activities to learn them.

Hope this helps!

8

This video will solve your problem @marct: Adding Specific Columns From One Data Table To Other - YouTube

This is done for excel but the same logic can be used for CSV as well.

Mark it as a solution if it helps.

Cheers,
Parth

9

Hi @jeevith

It is only working when i have 3 columns of data but whenever i add more data, the output file comes up empty.

At the same time, is it possible to compare the 2 CSV files if the number of columns are different?

10

Could you paste the error message or a image where it fails?

  1. Did you update the build data table activity with the extra columns when you added more columns?
  2. Did you add the values for the row for the corresponding column (in the for loop)?

As you add more columns, you will have to update the code for both 1 and 2.

11

HI @jeevith

Output just doesn’t appear if i have more columns, in my case, 50.

I have attached my workflow so that you’ll have a better understanding of the situation.

Main.xaml (9.5 KB) test1.xlsx (10.7 KB) test2.xlsx (9.4 KB)

At the same time, if my ‘test2’ CSV has more columns, this error message will appear. Is it possible to have more columns in ‘test2’ than ‘test1’?

image

Thanks for your prompt reply

12

Hi @Marct,

That is true it will show that error if the tables are ordered different and also depending on the row length. It will work but you could use the alternative solution.

Alternative (a better solution):
I am attaching the better solution, which I mentioned ealier in this thread i.e, Inner Join of Datatables. This is not dependent on the length of rows, you will always get the correct output and the workflow is easy to read and no need to manipulate rows. Annotations are in the file as well.

JoinTables.xaml (7.8 KB)

Cheers!

13

Hi @jeevith

It worked! Thank you for your time, appreciate it :grinning:

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.