Combining 2 rows from a datatable

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)

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 ; ?)

yep, precisely

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

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

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?

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(";")

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

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

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)

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)

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

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

can you upload the xaml and csv here?

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

the result should look like this

before
image

after
image

managed to get it working! thanks heaps

1 Like

thats great! :slight_smile:

1 Like

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