Extract the details from rows

Help
1

Hi,

How can I extract the 2 items (Refer to the Table 2 for Code 1 and Code 2) from column, Code in Table 1 as follows. Please advice.

Table 1 (Original):

Name Code Date
Lina T_KK_AS-PG-K-HYH-UYER 5/21/2018
Kelly T KK_AU_PG_K-AUG-UHGE 4/16/2018
Harry T_KK_AS-PG-K-HAE-HYUO 3/2/2018
… … …
… … …
2

Table 2 (Wanted output as):

Code 1 Code 2
HYH UYER
AUG UHGE
HAE HYUO
3

Hi @Katyy1,

Use build datatable. (Code 1,code2)

Use for each row activity to loop through the data
Get the value of the code
strcode=row("Code")
Arrvalue= strcode.split("-"c)

Code1=Arrvalue(3)
Code2=Arrvalue(4)

Split the code

Use add data row and append the value into the new datatable.

Regards, Arivu :slight_smile:

4

@Katyy1

  1. If you are retrieving data from excel, you can directly use read Column activity to read only the Code column or
    If the data is already in data table, use
    Datatable.DefaultView.ToTable(false, “Code”)
    This will return a datatable with only the Code column.
  2. Build a new datatable “outputDatatable”
  3. Use for each row activity to Loop through each row
  4. Use regex or string manipulation techniques to retrieve the Code1 and Code2 values from the column.
  5. Use Add Data Row activity to insert the Code1 and Code2 values as new row into outputDatatable.
5

Hi,

Would you mind elaborate further on your suggested solutions?

Thanks.

6

Hi @Katyy1,
Please find the solution Extract the details from rows.xaml (14.3 KB)
for the problem, assumed the 2nd code value as T_KK_AU-PG-K-AUG-UHGE, in your query it was given as T KK_AU_PG_K-AUG-UHGE

7

Hi @rtallapudi,

Thanks for sharing the solution!

Unfortunately, each row of code value in Code column has different delimiters (e.g. - ,_ , space). How should I split different delimiters in a code value?

Thanks!

8

Hi @Katyy1
Now I have used Regex to pull only last 8 characters where we have code1 and code2.Attached the workflow here Extract the details from rows.xaml (15.2 KB)

This might help to resolve the problem.