How to remove old records when output to a range while the list gets short

I used "Overwrite sheet or range" option in the Output tool, while setting up the range like B:L in one of the sheets in the xlsx file. Everything goes fine until in one run I got a result list shorter than the previous and the old records still remains in the sheet.

Take an example,

in Run 1 I have records

in Run 2 I have records

But in the Run 2 Output file, I see

I dont know how to tackle this problem as I need to keep the format in the output template and use this Overwrite range option.

Output

Accepted answers

messi007

Hello,

Please see below :

Attached the workflow 🙂

You can use VBS as well to that in order to delete previous data and then insert.

Hope that helps !

Regards,

DeleteHisto.yxmd

All comments

philipmannering

Overwrite will keep all the records that aren't filled. Perhaps one solution would be (if you knew the maximum data stored) add null records to overwrite all previous data. You could do this with a Generate Rows and Join (on record position).

Alternatively you could use the Reporting Tools - set the formatting in the Table tool and use the Render tool to create the xlsx with formatting.

Interested to hear if there's a better solution though.

messi007

Hello,

Please see below :

Attached the workflow 🙂

You can use VBS as well to that in order to delete previous data and then insert.

Hope that helps !

Regards,

DeleteHisto.yxmd

JokeFun

@messi007 @PhilipMannering Thank you for your great ideas! The Generate Rows solution works.

Raj_007

Hi, This approach does not work for me since i do not read the data from the same file. I read the data from an SQL query -- tried to add an extra null record but in the excel file i still have the records that are changed. it is not deleting.. i still have duplicates in the excel file.

I used overwrite sheet or range ..specified the path with sheet name and range... i still see the old records in the excel file and duplicates..duplicates because one of the field value got change

Raj_007

Hi Messi, i followed exactly the same approach.. i added 1 extra null record .. record count + 1 for the incoming data from qry. Not sure why i still see the duplicates in the excel file

data changes but it keeps 2 exactly same records .. it is not deleting so duplicates

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703