How to delete rows excel that contain unwanted data

Help StudioX
,
1

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)

2

@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

3

Hi,

Another approach:

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

image
image815×1142 33.5 KB

Sample20221211-4aL2.zip (9.7 KB)

Regards,

4

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
5

im using studio x. i cannot open the file

6

Hi,

Sorry, can you try the following sample?

image
image1011×2575 152 KB

NewBlankTask20221212-1.zip (68.0 KB)

Regards,

7

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?

Screenshot_1
Screenshot_11149×387 14.3 KB

8

Hi,

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

Regards,

9

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

10

Hi,

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

Regards,

11

Yes, i have tick has header

12

Hi,

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

Regards,

13

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

14

Hi,

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

Regards,

15

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

16

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,

17

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

18

actually the data first i convert from text file into excel

19

Can you uncheck the following “UseRowHeader”?

img20221214-3

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

Regards,

20

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