topic RE: Issue related Excel Data Comparison in Product Forum

Issue related Excel Data Comparison

cs — Wed, 26 Jan 2022 20:07:00 GMT

RE: Issue related Excel Data Comparison

ewilson — Thu, 27 Jan 2022 12:12:00 GMT

Hello @csandms1@gmail.com csandms1@gmail.com,

What you're describing sounds like a type of SQL JOIN. A situation where you have two tables and you want to get a collection of rows from those tables based on some condition that exists, or doesn't, between them.

I came across an article on Microsoft's Developer Network that seems related and includes an example of how to compare two DataTables (that's what a Collection is under the hood) and return a third. Perhaps this solution will cover your needs?

https://social.msdn.microsoft.com/Forums/en-US/502ef961-2644-41d6-84be-464d28fcc24d/compare-two-datatables-and-return-3rd-with-difference?forum=aspwebforms

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Issue related Excel Data Comparison

cs — Thu, 27 Jan 2022 15:28:00 GMT

RE: Issue related Excel Data Comparison

ewilson — Thu, 27 Jan 2022 17:50:00 GMT

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:

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
------------------------------

RE: Issue related Excel Data Comparison

cs — Mon, 31 Jan 2022 12:21:00 GMT

Original Message:
Sent: 01-27-2022 12:12
From: Eric Wilson
Subject: Issue related Excel Data Comparison

Hello @cs csandms1@gmail.com csandms1@gmail.com" data-itemmentionkey="b8bdb7f6-0929-4b19-bb49-8ee8b79feecd" biobubblekey="mentionb81700d3-e5fb-4ff6-b924-c1464624e2cf" href="https://community.blueprism.com/network/profile?UserKey=b81700d3-e5fb-4ff6-b924-c1464624e2cf" data-can-remove="True">@csandms1@gmail.com csandms1@gmail.com,

What you're describing sounds like a type of SQL JOIN. A situation where you have two tables and you want to get a collection of rows from those tables based on some condition that exists, or doesn't, between them.

I came across an article on Microsoft's Developer Network that seems related and includes an example of how to compare two DataTables (that's what a Collection is under the hood) and return a third. Perhaps this solution will cover your needs?

https://social.msdn.microsoft.com/Forums/en-US/502ef961-2644-41d6-84be-464d28fcc24d/compare-two-datatables-and-return-3rd-with-difference?forum=aspwebforms

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

Original Message:
Sent: 01-26-2022 20:06
From: csandms1@gmail.com csandms1@gmail.com
Subject: Issue related Excel Data Comparison

Hello to all 🙂
I have a scenario.....
1, Compare 2 excel files, each file contains 18 columns and approx. 7000 rows.
2, Columns are the same 18 in both files but rows may be different.
3, I need to compare these 2 files and find out the difference then take that difference as an output copy into excel.
Now, created a process and it partially worked in one scenario eg. if the columns and rows are the same then it compares row to row and extracts the difference correctly.
BUT when there is a difference in rows in both files eg. in one file 6000 and in 2nd file 5000 then the result is completely wrong.

A B C D E A B C D E

11 22 33 AB 55 11 22 33 XY 56

11 23 33 44 SS 11 23 33 GG JJ Till here it's fine and result are ok

11 24 33 44 55 11 26 33 44 55 BUT from here onwards results are showing wrong

11 25 33 44 55 11 27 33 44 55

11 26 33 44 55 11 24 33 44 55

11 27 33 44 55 11 25 33 44 55

I have created 2 collections for these 2 excel files and then merged these 2 collections into 1 collection.
And then compare row by row for each column, till first 2 rows the result is fine but afterward it's giving the wrong result.

Is there any way out to compare these files like..... eg. the value (24 )in the 3rd row of the 2nd column (B) in the first file, if it won't find the value (24) in the 3rd row of the 2nd column in 2nd file then it should move directly to 4th row of 2nd file until it gets the same value till the end? AND if it won't find the value in the 2ns file till the end then it should come back to the 4th row of the first file and start the comparison again in the same way and if it found any difference then catch it and save?

I hope some code stage will work BUT honestly, I don't have any idea as of now.

If you supporter/helper need any other information then please feel free to ask me.
Will be highly grateful for any reply !!!
Thank you

------------------------------
csandms1@gmail.com csandms1@gmail.com
------------------------------

RE: Issue related Excel Data Comparison

ewilson — Mon, 31 Jan 2022 18:03:00 GMT

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.

Given these assumptions I still think Linq is going to be your best bet, but it will be a slightly more complex query.

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
------------------------------

RE: Issue related Excel Data Comparison

cs — Tue, 01 Feb 2022 12:03:00 GMT

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:

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.

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

RE: Issue related Excel Data Comparison

ewilson — Tue, 01 Feb 2022 12:13:00 GMT

@CSANDMS1,

Let me clarify a bit. When I said we can ignore the CoCode, what I meant is that the comparison you perform will always be against a Master File and Comparison File that have the same CoCode. I was not implying that the CoCode was not important, or that the solution above couldn't be run in a loop.

You can always add a small bit of logic at the front end to first verify that both files are for the same CoCode, or you can extend the Linq query to include CoCode as part of the join (if there's a chance that one or both of the files could contain multiple different CoCodes).

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Issue related Excel Data Comparison

cs — Thu, 03 Mar 2022 17:15:00 GMT

Original Message:
Sent: 02-01-2022 12:12
From: Eric Wilson
Subject: Issue related Excel Data Comparison

@cs,

Let me clarify a bit. When I said we can ignore the CoCode, what I meant is that the comparison you perform will always be against a Master File and Comparison File that have the same CoCode. I was not implying that the CoCode was not important, or that the solution above couldn't be run in a loop.

You can always add a small bit of logic at the front end to first verify that both files are for the same CoCode, or you can extend the Linq query to include CoCode as part of the join (if there's a chance that one or both of the files could contain multiple different CoCodes).

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

RE: Issue related Excel Data Comparison

ewilson — Thu, 03 Mar 2022 23:29:00 GMT

@CSANDMS1,

Apologies for the delayed response. I was out of the office today. There are two DLL's you'll want to reference:

System.Linq.dll
System.Data.DataSetExtensions.dll

For Namespace Imports, you'll want to include System.Linq.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Issue related Excel Data Comparison

cs — Thu, 10 Mar 2022 12:17:00 GMT

Original Message:
Sent: 03-03-2022 23:28
From: Eric Wilson
Subject: Issue related Excel Data Comparison

@cs,

Apologies for the delayed response. I was out of the office today. There are two DLL's you'll want to reference:

System.Linq.dll
System.Data.DataSetExtensions.dll

For Namespace Imports, you'll want to include System.Linq.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

RE: Issue related Excel Data Comparison

ewilson — Thu, 10 Mar 2022 12:41:00 GMT

@CSANDMS1,

On the differences error, did you define an Output data item names differences too? If so, that's the cause. Basically you have an Output variable with the same name as the internal var differences variable you defined in the code. If you refer back to my example, I used a capital "D" on the output data item. It makes a difference, no pun intended, because of case sensitivity.

For the first error, I'm not sure on that one. Would you post a screenshot of your Code Options page?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Issue related Excel Data Comparison

cs — Fri, 11 Mar 2022 14:18:00 GMT

Original Message:

Sent: 03-10-2022 12:41
From: Eric Wilson
Subject: Issue related Excel Data Comparison

@cs,

On the differences error, did you define an Output data item names differences too? If so, that's the cause. Basically you have an Output variable with the same name as the internal var differences variable you defined in the code. If you refer back to my example, I used a capital "D" on the output data item. It makes a difference, no pun intended, because of case sensitivity.

For the first error, I'm not sure on that one. Would you post a screenshot of your Code Options page?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

RE: Issue related Excel Data Comparison

ewilson — Fri, 11 Mar 2022 17:25:00 GMT

@CSANDMS1,

There's one DLL reference I forgot. You need to add System.Core.dll to your External References.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Issue related Excel Data Comparison

cs — Thu, 24 Mar 2022 14:54:00 GMT

Original Message:
Sent: 03-11-2022 17:24
From: Eric Wilson
Subject: Issue related Excel Data Comparison

@cs,

There's one DLL reference I forgot. You need to add System.Core.dll to your External References.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

RE: Issue related Excel Data Comparison

ewilson — Thu, 24 Mar 2022 16:49:00 GMT

What version of Blue prism are you using? I tried importing your VBO, but BP threw an "unrecognized GUID format" error. Are you using a localized version of the software (different language)?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Issue related Excel Data Comparison

cs — Thu, 24 Mar 2022 17:27:00 GMT

Original Message:
Sent: 03-24-2022 16:49
From: Eric Wilson
Subject: Issue related Excel Data Comparison

What version of Blue prism are you using? I tried importing your VBO, but BP threw an "unrecognized GUID format" error. Are you using a localized version of the software (different language)?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

RE: Issue related Excel Data Comparison

ewilson — Thu, 24 Mar 2022 22:59:00 GMT

@cs,

The first, of the two, compile errors you have there is because you're defining an output variable in the Code1 stage, on the page Community Solution, called differences, but you're also defining an actual C# variable in the source code named differences.

Notice, I've changed the Output parameter name to Differences with a capital 'D'. There are a million different ways you could change this to resolve the name conflict.

I'm thinking once you resolve that name conflict, it may resolve the second error too.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Issue related Excel Data Comparison

cs — Fri, 25 Mar 2022 11:01:00 GMT

Original Message:
Sent: 03-24-2022 22:59
From: Eric Wilson
Subject: Issue related Excel Data Comparison

@cs,

The first, of the two, compile errors you have there is because you're defining an output variable in the Code1 stage, on the page Community Solution, called differences, but you're also defining an actual C# variable in the source code named differences.

Notice, I've changed the Output parameter name to Differences with a capital 'D'. There are a million different ways you could change this to resolve the name conflict.

RE: Issue related Excel Data Comparison

ewilson — Fri, 25 Mar 2022 11:28:00 GMT

@cs,

You're kill'n me. 😂

In your screenshot of the DLL's, you show the language has been set to Visual Basic. The example code I posted for you, is written in C#. The VBO you posted yesterday was also set to C#. So, somewhere along the line the language was changed. You need to change it back to C#, or, if you require VB for some other code stage you may be running, you'll have to convert the example code I gave you to VB.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------