How to remove a row from excel that contains digits?

Help
1

Just as the title says… How do i remove rows that contain digits?

2

Hi @Ibra

dt.AsEnumerable().Where(Function(row) IsNumeric(row(column).ToString.Trim)).ToArray()
will return an array of DataRows

See this

Thanks,
Prankur

1 Like
3

@Ibra

Let us take you are having a datatable dta.

dtb= (From p In dta.Select
        where not  p.ItemArray.ToList.Any(Function(x) Is Numeric(x.ToString))
       Select p).ToArray.CopyToDataTable

dtb will contain the rows which don’t have digits.

Regards,
Mahesh

1 Like
4

Tried it:
1

Got this exception:

2

This is my excel:

3

5

@Ibra The exception is because you are trying to convert the enumerable collection of datarow to data table using “CopyToDataTable()” but the query is not returning any value. Your excel contains only one record under and the Column1 is not numeric.
if check is there any result from the linq query, if there convert it to Datatable.
Also you are using a foreach loop and again using linq to compare data. This is wrong. Linq query is an alternative for the foreach loop. You should not use both together. Either Loop through each record and check the column in that row has numeric value or use linq to check for numeric value.
Linq also loops through all the records internally and returns the matching records.
Try,

Capture
Capture773×296 11.2 KB

6

I tried what you recommended but i got this error and i think its because my variable arr is of type DataRow[].

1

7

@Ibra The variable arr should be of datatype System.Data.EnumerableRowCollection(System.Data.DataRow) and not Array(System.Data.DataRow)
Change the datatype. It will work.

9

(Correct me if i did this wrong)

My testing workflow:

3
3598×783 20.3 KB

And here is the excel comparison (before and after i ran the robot):

BEFORE
1

AFTER
2

Conclusion:

It CONVERTED the first row that contains ONLY strings to numeric value (123).

This was not the intended purpose.

It should actually remove all the numbers (123) in that column (First).

10

@Ibra
This is happening because you are writing the values into the input excel itself without clearing the values in the excel file.
The input file contains 5 rows, out of which first row is the header, there are 2 rows with numeric values and 2 with alpha numeric values. According to the business rule and the bot that you deveoped, your output datatable will have 3 rows, first row is the header and 2 rows will have result.
When you write the values into the same input file, the resultant 3 records will be replaced and additional values in the input file remains in the file itself. Hence 4th and 5th rows.

11

Ok now i understand!

So i read your clarification very careful and decided to create a sheet2 where the output will be.

And the result was:

First
123
123

So my question would be:

Now that i moves the digits to another sheet - how do i instead remove them from the current sheet they are placed in?

12

@Ibra
You can use select range and then use hot keys to delete. But some reason its not working.
Other solution is to open the excel file, select the content and delete.

Please let me know if you get any other solutions.

13

Hmm maybe there is way to call that assign activity - that moved the digits to another sheet, and tell it to remove them instead with a syntax.

Maybe someone else have what we are looking for?