File comparison - Generic solution

Help
#1

Hi @aksh1yadav @akhi_s27 @Vikas.Jain @vvaidya @beesheep @badita ,

Is there a way we can compare two csv/xls/xlsx files in a very generic way like from reading a parameter/config file rather doing changes at code level for any two similar file types.

for example:
I have two xls files, File1.xls and File2.xls

File1.xls has columns as below
fname
lname
apple
sname
city
orange
street1
carrot

File2.xls has columns as below
cname
orange
sname
street1
street2
beetroot
orgname
apple

the comparison should be defined in config file so the solution remains intact for any two files.

here the comparison should be between
File1.xls -----> File2.xls
apple(col2) --> apple(col7)
sname(col3) --> sname(col2)
orange(col5) --> orange(col1)
street1(col6)–> street1(col3)

so from this config reading of what to compare in these two files, it should compare/validate.

If there is any match from the row of file1 to the items in file2, this should be captured in matching file else captured in differences file.

so next time if any other two files need to be compared all we may have to change is this config file with columns defined for file1 and file2 to compare.

Hope you got an understanding of the solution that I’m thinking of, can someone share your ideas around this as a solution.

Thanks!

2 Likes
#2

If you can save the Column names some where probably something like this should be easier
If it should match multiple columns to be qualified as similar you should use AND operator in below query else Or

dtSimilar = (from a in dt1.AsEnumerable()
                                  join b in dt2.AsEnumerable()
                                  on a[colname].ToString() equals b[colname].ToString()
                                  into g
                                  where g.Count() > 0
                                  select a).CopyToDataTable()
1 Like
#3

I assume this is an assign activity where you are assigning it to a dtSimilar datatable, isn’t it ?

As my requirement is to build a query with multiple columns read from config file dynamically, what if I build the right side part as you stated above as below string

(from a in datatable1.AsEnumerable() join b in datatable2.AsEnumerable() on a[apple].ToString() EQUALS b[apple].ToString() AND a[sname].ToString() EQUALS b[sname].ToString() OR a[orange].ToString() EQUALS b[orange].ToString() AND a[street1].ToString() EQUALS b[street1].ToString() into g where g.Count() > 0 select a).CopyToDataTable()

how can I make that execute and stored in dtSimilar datatable.?

I see some casting error. Let me know your thoughts…

Thanks!

#4

Hi @Dilli,

Below is my approach.

  • Read the config file.
  • Iterate through each record and use Invoke method with below values.
  • TargetObject - datatable2.columns(colIndex) or datatable2.columns(“colname”)
  • MethodName -SetOrdinal
  • Parameter - Integer - New Index for the column(from mapping)
  • Now both datatable’s columns Indexes are same. You can either iterate through all record and compare rows or use LINQ query.

Regards
Vitan

#5

@Vitan,

I’m glad to follow your approach,

Do you mind sharing the solution workflow as advised meeting my initial requirement as posted.

Thanks and appreciate your time and thoughts.

Compare and push data from Excel File into TextBox PowerPoint
#6

example if you are still looking for solution

comparedt.xaml (16.8 KB)

2 Likes
Comparing Two excel files
Help with how to use contains in two different excel
#7

@vvaidya, thanks for sharing the solution.

If I have to read a config file which has the columns defined as below, how would you construct a query dynamically for all the columns in tha above example shared ?

“colnames”:
[
{
“column”:“Type”,
“condition”:"=",
“operator”:" AND "
},
{
“column”:“Cat”,
“condition”:"=",
“operator”:" AND "
},
{
“column”:“Total Count”,
“condition”:"=",
“operator”:" AND "
},
{
“column”:“Total Amount”,
“condition”:"=",
“operator”:" "
}
]
could you please add your thoughts…

your thoughts could be a much better way than what I shared as a config param definition, but all I want to understand is in the comparison how to do it for multiple columns read from config file, which supports your query solution shared above.

Thanks!

#8

Can you build two data tables in UiPath with above mentioned columns and data and share it.

#9

Hi @vvaidya,

I’m attaching the requested structure as a package, where everything is read from a config file,

You may have to apply your logical query building in the “compare datatables.xaml” file, until there its all good.

Maybe you have a better approach even to there and please feel free to correct them,

sample.zip (10.8 KB)

Just change the source path in the config file and also make sure you generate the differences and matching files in results folder path as mentioned in config file.

If the value of the source datatable column is null it should be replaced with an “” before checking against target datatable column, as the target datatable column doesn’t have null values but “” string.

Hope a sounding solution comes out from this, which could help someone or other.

Thanks!

#10

@vvaidya, did you get a chance to look into ?

#11

Hi Dilli,

How to compare two websites data…?
(ex: we are developing abcd.com website and abcdindia.com website)
but data should be same in both the websites how to do that…?
Thank u

#12

Hi vvaidya,

How to compare two websites data…?
(ex: we are developing abcd.com website and abcdindia.com website)
but data should be same in both the websites how to do that…?
Thank u