Make an Excel contain only the newest 500 rows

[Jizh]

Hi, I have a process that writes log entries into an Excel sheet. With each execution the robot writes 5-100 rows. How could I make that sheet to only contain the last 500 rows? Thank you

[Lahiru.Fernando]

Hi @Jizh

So while writing are you reading the excel for anything?

1. Do you want to have the last 500 including the records that you are inserting or do you want to have the last 500 before inserting the records?

Depending on the above where you add the following tasks may vary…

To get the last 500 records.

1. Read the excel into a data table using read range
2. Use a sort data table activity to sort the records in descending order. May be you can use a date or a row ID which is available.
3. Then use a for each row activity with a count of 500 row limit and do a write range to excel

[KarthikByggari]

1. Read data into a data table.
2. get the count of data table.
3. if count > 500
   skipRows = count - 500
   newdt = myDataTable.AsEnumerable().Skip(skipRows).Take(500).CopyToDataTable

Skip - Will skip the unwanted rows
Take - Will take the next 500 rows

4. Save the newdt to a new excel file using write Range.

Regards,
Karthik Byggari

[Jizh]

@KarthikByggari So it will skip starting from the top of the table, right?

[KarthikByggari]

Yes @Jizh