Excel Remove rows not numeric

Hi,
I have to remove rows from Excel or datatable that have non-numeric values ​​in column “col1”.
I’d like to do it with regex.

Can you help me?

Thanks

give a try on
Assign activity
left: dtFiltered | datatype: DataTable
right:
(from d in yourdatatablevar.AsEnumerable
Where Not System.Text.RegularExpressions.Regex.isMatch(d(“col1”).toString.Trim, “\D”)
Select r=d).CopyToDataTable

in case of empty result is to expect avoid CopyToDataTable exception by defensive handling:

I have the error “Assign: The source contains no DataRows.” but there are rows on the table

see update post on jandling empty result:

Assign activity
left: Result| datatype: List(Of DataRow)

(from d in yourdatatablevar.AsEnumerable
Where Not System.Text.RegularExpressions.Regex.isMatch(d(“col1”).toString, “\D”)
Select r=d).toList

then check
If: Result.Count > 0
then: dtFiltered = Result.CopyToDataTable
Else: dtFiltered = yourdatatablevar.clone

But the error is that i have numeric rows but the datatable result null

lets do one by one.
maybe we fine tune only the regex like ^\d+$

can you share some samples for should be removed, should be kept? thanks

image

This is one example: i want remove rows that have non numeric value in colum “Col3”

Thanks

(from d in yourdatatablevar.AsEnumerable
Where System.Text.RegularExpressions.Regex.isMatch(d("col1").toString.Trim, "^\d+$")
Select r=d).toList

then check
If: Result.Count > 0
then: dtFiltered = Result.CopyToDataTable
Else: dtFiltered = yourdatatablevar.clone

also check col name screenshot shows col3 description is mentioning col1

i have the same error: Assign: The source contains no DataRows.

please share screenshot from your implementation. thanks

I have:

  • ReadRange Excel in datatable DT
  • Assicgn activity DT=(from d in DT.AsEnumerable
    Where System.Text.RegularExpressions.Regex.isMatch(d(“col1”).toString.Trim, " ^\d+$")
    Select r=d)CopyToDatatable

we mentioned that CopyToDataTable is throwing this exception in case of empty result. Therefore we shared the defensive way with you.

also check the col name as mentioned above

The result is not empty but i have this exception.
I try the list and the result is empty olso and this is wrong.

yes but a different thing, we also get it sorted out.
Did you check the col names what was mentioned? is it using the right col name?

@d_liberati
just some visualisations for your case:
grafik

Flow:
grafik

Statement was aligned on your table samples and is using col3

i/o
grafik

find starter help here:
Filter_1Col_onlyDigits.xaml (8.3 KB)

Thanks
I used the same datatable to read and filter the file.
i created a new datatable and it worked.

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