How to delete rows excel that contain unwanted data

hi, how i want to delete each row that has other than number at my first column?this is the example, i want to delete in column name ‘ain’ that contain other than number
EXAMPLE.xlsx (9.0 KB)

@Nur_Amin
Please try this in set variable

dt = dt.AsEnumerable.Where(function(x) IsNumeric(x("ain").ToString.Trim)).CopyToDataTable

Dt is the datatable in which you have

Left of equals symbol on left side of set variable and right of equals symbol on right side

Use advanced editor to give this formula

Cheers

Hi,

Another approach:

We can use DeleteRows activity as the following, for example.

Sample20221211-4aL2.zip (9.7 KB)

Regards,

hey @Anil_G
I would not recommend using IsNumeric as there is bug (I think) in vb.net where IsNumeric will give true for empty string as well.
more info here
VBA IsNumeric Returns Unexpected Results When Value Is Empty

Instead use Double.TryParse or Integer.TryParse

1 Like

im using studio x. i cannot open the file

Hi,

Sorry, can you try the following sample?

NewBlankTask20221212-1.zip (68.0 KB)

Regards,

excel that i give u is the sample. i have try to do using my data, but it shows this error. do you have any idea why is it happen?

Hi,

This error shows there is no column named LOB. Can you check your worksheet?

Regards,

image
this is my first column named LOB, u have any idea why is it become error?

Hi,

For now, can you check HasHeader option of ForEachExcelRow checked?

Regards,

Yes, i have tick has header

Hi,

Can you share your workbook?
It’s no problem if dummy data.

Regards,

Main.xaml (11.5 KB)
This is my workbook.

Hi,

I mean I’d like to check your excel workbook, if possible.

Regards,

EXAMPLETEST2.xlsx (14.0 KB)
Here is my workbook, in the excel, as u can see the first column is LOB. when i do it manually, i will choose all and sort by column A which is named as LOB. then i will delete all each row that in column A that has other than number

I’m not sure, but you use GenerateDataTable activity with UseRowHeader option in the above xaml. This means the first column of the datatable is deleted.
So, as it might be relevant, can you try to turn off it?

Regards,

U mean i have to tick is option in generate data table activity?
Uploading: image.jpg…

actually the data first i convert from text file into excel

Can you uncheck the following “UseRowHeader”?

img20221214-3

I don’t understand your whole process, however this was a little concern.

Regards,

image
i already untick but when i run it still show no column found