Add values from multiple rows in a data table to one row in a data table

[sharon.palawandram]

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?

[Anil_G]

@sharon.palawandram

1. Read the data into a datatable
2. Use a for loop on the column Enumerable.Range(0,dt.Columns.Count).ToArray and change type argument to integer
3. Use a filter datatable with column as currentitem and Is not empty
4. Assign Dt.rows(0)(Currentitem) = If(filtereddt.RowCount>0,filtereddt.Rows(0)(currentitem).ToString,"")
5. Outside loop if you want only first row dt = dt.AsEnumerable.Take(1).CopyToDataTable

Cheers

[sharon.palawandram]

Thank you let me try this.

[ignasi.peiris]

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!

[supermanPunch (Arpan)]

Hi @sharon.palawandram ,

Maybe you could also try with this alternate :

1. Make a Clone of the Input Datatable to an output datatable, say OutputDT like below :

OutputDT = DT.Clone

2. Next We can use the 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 :

[ppr (Peter)]

Assumption/Constraints:

• no complex DataTypes defined by the different DataColumns
• only 1 or no value, no need to concatenate multiple values

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:

[sharon.palawandram]

Hi @supermanPunch I am tryin this, but getting an error below:

[ppr (Peter)]

Qualified it with Cast(Of System.Data.DataColumn)

[sharon.palawandram]

Thank you @ppr, @ignasi.peiris & @supermanPunch, I tested all your recommended solutions and they all work!