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

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.

This is what we want the final report to look like (attached template):
Original.xlsx (9.8 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?

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.

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

Try to run this

Test.xaml (10.2 KB)

1 Like

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

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

Assign Activity:

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

Ensure following imports:


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