Look the max date in a dt and delete it

Help
1

Hey, i have a DataTable extracted from an excel file with some dates, and i need to get the oldest and delete from the excel.
how can i get the oldest?
i tried with some posts in forum but didnt work, i was thinking to set the first value of the column date and compare with the following, but i couldnt set the first value of the column
if u need, i can attach my workflw
thanks!

2

hello @Ignacio_Smail
is there any column like date in data table?
please attach workflow.

Happy Automation

1 Like
3

Hey, tks
this is my workflow, its empty of activities to look dates
image
and this is the excel file, the column name is “Fecha de nacimiento”.
image

4

Hi @Ignacio_Smail

Use the Sort Data Table and sort it in ascending order using your date column.

Then get the date of the first row which is the oldest.

Use the Filter data table activity and filter the records using the date you captured. Here there is a option to remove or keep the data. You can select it accordingly

Hope it helps

1 Like
5

and how can i select the first row of the datatable?
i need to delete it from the excel file

6

you can delete first row from datatable dt_MonthlyReport.Rows.RemoveAt(0)

if you want delete from excel. as suggest @Lahiru.Fernando use filter data table activity filter data during processing excel to datatable

Happy Automation

3 Likes
7

i cant found it clear.
to filter data and remove from excel, i dont know how to use the filter to select the first row (or second, bcs i have headers) to keep the other files.
and then, if i get to filter, how can i remove from excel? or u r saying to write range from that datatble filtered to excel?

8

Hi @Ignacio_Smail

Just to make it simple, here are the steps again in simplified format :slight_smile:

  1. Use Sort Data Table activity to sort your data based on the date in ascending order. So the oldest date will be the first row right?

  2. Now, we can remove the first row from the data table using the below command.
    dt_MonthlyReport = dt_MonthlyReport.Rows.RemoveAt(0)
    You can do this through an Assign activity

So now, you have the oldest record deleted and the rest is remaining here in the datatable

  1. So if you want to update your excel with the latest data, you can simply do a Write Range to write the data to the excel sheet.

Is that clear bro?

5 Likes
9

Before write range, you need to delete the old range. This is because the new range is one row smaller than the previous, so you would remain with one of the old records if you just overwrite.

2 Likes
10

Hi @Ignacio_Smail
As @Lahiru.Fernando Says you can sort the datatable using ascending order. after that just use a Remove Data Row activity to remove the Data row. You have to just provide the index of the Row.
Thanks & Regards

1 Like
11

right, i couldnt remove all data to rewrite my excel.
also, remember that i have my rows coloured, and now i need to remove one row.
so, i remove the oldest row but i need to keep it coloured
maybe, i can remove the oldest row and then colur it?
let me now how to remove all the data
tks

12

If they are colored with conditional formatting, you will have the correct colors after update. If not, I do not know how you can keep the colors…

2 Likes
13

no, no conditional formatting, just format with “Set range color” activity, so i need to manipulate the excel file, no the datatable, bcz the datatable fills my excel with blank rows.

14

amazing, i did it, and it’s working… but lets have an overview
i have an excel with 10 rows and 3 columns (“Name”“date of birth”“Gender”).
first of all, i coloured with different colours the males and females.
then, doing what u said, i remove the oldest, but doing throw datatable, and when i need to rewrite the excel file, first, i cant remove the leftover row, because dont forget that now, i have 1 row less. and then, i need to keep the coloured rows, so neither of the two are working how i need.
was clear?

15

What are you doing exactly? If this is all you want to do, just do it with a macro. You don’t need UiPath for this process.

16

Hi, i have two column effective date and termination date. I want to delete the rows in effective date column with a specific date and termination date is also same. Please let me know how i can implement this in UIPATH.

17

@pramod1

Welcome to the UIpath Community.

  1. Read the data from Excel using Read Range Activity and will give you output as dataTable.
  2. Then apply select query on that DataTable.

dataTableName.select("[EffectiveDate] <> ‘01/06/2019’ AND [TerminationDate] <> ‘01/06/2019’ ").CopyToDataTable

It will give you required dataTable and then use Write Range Activity to write into Excel file.