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.
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.
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.
Can you please share the excel here.?
I think this way can work
Read the excel using read range by specifying Range as “A5” and store in dt1
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”)
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
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.
Test.zip (9.2 KB)
Plss find the attached solution and let me know
@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.
will try and let you know.
DatatableParentChild.xaml (20.4 KB)
Your desired output
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.
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.
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.
Use ImportRow in invoke method
Which parameters to pass where in the method can you please elaborate
Tried Using it but its not appending both the datatables. Its giving the following result.
Instead of passing DataRow item in addDataRow activity, I tried passing ArrayRow. Its working and didnt give error.
Test.zip (53.0 KB)
Try to open this xaml