1 BEST ANSWER
Helpful Answers
@cs,
Below is an example of the Linq query I put together to look for the difference between the two files based on what I can see in your screenshots as well as the output. Notice I'm having to specifically compare columns by name because, as I mentioned, your screenshot seems to show that the columns have various differences between their column names.
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------
Below is an example of the Linq query I put together to look for the difference between the two files based on what I can see in your screenshots as well as the output. Notice I'm having to specifically compare columns by name because, as I mentioned, your screenshot seems to show that the columns have various differences between their column names.
var differences =
(from drPMD in PMD.AsEnumerable()
join drPFS in PFS.AsEnumerable() on drPMD.Field<string>(@"G/L Acct") equals drPFS.Field<string>(@"G/L acct") into match
from drPFS in match.DefaultIfEmpty()
where (drPFS == null) || (drPMD.Field<string>(@"Curr") != drPFS.Field<string>(@"Acct cu")) ||
(drPMD.Field<string>(@"FStGro") != drPFS.Field<string>(@"Fld stat")) ||
(drPMD.Field<string>(@"Sort") != drPFS.Field<string>(@"Sort"))
select drPMD).ToList();
Differences = differences.CopyToDataTable();
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------
So you want to flag rows that contain the same column values but are not in the same overall order between the two spreadsheets too? In your example above you call out the 3rd row of Excel1 where column B = 24 but the 3rd row of Excel2 has column B=26. The only issue here is that the rows from #3 onward are in a different order between the two collections. Would those then be flagged in your differences collection?
The following code will perform a comparison of two DataTables and return a DataTable that contains the actual differences between them. However, this is looking for actual differences in DataRow objects. If the rows are simply in a different order, they don't count as a difference. That's just a sorting issue. The only way a DataRow is actually different is if it contains completely different field values.
Here's the actual code:
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------
The following code will perform a comparison of two DataTables and return a DataTable that contains the actual differences between them. However, this is looking for actual differences in DataRow objects. If the rows are simply in a different order, they don't count as a difference. That's just a sorting issue. The only way a DataRow is actually different is if it contains completely different field values.
Here's the actual code:
var differences = excel2.AsEnumerable().Except(excel1.AsEnumerable(), DataRowComparer.Default);
Differences = differences.Any() ? differences.CopyToDataTable() : new DataTable();
FYI - Your username on the Community probably shouldn't contain an "@" sign as it screws up the mention feature a bit.
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------
Hello @cs,
No need to apologize. I'm simply a bit thick-headed sometimes. 🙂
So it looks like you're doing some General Ledger comparison. From your example I'm making the following assumptions:
I've set up another example below. I'm using smaller Collection definitions than what your actual files reflect, but the solution should still apply.
Collection1 - Master File
Collection2
Code Stage
The above code basically performs a SQL Join on the two collections, using G/L Acct as the primary key, and returns any rows where:
If you only wanted to perform the comparison on certain columns you could revise the above WHERE clause to look at specific columns:
Hope this helps.
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------
No need to apologize. I'm simply a bit thick-headed sometimes. 🙂
So it looks like you're doing some General Ledger comparison. From your example I'm making the following assumptions:
- Column CoCode will always be the same, so we can effectively ignore it.
- Column G/L Acct becomes our primary key, and all comparisons will be based off it.
- The column definitions (basically the schema) between the two files (i.e. Collections) will be the same.
I've set up another example below. I'm using smaller Collection definitions than what your actual files reflect, but the solution should still apply.
Collection1 - Master File
Collection2
Code Stage
var differences =
(from drColl2 in Collection2.AsEnumerable()
join drColl1 in Collection1.AsEnumerable() on drColl2.Field<string>(@"G/L Acct") equals drColl1.Field<string>(@"G/L Acct") into match
from drColl1 in match.DefaultIfEmpty()
where drColl1 == null || !DataRowComparer.Default.Equals(drColl2, drColl1)
select drColl2).ToList();
Differences = differences.CopyToDataTable();The above code basically performs a SQL Join on the two collections, using G/L Acct as the primary key, and returns any rows where:
- the values of any of the other columns, in Collection2, do not match between rows, of Collection1, that have a matching G/L Acct.
- any rows, from Collection2, that do not have a matching G/L Acct in Collection1.
If you only wanted to perform the comparison on certain columns you could revise the above WHERE clause to look at specific columns:
var differences =
(from drColl2 in Collection2.AsEnumerable()
join drColl1 in Collection1.AsEnumerable() on drColl2.Field<string>(@"G/L Acct") equals drColl1.Field<string>(@"G/L Acct") into match
from drColl1 in match.DefaultIfEmpty()
where drColl1 == null || drColl2.Field<string>(@"Acct Curr") != drColl1.Field<string>(@"Acct Curr")
select drColl2).ToList();
Differences = differences.CopyToDataTable();Hope this helps.
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------
Original Message:
Sent: 01-31-2022 18:03
From: Eric Wilson
Subject: Issue related Excel Data Comparison
Hello @cs,
No need to apologize. I'm simply a bit thick-headed sometimes. 🙂
So it looks like you're doing some General Ledger comparison. From your example I'm making the following assumptions:
I've set up another example below. I'm using smaller Collection definitions than what your actual files reflect, but the solution should still apply.
Collection1 - Master File
Collection2
Code Stage
The above code basically performs a SQL Join on the two collections, using G/L Acct as the primary key, and returns any rows where:
If you only wanted to perform the comparison on certain columns you could revise the above WHERE clause to look at specific columns:
Hope this helps.
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
Sent: 01-31-2022 18:03
From: Eric Wilson
Subject: Issue related Excel Data Comparison
Hello @cs,
No need to apologize. I'm simply a bit thick-headed sometimes. 🙂
So it looks like you're doing some General Ledger comparison. From your example I'm making the following assumptions:
- Column CoCode will always be the same, so we can effectively ignore it.
- Column G/L Acct becomes our primary key, and all comparisons will be based off it.
- The column definitions (basically the schema) between the two files (i.e. Collections) will be the same.
I've set up another example below. I'm using smaller Collection definitions than what your actual files reflect, but the solution should still apply.
Collection1 - Master File
Collection2
Code Stage
var differences = (from drColl2 in Collection2.AsEnumerable() join drColl1 in Collection1.AsEnumerable() on drColl2.Field<string>(@"G/L Acct") equals drColl1.Field<string>(@"G/L Acct") into match from drColl1 in match.DefaultIfEmpty() where drColl1 == null || !DataRowComparer.Default.Equals(drColl2, drColl1) select drColl2).ToList();Differences = differences.CopyToDataTable();The above code basically performs a SQL Join on the two collections, using G/L Acct as the primary key, and returns any rows where:
- the values of any of the other columns, in Collection2, do not match between rows, of Collection1, that have a matching G/L Acct.
- any rows, from Collection2, that do not have a matching G/L Acct in Collection1.
If you only wanted to perform the comparison on certain columns you could revise the above WHERE clause to look at specific columns:
var differences = (from drColl2 in Collection2.AsEnumerable() join drColl1 in Collection1.AsEnumerable() on drColl2.Field<string>(@"G/L Acct") equals drColl1.Field<string>(@"G/L Acct") into match from drColl1 in match.DefaultIfEmpty() where drColl1 == null || drColl2.Field<string>(@"Acct Curr") != drColl1.Field<string>(@"Acct Curr") select drColl2).ToList();Differences = differences.CopyToDataTable();Hope this helps.
Cheers,
------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
