No loop/foreach datatable

Hi all,

This is my case on an Excel sheet

James DOE
Michael Walker
Mary Splice

Output should be:

This should be done without using any foreach or loop, the spacing and uppercase should be removed for DB purposes

Any assistance for a solution would be appreciated

welcome to the forum

with the assumption that data is already present in a datatable have a look on this approach:
TrimRemove_AllCellsAllRows.xaml (9.0 KB)

with just editing the replaces it allows to do some corrections on all cells and rows

1 Like

It worked but how would i replace the uppercase to lowercase?

Thanks ppr
Out of curiosity could you maybe guide or briefly explain what this specific code is doing?

(From r In dtData.AsEnumerable
Select ia = r.ItemArray.toList
Select ic = ia.ConvertAll(Function (e) If(isNothing(e), Nothing, e.toString.Replace(" “,”")).toArray()).toArray()
Select dtCorrected.Rows.Add(ic)).CopyToDataTable()

its a linq statement

(From r In dtData.AsEnumerable - iterating over all rows
bringing the ItemArray (Array with all column values) to a list
ia.ConvertAll(Function (e) If(isNothing(e), Nothing, e.toString.Replace(" “,”")).toArray()).toArray() - iterates over all cel values and is doing the corrections (e.g. replace)
Select dtCorrected.Rows.Add(ic)). - adding the modified ItemArray as new row to the datatable dtCorrected

Thanks a lot ppr, i learnt quite a bit today.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.