Question

Parse characters in multi-value string to store in limited database field of 75 Characters

Workflow

Updated June 1, 2018

asked on May 31, 2018

I have a multi-value field that I want to parse through. Each row in the database can only store 75 Characters so that is my break point.

If the String contains 200 Characters for example, I have to get the String Char Count =200.

Then 200/75=2.667 Rounded to whole =3 :This gets me the Row Count.

I need to now load the parsed sections of the whole Multi-value string.

For Each Row:

I know I need to get the Start Char and End Char. I do this by:

(Row Count*75)-74 = Start Char

(Row Count*75) = End Char

How can I feed the Start Char - End Char Range to get me the portion of the Multi-value string I need to store at that given Row?

0 0

Answer

SELECTED ANSWER

replied on June 1, 2018 • Show version history

Hi Chase,
I had a similar problem filling out a pdf form with multiple fixed length rows.
Here is the general idea of what I ended up with, pattern matching to the rescue!

First step I am just assigning some text to a multi-value field to use.

Pattern matching step:
Input is your multi-value token combined into one line using the token dialog, applying an index and selecting all values separated by a space. %(Input#[ ]#)

The pattern match I used is: .{0,75}\b combined with the 'All matches (as a multi-value token)'
This splits the input token into a new multi-value token each with 75 characters or less ( I am splitting on word boundaries using the \b but that is not necessary, you could just use .{0,75} and split words)

You can then iterate over your new multi-value token from the pattern match and insert each value into your database.

My input token:

Result after pattern match:

No need to count lengths, start, end etc. I originally attempted doing that. Ugh

~ Andrew

edit: some of my token and test values were slightly different, thus slight differences in some of the screen shots ......

2 0

replied on June 1, 2018

Thank you. I'm sorry I actually meant Multi-Line field from Forms. But it works just the same in fact easier as I don't have to combine the values into one string as you have.

This works perfectly thanks!

0 0

Replies

replied on June 1, 2018 • Show version history

I have a solution but it's quite complicated so let me break it down into steps.

Create placeholder tokens for the following
1. Character Count = # of Characters in String
2. Row Count = Character Count / 75 rounded up to the nearest integer
3. Character Storing Token = an empty, multi-value token that will store the characters to be inserted into each row
Create a Repeat activity with condition set to $(Repeat_Iteration) is less than or equal to %(Row Count). Make sure to set the "Start the Iteration token at:" to 1.
Within the loop, create a Token Calculator that creates two tokens. We will use these tokens later in the pattern matching activities
1. Start Char = (%(Repeat_Iteration)*75)-74
2. End Char = (%Character Count)-(75*%(Repeat_Iteration))
After the Token Calculator, create a conditional decision:
1. Branch 1 with condition such that %(TokenCalculator_End Char) is greater than 0
2. Branch 2 with condition such that (TokenCalculator_End Char) is less than or equal to 0
Within first, create a pattern matching activity with the pattern below (without the "1." in the beginning). Regex is quite inconvenient when it comes to grabbing content within nth and mth characters. The round about way is to grab everything between nth character and "mth character counting from last character of the string". That's why the End Char token was calculated as 225-(75*%(Repeat_Iteration)).
1. (?<=^.{%(TokenCalculator_Start Char)}).+(?=.{%(TokenCalculator_End Char)})
Append the token generated from above to the Character Storing Token
In the second branch, create another pattern matching activity with the pattern below ((without the "1." in the beginning). This grabs everything from the Start Char token to the end. We can't use the same pattern as step 5 because TokenCalculator_End Char would be negative in the final iteration.
1. ^.{%(TokenCalculator_Start Char)}(.*)
Once again, append the token generated from above to the Character Storing Token

Using the above, I tested with a string containing 224 characters and was successfully able to store 1-74, 75-149, 150-224th characters in the multi-value token separately.

Best,

Ken

0 0

You are not allowed to follow up in this post.

Question

Question

Parse characters in multi-value string to store in limited database field of 75 Characters

Answer

Replies

Sign in to reply to this post.