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

[JokeFun]

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

A

B

C

in Run 2 I have records

D

E

But in the Run 2 Output file, I see

D

E

C

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.

[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 · Accepted solution]

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,

[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