Hello All,
I have an extraction result that convert extractions into multiple rows.
I want to add all values in multiple rows, as seen below into one unified row in a data table.
How can I do this?
Hello All,
I have an extraction result that convert extractions into multiple rows.
I want to add all values in multiple rows, as seen below into one unified row in a data table.
How can I do this?
Enumerable.Range(0,dt.Columns.Count).ToArray
and change type argument to integerDt.rows(0)(Currentitem) = If(filtereddt.RowCount>0,filtereddt.Rows(0)(currentitem).ToString,"")
dt = dt.AsEnumerable.Take(1).CopyToDataTable
Cheers
Thank you let me try this.
Multiple ways, as @Anil_G suggested, it can be done inside a foreach column to make it dynamic:
My take would be:
For each Column in
dt_Columns.Columns.Cast(Of DataColumn).Select(Function(x) x.ToString).ToArray
(This would be of type String)
Inside the For Each:
Small Linq to extract the value of each column, together with an Invoke Method “Add” to add the value to a list:
dt_Columns.AsEnumerable.Where(Function(x) Not String.IsNullOrEmpty(x(column).ToString)).Select(Function(y) y(column).ToString).First
Finally at the end, a “Add Data Row” activity that would add a single datarow, by converting the list created within the loop, to an ArrayRow.
Attached for reference:
ColumnsForEach.xaml (9.4 KB)
Second option - faster but dependant on Columns:
Single Linq:
(From num In Enumerable.Range(1,1)
Select tmp_TempDT.Rows.Add(
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 1").ToString)).Select(Function(y) y.Item("Table 1").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 2").ToString)).Select(Function(y) y.Item("Table 2").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 3").ToString)).Select(Function(y) y.Item("Table 3").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 4").ToString)).Select(Function(y) y.Item("Table 4").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 5").ToString)).Select(Function(y) y.Item("Table 5").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 6").ToString)).Select(Function(y) y.Item("Table 6").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 7").ToString)).Select(Function(y) y.Item("Table 7").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 8").ToString)).Select(Function(y) y.Item("Table 8").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 9").ToString)).Select(Function(y) y.Item("Table 9").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 10").ToString)).Select(Function(y) y.Item("Table 10").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 11").ToString)).Select(Function(y) y.Item("Table 11").ToString).First.ToString,
dt_Columns.AsEnumerable.Where(Function(z) Not String.IsNullOrEmpty(z("Table 12").ToString)).Select(Function(y) y.Item("Table 12").ToString).First.ToString
)
).CopyToDataTable
This will output the same as above, but it needs a single row per column in your DT.
Hope this helps!
Hi @sharon.palawandram ,
Maybe you could also try with this alternate :
OutputDT
like below :OutputDT = DT.Clone
Add Data Row
Activity with the below Expression in the ArrayRow
property and OutputDT
as the Datatable where the row needs to be added.(From i In NormalDT.Columns.Cast(Of DataColumn).Select(Function(x)x.ColumnName)
Let x = NormalDT.AsEnumerable.FirstOrDefault(Function(y)Not(String.IsNullOrWhiteSpace(y(i).ToString)))
Let item = If(x Is Nothing,"",x(i).ToString)
Select item).ToArray
Implementation :
Debug visuals :
Assumption/Constraints:
With the focus on dynamic, keeping DataType from the Columns we can do:
arrFlatten | Object()
. Array Of Objects =
(From i In Enumerable.Range(0, dtData.Columns.Count)
Let v = dtData.AsEnumerable.Where(Function (x) Not(isNothing(x(i)) OrElse String.IsNullOrEmpty(x(i).toString.Trim))).Select(Function (x) x(i)).FirstOrDefault()
Select vf =v).Cast(Of Object).toArray
and can proof:
Hi @supermanPunch I am tryin this, but getting an error below:
Qualified it with Cast(Of System.Data.DataColumn)
Thank you @ppr, @ignasi.peiris & @supermanPunch, I tested all your recommended solutions and they all work!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.