Overwrite with new data issue

I use a workflow to produce Excel outputs for my team but I am finding a reoccurring issue. Let's say I have 10 rows of data in my existing sheet, and a new run produces 2 rows of data. In my sheet, the top two rows are replaced to be current but Alteryx leaves the previous old data in there. Is there a way to fix this without clearing my sheet every time? Thanks.

Help

Accepted answers

All comments

atcodedog05

Hi @ablack4

This might happen when you are writing to range not only to sheet. Not writing to range might fix the issue.

mceleavey

Hi @ablack4 ,

What is it you're trying to achieve?

This isn't a fix, it's just how things work, so let us know what you need to happen and we can help.

Luke_C

@ablack4

Sounds like you might want to write to a template file, that way each time you run the workflow it's outputting to a new file rather than trying to constantly overwrite the same file.

ablack4

Hi @atcodedog05 , thanks for the reply.

Yes I am writing to a range, but that's to preserve the header format. Is there anyway to write to a sheet but leave the formatting?

ablack4

Hi @mceleavey , thanks for the reply.

I would like my output sheet to only have the data that was produced from the current workflow run. As of now, I have new data being spit out on top of previous data. This is cumbersome because then I have to clear the sheet contents every time before I run the workflow.

ablack4

Hi @Luke_C , thanks for the reply.

I would rather not do this because this process gets run often and it would be annoying to have a new output every time, plus if I did that I wouldn't have the same formatting I'm trying to preserve.

apathetichell

Can we assume that there are other sheets which are static and not being written to? If not - would it be possible to bring the header into Alteryx and then output the file anew each time via render - that way you keep the formatting constant?

Can you also post a screengrab of the config or your ouptut data tool?

ablack4

Hi @apathetichell

Yes, there are not other sheets. I've never used the render tool - would that work for an Excel file?

Here us my output data config. It's currently being written to a sheet with a range.

apathetichell

if there are no other sheets - read in your original header and format it as a separate table. format your data as a table. union the two and render. You can create whatever format you need in the table tool but you will overwrite the file entirely everytime you run it..

so yes - render works find on excel, preservers formatting and renders as the proper field (ie general vs string). It allows you a solid amount of formatting options - but it overwrites only. it will not modify an existing sheet or range.

gabrielvilella

There are two ways to to make sure you erase all preexisting data when using the preserve formatting option:

Use a range that is long enough to cover any data that will ever coming in, as @Watermark suggested.
Read in the existing file and count the records, then use this to update the range on the file name.

Watermark

The variable of course comes from how much variance is there each time in the file... does it change by 10 or 20 rows or by 1000 to 2000. There is always more than 1 ways to skin a cat, and the 'best' way usually depends on knowing the exact devil in the details to be able to really dial it in.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703