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.
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()
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…
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.
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.
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
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