topic How to convert entire DataTable column into a string? in Product Forum

How to convert entire DataTable column into a string?

IrinaMihalache — Wed, 03 Aug 2022 21:27:49 GMT

Hello,

I have an Excel file with 2000+ rows and it containing a column "Email Address". I have to concatenate all rows on that column, put a ";" between them and then paste that huge string into a "TO" field and send an email.

I tried looping throw all rows one by one and create that huge string but it takes too much time. Is there any solution for this, without using the Loop?
(TEXTJOIN and CONCAT functions from Excel apparently limit the numbers of values/characters you can merge, so for my 2000+ rows excel column it's not working)

Email Address
----------------
email 1
email 2
email 3
email 4
.
.
.
email 2000

Wanted result: "email 1; email 2; email 3; email 4;.........;email 2000"

Re: How to convert entire DataTable column into a string?

ewilson — Wed, 03 Aug 2022 23:42:27 GMT

Hi @IrinaMihalache,

One option would be to get the column of email addresses as a Collection and then use the Join Text action of the Utility - Strings VBO to concatenate them all together. You may have to throw in a call to the Utility - Collection Manipulation's Rename Field action though. The reason for that is the Join Text action expects the input Collection to contain a field named Input Value.

I've attached a very rudimentary example process. This example assumes you're getting the collection of emails from an spreadsheet titled Emails.xlsx in the C:\temp folder. It also expecting a column header of Email. You can, of course, change all those.

Cheers,
Eric

Re: How to convert entire DataTable column into a string?

whitehouse-UoL — Thu, 06 Jun 2024 09:44:17 GMT

Hi @ewilson
Your reply is really helpful, thank you! I cannot see the example process you mention, could you repost it please?

Thanks!

David

Re: How to convert entire DataTable column into a string?

ewilson — Thu, 06 Jun 2024 14:46:57 GMT

@whitehouse-UoL

Unfortunately, there doesn't seem to be an option here for me to attach a file. I may not have permission, or it just may be a missing feature on this new community. Send me a DM and we'll see if I can get it to you that way.

Cheers,

Eric

Re: How to convert entire DataTable column into a string?

whitehouse-UoL — Fri, 07 Jun 2024 09:19:30 GMT

Hi @ewilson
Thank you for your email. I am very new to BP and I have run into an error that I am unsure how to resolve.
The Error is: Internal : "Failed to translate data - No conversion available from Text to Collection"

It occurs At the very start of the "Utility - Strings::Join Text" Stage?

Any suggests would be greatly appreciated.

Thanks
David

Re: How to convert entire DataTable column into a string?

whitehouse-UoL — Fri, 07 Jun 2024 10:00:18 GMT

I should maybe also say I am working with ID numbers all numbers no text.

Re: How to convert entire DataTable column into a string?

Denis__Dennehy — Fri, 07 Jun 2024 10:12:54 GMT

In terms of the speed of your original solution being too slow, have you tried stepping out of the object with your logic or stepping over it in studio? The reality is that a very large loop in studio can seem extremely slow because Blue Prism needs to draw the diagram changing - however usually the logic is extreamly rapid at runtime because it does not have to draw your diagram and becomes just a normal .NET coded for... next loop.

For the error you have got in a later comment we would need to see your calculation syntax to understand the cause of the error - which suggests you are not referencing your collection correctly.

Re: How to convert entire DataTable column into a string?

whitehouse-UoL — Fri, 07 Jun 2024 11:26:42 GMT

Hi @Denis__Dennehy
In the "Utility - Strings::Join Text" Stage I am referencing the collection like this, as I want only the rows of this feild to be created as a string.

[Collection Out.Item Value].

Does this look correct?

Thanks
David

Re: How to convert entire DataTable column into a string?

ewilson — Fri, 07 Jun 2024 15:13:21 GMT

@whitehouse-UoL what version of Blue Prism are you running, and do you have the latest release of the Utility - Strings VBO from the DX installed?

Re: How to convert entire DataTable column into a string?

devneetmohanty07 — Mon, 10 Jun 2024 06:13:09 GMT

Hi @whitehouse-UoL ,

The problem is with the parameter that you are passing in the above screenshot. You need to pass a collection but you are passing a field column of the collection which seems to be of Text type.

Let me show you with an example on how you can achieve to get all the email addresses using the Join Text action.

Assuming I have a collection with me where let say I have multiple columns and one of the column consists of all the email addresses that I want to join as shown below:

Now, what I want is to get all these email addresses as part of a single string. For this, I have created the below workflow:

So first, I will call the Split Collection action from Collection Manipulation business object. This I would need so that I can just have a single collection with the email address column as the Join Text action expects us to pass the collection in the same way. For using the Split Collection action, you need to have two single row collections which will have the mentioned fields divided from your initial collection which you want to split.

For this I will have two collections, first one called Email Collection Template with the field name as 'Email Addresses' same as what I have in my initial collection and second collection is called Email Collection Template 2 with the other field values that we do not want from the initial collection. Please ensure the collection have single row option enabled as shown below:

Now, we will pass these collection templates and the initial collection called 'Data' where my original data is stored to the Split Collection action as shown below. In the output tab of this action, we will just assign Collection 2 parameter to another empty collection called Email Addresses as we just want the values for the 'Email Addresses' field as indicated in the first collection template:

Once, we execute this action we will get the email addresses mentioned in just one column as part of the Email Addresses collection:

Now, we will use rename the field of the collection from 'Email Addresses' to 'Item Value' because the Join Text action expects us to have a collection with just one column called as 'Item Value'. For this, we will use the Rename Field action from Collection Manipulation business object and rename the column in the Email Addresses collection and store the changes in the same collection as shown below:

Once, we execute this action, you should have the updated Email Addresses collection with the column name now as 'Item Value':

Now, we will finally use the 'Join Text' action from Strings business object and pass the Email Addresses collection along with ';' as the separator and we can enable the Trim Values parameter to get rid of extra spaces and this result will be stored in a text type data item:

The result of this action should return you the data item with all the email addresses joined together as a string value:

This way we can directly get the email addresses in a string without using any loops over a collection as mentioned before by @ewilson

Re: How to convert entire DataTable column into a string?

whitehouse-UoL — Tue, 18 Jun 2024 10:38:09 GMT

Hi All,
Thank you for you help on this question.
@devneetmohanty07 Your Solution worked perfectly.

Thanks again @devneetmohanty07 , @IrinaMihalache @ewilson . @Denis__Dennehy

Have a great rest of the week.

David