How do I do the Data Massaging as following in a data table?

Help Studio
, ,
1

Hi Team,

I am having a table as shown below:

Name Age Place Description Vendor
User 1 23 Pune This is UiPath
a description for
user one
User 2 24 Mumbai Demo of re UiPath
quirements
User 3 25 London UiPath is a autom UiPath
ation tool
User 4 26 Boston User four be UiPa
longs to the Boston th
in USA
User 5 27 Delhi This is the la Ui
st example to Pat
make this line comple h
ted

And I wish to have a table as shown below:

Name Age Place Description Vendor
User 1 23 Pune This is a description for user one UiPath
User 2 24 Mumbai Demo of requirements UiPath
User 3 25 London UiPath is a automation tool UiPath
User 4 26 Boston User four belongs to the Boston in USA UiPath
User 5 27 Delhi This is the last example to make this line completed UiPath

I hope the requirement is clear, as I want to do the data massage such that data moving to multiple lines in the table is appended to previous lines.

Also There are empty lines in the raw data after each and every record, I wanted to remove that as well.

I know I can do it wit some messy unreliable complex string manipulations, but I want some clean and simple logic to satisfy these conditions.

@experts - @ppr @Krishna_547

Thanks and Regards,
@hacky

2

@hacky

  • fill up the empty Name values with the value from previous row
  • afterwards group by the data by Name and create the concated values from the group members
3

@ppr
Can you please explain the activities and logic bit more in detail? Like for my case in particular…

User1, User 2, etc is just an example here. In real time, it can be "User 1, User 1, User 2, User 1, etc. "

4

Hi ,

I think the below steps should work :

Build DT with the required columns

  • For each row , if name column has some name then take tempVariable to append the contents of the next consecutive rows untill the name col has some value .

so , in the first iteration Name column has some name(User 1) so use variable to concatenate the contents of decription column and use add data row to add these text to the newly built DT.

Hope it is not that confusing :sweat_smile:

1 Like
5

@hitesh

6

would it look like this?

Name Age Place Description Vendor
User 1 23 Pune Line 1 User One UiPath
Line 2 User One
User 2 24 Mumbai Line 1 User Two UiPath
Line 2 User Two
User 1 23 Pune Line 3 User One UiPath
Line 4 User One

otherwise can you share this alternating User Block sample data with us? Thanks

7

@ppr

What I meant there was that column one might have same Name multiple times.

So populating the empty cells in colum one willy nilly will confuse the logic since name data is not unique for every record.

Also, only thing i want was to merge those columns which are spreading their data into multiple lines keeping in mind that column one data can be used as a pointer to define a new record.

Hope this is clear now.

8

@hacky

Vars & Preperation

grafik
grafik820×133 26.1 KB

grafik

Filling Up User Info:

grafik
grafik695×490 32 KB

Doing GroupBy and Concatenations with LINQ

grafik
grafik724×205 39.9 KB 

(From d In dtData.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
Let dc = String.Join("",grp.Select(Function (x) x("Description")))
Let vc = String.Join("",grp.Select(Function (x) x("Vendor")))
Let ra = New Object(){k, grp.First()("Age"),grp.First()("Place"),dc,vc}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Worked with a reduced sample set:
Input:
grafik
After filling up:
grafik
After Grouping and concatenations:
grafik

Find starter help here:
GroupBy_1Col_FixGroup_ConcatCols.xaml (10.8 KB)

As you can see the defect on isa vs is a, but needed concatenation without seperator for another vs. an other we do have some cases for doing a fine tuning