Mixing
Collection Data manipulation Blue Prism: Calculation stage - and split into two collections based on the...
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
I am currently working on a Collection that has the data of a reconciliation Excel report (accounting). Since there are apprx 1500 rows, looping through the collection takes quite some time.
I'd rather do a code stage that does the calculation, mainly looking to see if there is a difference between two of the rows. All The rows with a difference between the two lines should be exported to a new collection.
Has anybody done anything similar either in C# or VB.script?
Collection
c# vb.net blueprism rpa
edited Jan 4 at 17:45
Marek Stejskal
2,10711325
asked Jan 3 at 13:22
A.AA.A
112
|
show 6 more comments
1
I am currently working on a Collection that has the data of a reconciliation Excel report (accounting). Since there are apprx 1500 rows, looping through the collection takes quite some time.
I'd rather do a code stage that does the calculation, mainly looking to see if there is a difference between two of the rows. All The rows with a difference between the two lines should be exported to a new collection.
Has anybody done anything similar either in C# or VB.script?
Collection
c# vb.net blueprism rpa
edited Jan 4 at 17:45
Marek Stejskal
2,10711325
asked Jan 3 at 13:22
A.AA.A
112
1,500 rows isn't really a lot of data from a Blue Prism perspective - are you sure your current Blue Prism process is optimized fully? Are you running in the fastest Debug mode or from the Control Room?
– esqew
Jan 3 at 14:28
Well, that is the first loop in the process, there are quite a few more as several reports need to be reconciled. Was just wondering if anybody had done similar stages using code to gather the differences rather than looping
– A.A
Jan 3 at 14:57
1
I'm not quite sure what do you mean by "difference between two lines". Two lines anywhere in the data? Two adjacent lines? Can you post sample data to show what exactly you want to achieve?
– Marek Stejskal
Jan 3 at 17:51
Hi Marek. there are two lines, one for the bank account and another for what is booked in the books. The current calculation stage has the different between the two rows.
– A.A
Jan 3 at 18:52
How do you know that the two lines go together? Are they next to each other or do they have a common ID column? Is it always two matching rows or can there be just one or more than two? Again, a picture would be helpful :)
– Marek Stejskal
Jan 4 at 10:00
|
show 6 more comments
1
1
1
I am currently working on a Collection that has the data of a reconciliation Excel report (accounting). Since there are apprx 1500 rows, looping through the collection takes quite some time.
I'd rather do a code stage that does the calculation, mainly looking to see if there is a difference between two of the rows. All The rows with a difference between the two lines should be exported to a new collection.
Has anybody done anything similar either in C# or VB.script?
Collection
c# vb.net blueprism rpa
edited Jan 4 at 17:45
Marek Stejskal
2,10711325
asked Jan 3 at 13:22
A.AA.A
112
I am currently working on a Collection that has the data of a reconciliation Excel report (accounting). Since there are apprx 1500 rows, looping through the collection takes quite some time.
I'd rather do a code stage that does the calculation, mainly looking to see if there is a difference between two of the rows. All The rows with a difference between the two lines should be exported to a new collection.
Has anybody done anything similar either in C# or VB.script?
Collection
c# vb.net blueprism rpa
c# vb.net blueprism rpa
edited Jan 4 at 17:45
Marek Stejskal
2,10711325
asked Jan 3 at 13:22
A.AA.A
112
edited Jan 4 at 17:45
Marek Stejskal
2,10711325
asked Jan 3 at 13:22
A.AA.A
112
edited Jan 4 at 17:45
Marek Stejskal
2,10711325
edited Jan 4 at 17:45
Marek Stejskal
2,10711325
edited Jan 4 at 17:45
Marek Stejskal
2,10711325
2,10711325
asked Jan 3 at 13:22
A.AA.A
112
asked Jan 3 at 13:22
A.AA.A
112
asked Jan 3 at 13:22
A.AA.A
112
112
1,500 rows isn't really a lot of data from a Blue Prism perspective - are you sure your current Blue Prism process is optimized fully? Are you running in the fastest Debug mode or from the Control Room?
– esqew
Jan 3 at 14:28
Well, that is the first loop in the process, there are quite a few more as several reports need to be reconciled. Was just wondering if anybody had done similar stages using code to gather the differences rather than looping
– A.A
Jan 3 at 14:57
1
I'm not quite sure what do you mean by "difference between two lines". Two lines anywhere in the data? Two adjacent lines? Can you post sample data to show what exactly you want to achieve?
– Marek Stejskal
Jan 3 at 17:51
Hi Marek. there are two lines, one for the bank account and another for what is booked in the books. The current calculation stage has the different between the two rows.
– A.A
Jan 3 at 18:52
How do you know that the two lines go together? Are they next to each other or do they have a common ID column? Is it always two matching rows or can there be just one or more than two? Again, a picture would be helpful :)
– Marek Stejskal
Jan 4 at 10:00
|
show 6 more comments
1,500 rows isn't really a lot of data from a Blue Prism perspective - are you sure your current Blue Prism process is optimized fully? Are you running in the fastest Debug mode or from the Control Room?
– esqew
Jan 3 at 14:28
Well, that is the first loop in the process, there are quite a few more as several reports need to be reconciled. Was just wondering if anybody had done similar stages using code to gather the differences rather than looping
– A.A
Jan 3 at 14:57
1
I'm not quite sure what do you mean by "difference between two lines". Two lines anywhere in the data? Two adjacent lines? Can you post sample data to show what exactly you want to achieve?
– Marek Stejskal
Jan 3 at 17:51
Hi Marek. there are two lines, one for the bank account and another for what is booked in the books. The current calculation stage has the different between the two rows.
– A.A
Jan 3 at 18:52
How do you know that the two lines go together? Are they next to each other or do they have a common ID column? Is it always two matching rows or can there be just one or more than two? Again, a picture would be helpful :)
– Marek Stejskal
Jan 4 at 10:00
1,500 rows isn't really a lot of data from a Blue Prism perspective - are you sure your current Blue Prism process is optimized fully? Are you running in the fastest Debug mode or from the Control Room?
– esqew
Jan 3 at 14:28
1,500 rows isn't really a lot of data from a Blue Prism perspective - are you sure your current Blue Prism process is optimized fully? Are you running in the fastest Debug mode or from the Control Room?
– esqew
Jan 3 at 14:28
Well, that is the first loop in the process, there are quite a few more as several reports need to be reconciled. Was just wondering if anybody had done similar stages using code to gather the differences rather than looping
– A.A
Jan 3 at 14:57
Well, that is the first loop in the process, there are quite a few more as several reports need to be reconciled. Was just wondering if anybody had done similar stages using code to gather the differences rather than looping
– A.A
Jan 3 at 14:57
1
1
I'm not quite sure what do you mean by "difference between two lines". Two lines anywhere in the data? Two adjacent lines? Can you post sample data to show what exactly you want to achieve?
– Marek Stejskal
Jan 3 at 17:51
I'm not quite sure what do you mean by "difference between two lines". Two lines anywhere in the data? Two adjacent lines? Can you post sample data to show what exactly you want to achieve?
– Marek Stejskal
Jan 3 at 17:51
Hi Marek. there are two lines, one for the bank account and another for what is booked in the books. The current calculation stage has the different between the two rows.
– A.A
Jan 3 at 18:52
Hi Marek. there are two lines, one for the bank account and another for what is booked in the books. The current calculation stage has the different between the two rows.
– A.A
Jan 3 at 18:52
How do you know that the two lines go together? Are they next to each other or do they have a common ID column? Is it always two matching rows or can there be just one or more than two? Again, a picture would be helpful :)
– Marek Stejskal
Jan 4 at 10:00
How do you know that the two lines go together? Are they next to each other or do they have a common ID column? Is it always two matching rows or can there be just one or more than two? Again, a picture would be helpful :)
– Marek Stejskal
Jan 4 at 10:00
|
show 6 more comments
1 Answer
1
active
oldest
votes
1
Use the object Utility - Collection Manipulation and the action Filter Collection.
As an input collection use the collection you retrieved from the Excel file and as a filter express use this:
"[ub_hovedbok] <> [ub_bank]"
This will output a new collection with rows only with values different in those two columns.
Alternatively, you could do this with direct OLEDB connection to the Excel file.
answered Jan 4 at 12:18
Marek StejskalMarek Stejskal
2,10711325
Note that OLEDB doesn't work anymore in MS Office 2016/365. The user would have to remove MS Access of that version and install back MS Access 2013, but I don't see that as a viable option because updates might be pushed from an uncontrollable source (and just adds a lot of more hassle to be honest).
– Jerry
Jan 4 at 22:49
Thanks for the info, I didn't know that. Why is that? Are the drivers missing in the standard Office installation?
– Marek Stejskal
Jan 5 at 21:06
I'm not entirely sure myself, seems like MS did not include a library and might be using something different. I haven't been able to find a workaround while still using OLEDB even on the BluePrism forums, so I've been using SQL since I needed a solution fast (several live bots were affected by it). Actually, the code stage I've been using usesMicrosoft.ACE.OLEDB.12.0for the provider parameter of the connection. Maybe I don't know where to repoint it, but changing 12 to a new version didn't work.
– Jerry
Jan 5 at 21:20
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023163%2fcollection-data-manipulation-blue-prism-calculation-stage-and-split-into-two%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Use the object Utility - Collection Manipulation and the action Filter Collection.
As an input collection use the collection you retrieved from the Excel file and as a filter express use this:
"[ub_hovedbok] <> [ub_bank]"
This will output a new collection with rows only with values different in those two columns.
Alternatively, you could do this with direct OLEDB connection to the Excel file.
answered Jan 4 at 12:18
Marek StejskalMarek Stejskal
2,10711325
Note that OLEDB doesn't work anymore in MS Office 2016/365. The user would have to remove MS Access of that version and install back MS Access 2013, but I don't see that as a viable option because updates might be pushed from an uncontrollable source (and just adds a lot of more hassle to be honest).
– Jerry
Jan 4 at 22:49
Thanks for the info, I didn't know that. Why is that? Are the drivers missing in the standard Office installation?
– Marek Stejskal
Jan 5 at 21:06
I'm not entirely sure myself, seems like MS did not include a library and might be using something different. I haven't been able to find a workaround while still using OLEDB even on the BluePrism forums, so I've been using SQL since I needed a solution fast (several live bots were affected by it). Actually, the code stage I've been using usesMicrosoft.ACE.OLEDB.12.0for the provider parameter of the connection. Maybe I don't know where to repoint it, but changing 12 to a new version didn't work.
– Jerry
Jan 5 at 21:20
add a comment |
1
Use the object Utility - Collection Manipulation and the action Filter Collection.
As an input collection use the collection you retrieved from the Excel file and as a filter express use this:
"[ub_hovedbok] <> [ub_bank]"
This will output a new collection with rows only with values different in those two columns.
Alternatively, you could do this with direct OLEDB connection to the Excel file.
answered Jan 4 at 12:18
Marek StejskalMarek Stejskal
2,10711325
Note that OLEDB doesn't work anymore in MS Office 2016/365. The user would have to remove MS Access of that version and install back MS Access 2013, but I don't see that as a viable option because updates might be pushed from an uncontrollable source (and just adds a lot of more hassle to be honest).
– Jerry
Jan 4 at 22:49
Thanks for the info, I didn't know that. Why is that? Are the drivers missing in the standard Office installation?
– Marek Stejskal
Jan 5 at 21:06
I'm not entirely sure myself, seems like MS did not include a library and might be using something different. I haven't been able to find a workaround while still using OLEDB even on the BluePrism forums, so I've been using SQL since I needed a solution fast (several live bots were affected by it). Actually, the code stage I've been using usesMicrosoft.ACE.OLEDB.12.0for the provider parameter of the connection. Maybe I don't know where to repoint it, but changing 12 to a new version didn't work.
– Jerry
Jan 5 at 21:20
add a comment |
1
1
1
Use the object Utility - Collection Manipulation and the action Filter Collection.
As an input collection use the collection you retrieved from the Excel file and as a filter express use this:
"[ub_hovedbok] <> [ub_bank]"
This will output a new collection with rows only with values different in those two columns.
Alternatively, you could do this with direct OLEDB connection to the Excel file.
answered Jan 4 at 12:18
Marek StejskalMarek Stejskal
2,10711325
Use the object Utility - Collection Manipulation and the action Filter Collection.
As an input collection use the collection you retrieved from the Excel file and as a filter express use this:
"[ub_hovedbok] <> [ub_bank]"
This will output a new collection with rows only with values different in those two columns.
Alternatively, you could do this with direct OLEDB connection to the Excel file.
answered Jan 4 at 12:18
Marek StejskalMarek Stejskal
2,10711325
answered Jan 4 at 12:18
Marek StejskalMarek Stejskal
2,10711325
answered Jan 4 at 12:18
Marek StejskalMarek Stejskal
2,10711325
answered Jan 4 at 12:18
Marek StejskalMarek Stejskal
2,10711325
2,10711325
Note that OLEDB doesn't work anymore in MS Office 2016/365. The user would have to remove MS Access of that version and install back MS Access 2013, but I don't see that as a viable option because updates might be pushed from an uncontrollable source (and just adds a lot of more hassle to be honest).
– Jerry
Jan 4 at 22:49
Thanks for the info, I didn't know that. Why is that? Are the drivers missing in the standard Office installation?
– Marek Stejskal
Jan 5 at 21:06
I'm not entirely sure myself, seems like MS did not include a library and might be using something different. I haven't been able to find a workaround while still using OLEDB even on the BluePrism forums, so I've been using SQL since I needed a solution fast (several live bots were affected by it). Actually, the code stage I've been using usesMicrosoft.ACE.OLEDB.12.0for the provider parameter of the connection. Maybe I don't know where to repoint it, but changing 12 to a new version didn't work.
– Jerry
Jan 5 at 21:20
add a comment |
Note that OLEDB doesn't work anymore in MS Office 2016/365. The user would have to remove MS Access of that version and install back MS Access 2013, but I don't see that as a viable option because updates might be pushed from an uncontrollable source (and just adds a lot of more hassle to be honest).
– Jerry
Jan 4 at 22:49
Thanks for the info, I didn't know that. Why is that? Are the drivers missing in the standard Office installation?
– Marek Stejskal
Jan 5 at 21:06
I'm not entirely sure myself, seems like MS did not include a library and might be using something different. I haven't been able to find a workaround while still using OLEDB even on the BluePrism forums, so I've been using SQL since I needed a solution fast (several live bots were affected by it). Actually, the code stage I've been using usesMicrosoft.ACE.OLEDB.12.0for the provider parameter of the connection. Maybe I don't know where to repoint it, but changing 12 to a new version didn't work.
– Jerry
Jan 5 at 21:20
Note that OLEDB doesn't work anymore in MS Office 2016/365. The user would have to remove MS Access of that version and install back MS Access 2013, but I don't see that as a viable option because updates might be pushed from an uncontrollable source (and just adds a lot of more hassle to be honest).
– Jerry
Jan 4 at 22:49
Note that OLEDB doesn't work anymore in MS Office 2016/365. The user would have to remove MS Access of that version and install back MS Access 2013, but I don't see that as a viable option because updates might be pushed from an uncontrollable source (and just adds a lot of more hassle to be honest).
– Jerry
Jan 4 at 22:49
Thanks for the info, I didn't know that. Why is that? Are the drivers missing in the standard Office installation?
– Marek Stejskal
Jan 5 at 21:06
Thanks for the info, I didn't know that. Why is that? Are the drivers missing in the standard Office installation?
– Marek Stejskal
Jan 5 at 21:06
I'm not entirely sure myself, seems like MS did not include a library and might be using something different. I haven't been able to find a workaround while still using OLEDB even on the BluePrism forums, so I've been using SQL since I needed a solution fast (several live bots were affected by it). Actually, the code stage I've been using uses
Microsoft.ACE.OLEDB.12.0 for the provider parameter of the connection. Maybe I don't know where to repoint it, but changing 12 to a new version didn't work.– Jerry
Jan 5 at 21:20
I'm not entirely sure myself, seems like MS did not include a library and might be using something different. I haven't been able to find a workaround while still using OLEDB even on the BluePrism forums, so I've been using SQL since I needed a solution fast (several live bots were affected by it). Actually, the code stage I've been using uses
Microsoft.ACE.OLEDB.12.0 for the provider parameter of the connection. Maybe I don't know where to repoint it, but changing 12 to a new version didn't work.– Jerry
Jan 5 at 21:20
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023163%2fcollection-data-manipulation-blue-prism-calculation-stage-and-split-into-two%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1,500 rows isn't really a lot of data from a Blue Prism perspective - are you sure your current Blue Prism process is optimized fully? Are you running in the fastest Debug mode or from the Control Room?
– esqew
Jan 3 at 14:28
Well, that is the first loop in the process, there are quite a few more as several reports need to be reconciled. Was just wondering if anybody had done similar stages using code to gather the differences rather than looping
– A.A
Jan 3 at 14:57
1
I'm not quite sure what do you mean by "difference between two lines". Two lines anywhere in the data? Two adjacent lines? Can you post sample data to show what exactly you want to achieve?
– Marek Stejskal
Jan 3 at 17:51
Hi Marek. there are two lines, one for the bank account and another for what is booked in the books. The current calculation stage has the different between the two rows.
– A.A
Jan 3 at 18:52
How do you know that the two lines go together? Are they next to each other or do they have a common ID column? Is it always two matching rows or can there be just one or more than two? Again, a picture would be helpful :)
– Marek Stejskal
Jan 4 at 10:00