How to delete complete row if contains alphabetical letters

Hi All,

I’m new to Uipath and need to find and delete complete row if contains alphabetical letters in Column (PO Number) in an excel then save it back as a CSV file

DT1=Datatable
ctp_Loop=Row
“PO number”=Column

Thanks for your support

3 Likes

Fine
—hope we have the datatble ready in our hand named dt
—now use a for each row loop and pass the above datatable variable as input
—inside the loop use a assign activity like this
Str_input = row(“yourcolumnname”).ToString
—then use a if condition like this

system.Text.RegularExpressions.Regex.Matches(Str_input,"[A-Z]").Count.ToString > 0

If this is true it will go to THEN part where we can use a ADD TO COLLECTIONS activity where in the collection property should be mention with a list variable of type list(of integer) named list_rowindex where this variable is created in the variable panel with default value like New List(of Integer) and in the item property of add to collections mention as dt.Rows.IndexOf(row)
And change the type argument as int32

—now next to this for each row loop activity use a for each activity and mention the input as list_rowindex and change the type argument as int32
—inside the loop use REMOVE DATAROW activity and mention the rowindex property as item and in the datatable mention as dt

That’s all now the datatable won’t have the string ah I guess any upper case letter

Hope this would help you
Cheers @MDias

Hi @MDias
Follow the Following Steps.

  • Use File Using Read range and Save Output In Data table Variable As dt.
  • Use For Each Row To Iterate data table row by row.
  • get the Value from One of Column Which you want To Check Using row(“YourColumnName”) and Assign it To variable.
  • Then use IsMatch Activity and Give the input Output as Input to it.
  • Give the “[a-z A-Z]” Pattern to ISMatch.
  • Stored Output Of IsMatch In Boolean Variable Suppose as Res.
  • Then Below this use If condition And give the condition as Res= True.
  • In the then Section Use InsertDeleteRows Activity and change the mode as Remove.
  • Give the Index Of rows to It.
  • Finally Write The Data table Using Write Range.
    Thanks & Regards

Hey @MDias

You can use below linq query to achieve what you are looking for.
Datatable ColListHavingAlphabetsDT=<YourDatatableName>.AsEnumerable().Where(function(r) Not System.Text.RegularExpressions.Regex.IsMatch(r.Field(Of String)("PO Number"), "[a-zA-Z]")).CopyToDataTable

Reference - DataTableCol Containes Alphabet or Not.xaml (6.7 KB)

Above sample will only take those rows which column PO Number is not having any alphabet letter in it. and writing those rows in a csv file.

Hope this will help you. In case of any doubts or queries lemme know :slight_smile:

Regards…!!
Aksh

3 Likes

Thanks all for your reply.
@Palaniyappan
I followed the instructions to some stage and lost after each activity step.
I’ve attached project files SeachAlpha.zip (23.1 KB) if you can have a look really appreciate.

Next to Count.ToString
Remove that TOString pls
Cheers @MDias

@Palaniyappan Thanks for the reply
now i’m getting below error & can you guide me for below steps as well.
—now next to this for each row loop activity use a for each activity and mention the input as list_rowindex and change the type argument as int32
—inside the loop use REMOVE DATAROW activity and mention the rowindex property as item and in the datatable mention as dt

1 Like

The datatype of that list variable is int32 and it should be
System.Collections.Generic.List(of int32)
We can get that in browse type option in variable panel under type column
@MDias

hey @MDias
Have you tried this:

Regards…!!
Aksh

2 Likes

@aksh1yadav
Sorry for the late reply.
I’m getting this error when i included to my workflow2019-09-01%2017_12_21-Runtime%20execution%20error .

@paul.placintar
Will you be able to help with this topic also

Hi @MDias

@aksh1yadav gave you the solution and a workflow

Just change from the initial code

r.Field(Of String)(“PO Number”)

to the following

r.Field(Of Double)(“PO Number”).tostring

This should work, based on the error screen you sent us

best regards

1 Like

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