Appending Data to Excel, then Deleting Rows to Start Over w/out Creating New Sheet

[RifferX]

Server Job: Runs nightly (weeknights) script and appends to excel in a network folder. Each month, I need to use the file and start a new one, as they are aging tickets for the month. This is my first month of completion using Server and I know in the past, when deleting rows manually from existing sheet, Alteryx either appends rows at bottom of deleted data or won't append at all. Creating a dummy sheet with same name in same location doesn't work either without first creating a new sheet in the workflow. 

My question is what alternatives do I have without manually changing the workflow each month to "Create New Sheet" on first day of month and then changing back to "Append to Existing Sheet" the following day?

This has got to be a common issue but I am not finding any viable solutions or perhaps there aren't any? My goal is to only have to do one thing manually each month, and that is taking the excel file and Saving As to my hard drive to use in further processes. I do not want to have to change the script each month for two days. I would also delete the data rows in the sheet and leave just the column headers. Hope this makes sense.......

--Russ

[fmvizcaino · Accepted solution]

Hi @RifferX ,

One idea is for you to always concatenate your daily data with your current month data and to overwrite your sheet everytime as image below. Also, if you want to keep your header intact, you can use the Range option and to overwrite everything below the second line. (New feature from 2020.1 version)

Best,

Fernando Vizcaino

[RifferX]

@fmvizcaino Thanks

I think I get where you are coming from and am going to work on putting something together tomorrow. I will let you know how it goes.

--Russ

[RifferX]

@fmvizcaino I think my solution is to just leave it go and identify current month in my processes later. We average around 150 new rows daily, which is around 3150 a month, or 37800 a year. That isn't a ton of data, although I do have another excel tab that basically copies the data and adds a networkdays formula (since Tableau and Alteryx both fail miserably with that function), but it still wouldn't be some enormous file and I would also have nice retro data all in one place. 

So essentially, your idea wasn't necessarily a solution, but the logic would theoretically work. Thanks again!

--Russ