Clearing Data in Template before Overwrite

[pmelikian]

Hello,

I have a workflow that overwrites data within a .xlsx template. The overwrite works fine but I am having issues when the data that was previously there is larger than what it is being overwritten (see example senerio below). Is there a way to clear a data range before it gets overwritten? or possibly fill in the remaining lines with null values?

Example: Say there was 10 lines of data on a template and I run the workflow and overwrite the file with new data that is only 5 lines. I am still seeing 10 lines of data. 5 lines of new data + the bottom 5 from the previous dataset that did not get overwritten. 

Any advice is appreciated here.

thanks

Paul 

[afv2688]

Hello @pmelikian ,

What about a different approach:

• Save blank template in some place.
• Copy template to desired output path
• Overwrite data

Would this be something manageable?

Regards

[pmelikian]

thanks for the response @afv2688 !

Is it possible to have the workflow save a blank template and then later in the workflow write to that sheet?

It wouldnt be optimal to manually save the blank templates or clear them out before running the workflow because there are ~30 templates and that would need clearing and that would be too time consuming.

[jcboyle82]

This is the exact same issue Im having. If you find a solution, please post it!

I'm going to try creating some sort of dummy blank data to insert before inserting the real data, but this seems very tedious and inefficient as I have a few named ranges to fill in.

[Luke_C]

@pmelikian 

I would agree with what @afv2688  suggested:

1. Keep all the blank templates in one folder location
2. When the workflow runs, use Run Command tools to copy the templates into your output folder
3. Have the workflow output to the templates in the output folder (I usually also have a run command tool to rename them with today's date or something)

I've done this for a number of projects and it works perfectly. 

[pmelikian]

Thanks for the additional detail, Luke. I've never heard of the run command tools so I will have to do some research on how that works and try to implement this.

[jcboyle82]

This sounds like a great solution, can you post an example of how this would work? I'm still pretty new to Alteryx. Thanks!

[Luke_C · Accepted solution]

@jcboyle82 @pmelikian 

Attached is a sample of how this would work. If you open the yxzp it should make the folders for you and work as expected once you run it. You'll need to tweak the formulas to your needs, but essentially the commands are very straightforward:

1. Copy Syntax: copy 'source file path' 'destination file path'
   1. Note, you can use the * operators, so something like C:\Users\..\Templates\*.xlsx would copy all the xlsx files from that location.
2. Rename Syntax: ren 'original file path and name' 'new file name'

Hope this helps. Also worth noting you could use the 'events' menu in designer to trigger a copy .bat file before the workflow runs, which would need the .bat file to be saved somewhere on your computer. 

[dhanyaantonyp]

Can you please send workflow using alteryx 2020.3.I am not able to import this workflow since its in higher version of alteryx. I am also facing similar issues and wanted to know how Run Command works.

[Luke_C]

Hi @dhanyaantonyp 

Open the yxmd file with notepad and change the version there. It will work