Combining 2 rows from a datatable

RPA Discussions
1

I have been able to scrape data off a website and store it into a datatable (not csv/excel file) however, is there any way to combine 2 or more rows (if there are 2 or more (ie dynamic) rows) of the same column from data-scraping (web recorder) and separate them using “;” into one cell?
(Maybe using the ‘assign’ activity or something like that)

2

combine 2 or more rows (if there are 2 or more (ie dynamic) rows) of the same column

you mean combine multiple rows into same row? (separated by ; ?)

3

yep, precisely

4

like ive tried assigning the variable, used in the ‘Add data row’ activity, = string.join(" ; ", row.item(0).ToString
But that doesnt seem to combine multiple values just gives me the first one without the " ; " either

5

you can firstly create a new dataRow variable, then loop over the datacolumns and for each column do
newRow(col.ColumnName) = String.Join(";"c, in_dt.AsEnumerable.Select(function(x) x.Field(of string)(col.ColumnName)))

this will join the rows into one value for each column

you can refer to this
MergeRows.xaml (8.6 KB)
pass in your datatable like this
image

before
image
after
image

6

I’m quite new to this so I’m not sure what everything means, but I’ve seen ‘c’ after “;” many times, what does it mean?

7

it converts the string to a char. Actually thats optional for string.join() and you can remove it. For some functions such as string.Split() it only accepts char so you need to use it

i think its the same as cchar(“;”)

8

Would it be because it was only reading 1 value that the “;” doesn’t show?

9

nothing to do with that , look at the arguments. If its char() then you need to convert your string to char using cchar(“string”) or "string"c

image

image

10

I was just wondering if there is a different way to combine 2 values in Studio?
Like under ‘For Each Row in Datatable’ - ‘assign’ → To: variable = row.item(0).ToString + " ; " + …
But the “+…” is where i’m stuck cuz using row.item(0) twice doesnt make sense, but I have the values stored in the same column so thhe item() must be (0)

11

yes there is … . . .

you can loop through columns and rows of the table then for each row, assign
newRow(col.ColumnName) = newRow(col.ColumnName).ToString + ";" + CurrentRow(col.ColumnName).ToString

its literally the same thing as my answer above but just takes longer to write

MergeRows.xaml (11.3 KB)

12

this wont work, just look at the 2 answers i gave you

13

I tried implementing the code, but the result just turns the column in the csv file empty

15

can you upload the xaml and csv here?

16

@Karl_Tang
are you calling my xaml and passing the datatable like this?

image
image1062×245 16.8 KB

the result should look like this

before
image

after
image

17

managed to get it working! thanks heaps

1 Like
18

thats great! :slight_smile:

1 Like
19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.