Substring a column Without looping

Hi, I would like to substring the cells in a column from a data table but without using any loops, How can i do this?

Captura

Hi
Welcome to uipath community
Hope this expression would help you resolve this
dt = dt.Asenumerable().Where(Function(a) a.Field(of string)(“yourcolumnname”).ToString.SubString(0,4).ToString).CopyToDatatable()

Where dt is the variable from read range activity
Cheers @mateotabares

@mateotabares

For demo purposes I did a substring over all Rows and Columns and removed the letter from the value.
Find demo XAML here: mateotabares.xaml (8.4 KB)

From origin datatable a second empty datatable with the same schema were retrieved with the Clone method

LINQ statement is doing the conversion and adding the converted rows to the second datatable: dtConverted

Feel free to change and play with it. Let us know your open questions. Mark the solving post as solution. So others can benefit from it

grafik

What will you do with that DataTable after the substring? wont you loop it? to be honest, using linq will internally have a loop anyway…

Hi, I’m having some trouble understanding the logic in the LINQ statement…:pensive:

How can i do that both columns(Start and result) appear in the final datatable? My final goal is something like this:
image

So im not really sure how to use or modify the LINQ statament that you gave me…
Im sorry if im not making mysef clear. Thanks in advance

1 Like

I know how to do it with a for each row, but it would take A LOT of time because my dt has almost 100.000 rows… So i wanna do it in a more effcient way.

@mateotabares
Edited Post: Ok your scenario has the goal to update an additional column with a computed result. In your case for demo pusrpose: Col3 ist the substring(0,4) from Col2

Here are two approaches common:

  • create new rows (3 cols) and add it to a new/temporary datatable
  • update the 3rd column

For both approaches a datatable is to prepare within its structure with the additional column.

On my evening I will create a new demo xaml and will share it with you. Along with this I will give some eleaborations on the main blocks of the LINQ statement

Hello @mateotabares
You can do this using linq using this code

(From roww In DT.AsEnumerable() Select DT.Clone.Rows.Add(roww.Item("Some other data"),roww.Item("Column1"),CStr(roww.Item("Column1")).Substring(0,4))).CopyToDataTable

Check this workflow for better understanding

Linq_Substring.xaml (8.2 KB)

You can use macro for that
Using Invoke VBA you can achieve this

Sub Macro3()

Range("B1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
Range("B1:B10125").Select
Selection.FillDown

End Sub

For dynamic Use Read Range
then Assign Count=Datatable.Rows.count+1
Then Use Invoke Vba --pass parameter as {Count}
code will be

Sub Macro3(Count)

Range("B1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
Range("B1:B" & Count).Select
Selection.FillDown

End Sub

Note Code is for.
image

Thats is exactly what i mean, i can assure you that a manual loop will be a lot faster than linq solutions you see here…

@ppr
I think the second approach is the one that better fits my needs, because eventually that approach can be used in datatables with a lot of columns… am i wrong? Thank you for your time!!!

So you think that i should just go for the “for each row”? even when my datatable has so many rows? Thats interesting…

Yes, and to be honest 10k rows is not that much… there is no magic solution, if you need to transform every row, there will be a loop hidden in every code solution anyway…

@mateotabares
Find updated DEMO XAML showcasing both approaches mentioned in my post from above:
mateotabares2.xaml (10.8 KB)

1 Like

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