hello,
I want to convert this
table to this new table without excel activity.
Please help
Use Generate Data Table to get oldDT, Build Data Table to create newDT, then For Each Row with Add Data Row using expression like … {row(“Col1”).ToString, row(“Col2”).ToString} to map values, finally Output Data Table to display newDT.
Happy Automation
pivot your data without Excel activities, use a LINQ query in an Assign activity.
1. Build Data Table: Create dtOutput with columns curr, off, cash, and noncash.
2. Assign Activity:
To: dtOutput:(From row In dtInput.AsEnumerable()
Group row By c = row(“curr”).ToString Into Group
Select dtOutput.Rows.Add({
c,
Group.Where(Function(x) x(“type”).ToString = “off”).Select(Function(x) x(“rate”)).FirstOrDefault,
Group.Where(Function(x) x(“type”).ToString = “cash”).Select(Function(x) x(“rate”)).FirstOrDefault,
Group.Where(Function(x) x(“type”).ToString = “noncash”).Select(Function(x) x(“rate”)).FirstOrDefault
})).CopyToDataTable
Check if this works
Welcome to the community
To make it dynamic with dynamic columns and rows you can follow below steps
output:
in_dt - Input datatable
out_dt - output datatable
- Sort datatable is done on
"type"column to ensure order is taken care and data is accurate - To create the columns dynamically using generate datatable from text activity with value as
"curr,"+String.Join(",",in_dt.AsEnumerable.Select(function(x) x("type").ToString).Distinct())and this would be the out_dt columns - Loop on distinct currencies as number of rows depend on number of currencies using input as
in_dt.AsEnumerable.Select(function(x) x("curr").ToString).Distinct() - To load data using add datarow with arrayrow as
(currentText+","+String.Join(",",in_dt.AsEnumerable.Where(function(x) x("curr").ToString.Equals(currentText)).Select(function(x) x("rate").ToString))).Split(","c)to populate out_dt. Here currentText is loop variable
Hope this helps
cheers
Hi @Anil_G,
Thank you for your reply.
Can you look into this error in Add data row activity: Input array is longer than the number of columns in this table.
1 Like
looks like in generate datatable options you did not select csv parsing and use first row as header
cheers
@Anil_G
Checked the CSV parsing and it worked.
Thank you so much. This really helps me with my automation, and I’m so grateful for your help.
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.