How to filter CSV and delete the data

Help Activities
, ,
1

I have to filter .csv column and then have to delete some data and unfilter it.
image
I will get the different numbers every time. (1,2,3 or 40,41,42 Likewise)
I will have to keep the data for last number and have to delete the data by filtering first two 22,23.
FYI- Columns are sorted before this step in ascending order so, each time need to pick the last number which is largest among the three.
Can someone help me here ?

2

Hi @PALKUMARI_PATEL
Below are the steps with the example data as
image

You should read the CSV first with Tab as delimiter which will return you a Datatable

image
image1492Ă—430 27.5 KB

And then Try finding the largest number from the column by below linq query
InputDT.AsEnumerable().Max(Function(row) cint(row(“Marks”)))

image
image1807Ă—478 31.3 KB

Finally you can filter the datable with the largest value which you got using

InputDT.AsEnumerable().Where(Function(x) x(Cint(“Marks”)).Equals(MaxValue)).CopyToDataTable()

which gives you the datable containing only with the required largest value.

image
image1573Ă—387 22.9 KB

Cheers,
Happy Automation

3

Thanks for the detailed explanation but I want to delete the data for other two numbers and then have to keep it only largest. Can you explain that delete code of other two number rows ?

Thanks again in advanced.

4

@PALKUMARI_PATEL

As you say already it is sorted…you can use this

say the data is read into datatable dt then

dt.AsEnumerable.GroupBy(function(x) x("NumberColumn").ToString).Last.CopyToDataTable

after this just write the datatable back to csv

cheers

5

Thanks @Anil_G I don’t know if this will return largest number data and copy the same into that file. Can you explain more ?

6

@PALKUMARI_PATEL

As the data is already sorted…I am taking the group by of the column containing numbers and then getting the last group only…and leaving all other groups…as its the last group the data that is retained would be the highest number

And writing only that last group to cav so that all other data is removed

Cheers

7

Ok, I tried this and it executed without error but when I open that csv it says you can not open the file. Nothing else coming up.
image

8

@PALKUMARI_PATEL

How did you execute?

And did you use write csv to write the data back?

Cheers

9

I use read csv then assign activity and providing the same string as you said and write csv.

10

@PALKUMARI_PATEL

  1. Read csv to dt
  2. Assign with dt = above formula
  3. Write csv dt again

Cheers

11

I am doing it same way. I don’t know what is the error.

12

@PALKUMARI_PATEL

Did you give the extension also in write csv?

Cheers

13

@PALKUMARI_PATEL
Once you get the datatable with the required data, you can write it back to CSV using “Write CSV” activity

image
image1509Ă—609 32.3 KB

14

Yes, @Anil_G I am giving extension also.

15

@PALKUMARI_PATEL

Just to check can you give any dummy name and see if you are getting the same issue, instead of rewriting

cheers

16

You want me to paste the data in any other file with some other name ?

17

@PALKUMARI_PATEL

Yes …so that we can check if the values are coming are not

Cheers

1 Like
18

So, IF I write data into other file, It is working and allow to open the file. But It it printing the shortest number as I am sorting in descending order. (My apologies I said Ascending). Can you help me to get the largest number among 3 ?

It is showing in ascending order when I open the filter but actually data is sorted in descending.

19

@PALKUMARI_PATEL

Instead of last use first

dt.AsEnumerable.GroupBy(function(x) x("NumberColumn").ToString).First.CopyToDataTable

cheers

1 Like
20

Thanks @Anil_G But anything you can suggest me if I want to write the data in same file and I can open it?

1 Like