Data manipulation in excel table having Parent and Child columns

Help Studio
, ,
1

Hi, I have a scenario where I deal with data manipulation in excel with a table having Main and Sub-Headers. Attaching photos for reference.

image

I need to delete columns from the child table that do not start with “Cases”. But doing so would remove parent column data. The Main table headers also needs to be intact at the same time.

image

I have tried to find a workaround using datatable and excel operations both, but none seem to work.
Has anyone faced a similar problem statement and knows how can I go about this solution.

Thanks,
Mansi.

1 Like
2

Can you please share the excel here.?

3

Hi @mansi.garg

I think this way can work

  1. Read the excel using read range by specifying Range as “A5” and store in dt1

  2. Now use the below assign activitiy to get the list of columns who name doesn’t strated with cases

column_arr= (From dc in dt1.Columns.Cast(Of String)
Where Not dc.ColumnName.ToString.StartsWith(“Cases”)
select dc.ColumnName.ToString).ToArray

Now u will get the array of columns names in column_arr variable.

Now loop through each element and use remove column activity to remove the datatable.

Now write the dt1 to the same excel using write range with range as A4

Regards

Nived N :robot:

Happy Automation :relaxed:

3 Likes
4

MainSubHeader Excel Table Problem.xlsx (11.3 KB)

Please find the file for table format reference. Sheet 1 has the problem statement table. Sheet2 has what the output should look like.

Thanks,
Mansi.

5

Test.zip (9.2 KB)
Plss find the attached solution and let me know

6

@Divyanshu_Divyanshu your solution would be amazing in case if my main table headers were in merged cells. But in my case the Parent table headers are not placed in merged cells. They are in a different format, as the sample excel attached. I will try modifying the approach and apply your solution to find a workaround nonetheless. Will keep you posted if that works.

Thanks,
Mansi.

7

will try and let you know.

8

DatatableParentChild.xaml (20.4 KB)
Your desired output

9

Hi @Divyanshu_Divyanshu I am somehow unable to view the workflow. Can you please tell if there is a specific package that needs to be downloaded.

image
image1106×757 31.3 KB

10

Hi @Divyanshu_Divyanshu , With the logic you gave earlier I am able to break my input datatable into two separate datatables having only the desired values and as expected 4 columns each.
image

To append the child DT to parentDT headers; I used DT.Select on the childDT to convert it into array of datarows.
Then applying a foreach loop to the resultant array I am trying to pass the datarow variable in Add datarow activity to be appended to the ParentDT header But getting the following error.

image
image1842×769 43 KB

11

Use ImportRow in invoke method

1 Like
12

Which parameters to pass where in the method can you please elaborate

13

Tried Using it but its not appending both the datatables. Its giving the following result.

image

14

Instead of passing DataRow item in addDataRow activity, I tried passing ArrayRow. Its working and didnt give error.

image
image1347×637 32.3 KB

15

Test.zip (53.0 KB)
Try to open this xaml