Overwriting existing spreadsheet when no data

[bkgators]

I have a workflow that is parsing bad data into separate tabs in an excel workbook. The workflow runs fine, however each week I need the most recent run to overwrite ALL data within the previous file. It does appear to overwrite the data, but it doesn't actually remove all the data that was there previously. If the prior report had 8 rows of data and the new report only has 3, the report shows 8 rows of data, with the first 3 being the new ones.

How do I solve this and retain formatting?

[skeen503979]

@bkgators 

You will likely need to select the "Overwrite File (Remove)" option so that a brand new file is created each time rather than attempting to edit the existing file.  The option you have selected (Overwrite Sheet or Range) will only update the sheet as far as the new data goes.  That is why you're experiencing the problem of today's 3 rows of data only updating and still seeing yesterday's 8 rows that remain unedited.  Changin your output option (pic below) will eliminate that problem for you.

May the data be ever in your favor.

[Qiu]

@bkgators 
I replicate the error in my end.

Maybe its really a bug?

[bkgators]

I do not want to recreate the file as I will lose the formatting that is in place. Why call it overwrite it if doesn't or am I doing something wrong. Overwrite the file means replace, why would it not do the same for the tab.

[skeen503979]

@bkgators If you don't want to overwrite the entire file itself and start clean each time you can try using the "Select Records" tool in the blue Preparation tab.  Input the range well at some crazy high number your data set will never exceed so the output from that tool will be blank while still preserving your column header formats.  From that blank output, you can then merge your current week's data.

Hope this helps!

[bkgators]

@skeen503979

Thank you for your solution. I also found that I can feed null values from a blank sheet into those tabs before loading. So, I guess that brings me to the next question - Is that actually how this was designed to work? Seems a bit shortsighted to now overwrite all data in the tab vs whatever rows happen to overlay. I would suggest that it remove all or at the least have something that would allow you to choose the solution you were looking for.

[skeen503979]

@bkgators  I'm not sure I understand your second question. I certainly can't speak to the intention of this tool 😄, but I think it will accomplish your purpose here.  I understand the Select Records tool to help identify a particular range of data (between rows 50 and 100, etc.) or if you only want to deal with everything before or after a certain record.  It's a good "line in the sand" tool.  What you do with that line is up to you.

[Shreyash41]

@bkgators Hello! Can you please elaborate on the idea of feeding null values to sheets.  I have been trying to find solution for this for a week now. My workflow writes on sheets based on a fixed number of records per sheet and the sheets that are not written over stay unaltered reflecting overall wrong number of records in final output.