Split Multiple Rows with no spaces to get Specific Data

Hello,

I am currently developing a process which requires me to pull data from a banking website, and then add this data to an array, to later be added to a spreadsheet. I am using a Get Full Text activity to get the items, and I only want two items; Ordered By, and Payment Details. I cannot figure out how to split the data as there are no spaces between the title and the required data. An example will be below. Items needed are in bold.

Amount Credited
GBP 567,000.00

Value date16-DEC-2019
Beneficiary nameEXAMPLE NAME
Credit account12345678 96-10-24
Ordered byOTHER EXAMPLE NAME
On instructions fromBANKNAME PLC
(LOCATION OF OFFICE)
ADDRESSLINE1
CITY
COUNTRY

ReferenceEXAMPLEREFERENCE
Payment details25 Street Name

Does anybody have any ideas as to how I can get the required data? Any help is appriciated.

Thanks in advance!

2 Likes

You can create an string array variable and assign like this:

array = “my big text”.Split(Environment.NewLine.ToCharArray, StringSplitOptions.RemoveEmptyEntries)

inside for each you put 2 IF to see if the line start with Ordered by or Payment details :slight_smile:

3 Likes

Then use Substring manipulation to find their values

@bcorrea apologies, I should’ve been clearer. I only want the values of the data, I have changed the example above and the required data is bold. Your solution has worked for getting the rows though, thank you for that.

yes, after that will be a bit easier to remove the Ordered by or Payment details text from the data…

@bcorrea,

Do you have any ideas how I could split the titles and data with no spaces?

Yes, i know how to do it, just was giving you some time to try it first, if you can share what you got so far, i can help with the rest of it.

@bcorrea,

This is what I got, but it had a validation error.

image

So that is how you want to get the data for the line that has the order by right? if you are sure there will never be a Y inside the data, you can use:
data = item(5).Split(“y”)(1)

I tried this, but it has given me a validation error. Option Strict On disallows late binding

Hey @Palaniyappan,

Do you think you could help? Please see the main post above :slight_smile:

1 Like

sure
lets take like the input is with a variable str_input
str_input = " Amount Credited
GBP 567,000.00

Value date16-DEC-2019
Beneficiary nameEXAMPLE NAME
Credit account12345678 96-10-24
Ordered by OTHER EXAMPLE NAME
On instructions fromBANKNAME PLC
(LOCATION OF OFFICE)
ADDRESSLINE1
CITY
COUNTRY

ReferenceEXAMPLEREFERENCE
Payment details 25 Street Name"

now use a assign activity like this
arr_lines = str_input.Split(Environment.Newline.ToArray())
where arr_lines is a variable of type array of string
–then use a FOR EACH activity and pass the above variable arr_lines as input
and change the type argument as string in the property panel
–inside the loop use a IF condition like this
item.ToString.Contains(“Ordered by”) OR item.ToString.Contains(“Payment details”)
if true it will go to THEN PART where we can use a assign activity like this
str_orderedby = System.Text.RegularExpressions.Regex.Match(item.ToString,"(?<=Ordered by).*").ToString.Trim

str_PaymentDetails = System.Text.RegularExpressions.Regex.Match(item.ToString,"(?<=Payment details).*").ToString.Trim

cheers @william.coulson

1 Like

Hi @william.coulson,

regexExtract.xaml (5.9 KB)

Thanks!

Kindly let know for any queries or clarification
Cheers @william.coulson

1 Like

@Palaniyappan attempting to test from beginning to end now, I’ll let you know what happens

1 Like

Hey @Palaniyappan,

Your solution has worked to an extent. Thank you! But when I was testing the seperate activities, I noticed that for the Payment details, sometimes it will span over one line. It picks up the first line, but if the Payment details value is, for example, 4 lines, then it will leave those out. Any ideas of what I could do for that?

Full test completed, it is now only taking the Order by values. I haven’t changed anything

1 Like

@Palaniyappan, how would I get the rest of the data if it is more than one line?

1 Like

The for getting payment alone
Mention like this

Once after getting arr_lines use a assign activity like this
Str_input = String.Join(“”,arr_lines)

Now str_input will have all lines in a line

Now use this expression outside the For each loop with a assign activity
Str_payment = System.Text.RegularExpressions.Regex.Match(str_input.ToString,"(?<=Payment details).*").ToString.Trim

Cheers @william.coulson

1 Like

@Palaniyappan,
Thank you very much for your help, the process is working with no issues!

1 Like

Awesome
Cheers @william.coulson

1 Like