No loop/foreach datatable

[Junior458]

Hi all,

This is my case on an Excel sheet

Input:
James DOE
Michael Walker
Mary Splice

Output should be:
jamesdoe
michaelwalker
marysplice

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

[ppr (Peter)]

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

[Junior458]

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

[ppr (Peter)]

String.ToLower Method (System)

[Junior458]

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()

[ppr (Peter)]

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

[Junior458]

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