String manipulation question

Help
1

Hi community,
i have a datatable with 10 columns, every column contain the name of column+value.

Example
IDCLIENT | NAME
idclient128 NAMEjosephine
idclient1409 | NAMEfrank

I use a for each row and get row item, for the first column, column index 0 and value row
my goal is to bring only the value, erase the column name inside the value and write an excel file with the today date name.
Can you help me?
Thanks

2

For example, i know how can i use the substring.
Example
for the first row assign IDCLIENT = IDCLIENT.substring(IDCLIENT.indexof(“nt”)+1), BUT HERE HOW CAN I PUT THE LENGHT IF I DON’T KNOW THE EXACT LENGHT?

3

clientID= IDCLIENT.ToString.Remove(“idclient”).Remove(“name”).Split(" “c)(0).Trim
clientName = IDCLIENT.ToString.Remove(“idclient”).Remove(“name”).Split(” "c)(1).Trim

2 Likes
4

Thanks @Jarzzz
but in the first get row item i extract the string IDCLIENT
The value of IDCLIENT is: idclient128 → i need only 128

Thanks

5

clientID= IDCLIENT.ToString.Remove(“idclient”).Split(" “c)(0).Trim

6

Hi
Hope these steps would help you
—use a excel application scope and pass the file path as input
—inside the scope use a Read range activity and get the output with a variable of type datatable named dt
—now use a FOR EACH ROW activity And pass the above variable as input
—inside the loop use a assign activity like this
row(“IDCLIENT”) = row(“IDCLIENT”).ToString.ToUpper.Replace(“IDCLIENT”,””)

And another assign activity like this
row(“NAME”) = row(“NAME”).ToString.Replace(“NAME”,””)

this will change all the row values in the datatable

Next to this For each row activity use a Write range activity and mention the variable dt as input and enable the add headers property

Cheers @yiroday

1 Like
7

Thank you @Palaniyappan
very appreciate your help.
I’ve just doing data scraping of a table and iterate datatable in a FOR EACH ROW
inside i’ve put GET ROW ITEM (column 0) ITEM row output ID
GET ROW ITEM (column 1) ITEM row output NAME
etc.
how can i build a datatable and write it into excel?
Thanks

1 Like
8

before using FOR EACH ROW loop use BUILD DATATABLE activity and create a table with two columns with proper name both of type string and get the output with a variable of type datatable named FinalDt
–now inside the FOR EACH ROW loop next to those two GET ROW ITEM use a ADD DATAROW activity and mention the output variables obtained from GET ROW ITEM activity in ArrayRow PROPERTY LIKE THIS
{variable1.ToString,Variable2.ToString} and in datatable property mention as FinalDt
so that for each iteration those two variables with values will be added to that datatable

–next to this FOR EACH ROW loop use a WRITE RANGE activity and mention the input as Finaldt and enable ADD HEADERS property

Cheers @yiroday

1 Like
9

Thanks for all @Palaniyappan
in GET ROW ITEM i extract a value of string type, because i need to do string manipulation on that, how can i pass ID VALUE,NAME VALUE in an array of row?

Thx

10

may i know how the values comes actually @yiroday

1 Like
11

ID was ID20, now is 20
NAME was NAMErick now is RICK
so now i have two string variables, one ID 20 and another one VALUE RICK
Thanks for your work @Palaniyappan

12

if these values are stored in two variables like variable1 and variable2
then while mentioning in ArrayRow property mention as
{Split(variable1," “).Last().ToString, Split(variable2,” ").Last().ToString}

Cheers @yiroday

1 Like
13

{Split(variable1," “).Last().ToString, Split(variable2,” ").Last().ToString}

With this function i can put string variables into an array of row? And then pass it trough ADD DATA ROW into FINALDT?

14

yes @yiroday

1 Like
15

I create a datarow variable

print2
print2747Ă—26 2.07 KB

Then i’ll try to assign string variables but this is the error message:
print2

Why we doing split for insert string variables inside an array?
Thank you @Palaniyappan

16

to get individual ID and NAME value
and here directly mention the expression in ArrayRow property of ADD DATAROW activity
{Split(variable1," “).Last().ToString, Split(variable2,” ").Last().ToString}

Cheers @yiroday

1 Like
17

And if i just have the value stored in a string variable, how can i pass it in a datarow array?
Thanks @Palaniyappan

18

Then like this

@yiroday

1 Like
19

If my variable1 and variable2 are just a string, i fill it without .tostring?
And i insert that in ADD DATA ROW → ARRAYROW or DATAROW?

Sorry and thanks for your explanation :slightly_smiling_face: @Palaniyappan

1 Like
20

in ArrayRow
datarow will take input only as System.Data.Datarow

Cheers @yiroday

1 Like