How to separate multiple merged data in 1 column into individual columns?

Help Studio
, ,
1

The usual excel text-to-column is not helpful to process our data. I have an excel report (attached) with a column that contains numerous combined data that need to be separated into distinct columns. I need help writing a uipath bot that can achieve the desired report format.

This is what the raw data looks like (attached):
The data is in a Q&A format.

image
image1482×156 7.04 KB

This is what we want the final report to look like (attached template):
Original.xlsx (9.8 KB)

image
image995×160 4.51 KB

Desired split column data.xlsx (8.6 KB)

How might wewrite this? Or where can I find a good tutorial that specifically explains how to do this?

2

Use Build data table activity to create a data table with 4 columns.
Iterate through each rows of the input excel file, for each row,

  1. split current row by “,” and assign to a array variable say var1, so you will get 4 entries as an array
  2. use “add data row” activity to add a row to the created table, use
    split(var1(0),“:”)(1),split(var1(1),“:”)(1),split(var1(2),“:”)(1),split(var1(3),“:”)(1), to get all 4 answers.
3

Hi thanks for the quick response, might ye have a working solution file? I am not sure which activities from the activity panel I need to use

4

Try to run this

Test.xaml (10.2 KB)

1 Like
5

As the Text is JSON we can do it with a Oneliner:

grafik
grafik731×92 29.7 KB

Excel Application Scope & Read Range - addHeaders -output: dtData

Assign Activity:

dtDataParsed | DataType: DataTable = 
JsonConvert.DeserializeObject(of DataTable)("[" & String.Join(",",dtData.asEnumerable.select(Function (x) x(0).ToString)) & "]")

Ensure following imports:

grafik

6

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