Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Clearing Data in Template before Overwrite

pmelikian
7 - Meteor

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 

22 REPLIES 22
afv2688
16 - Nebula
16 - Nebula

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
7 - Meteor

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
5 - Atom

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
17 - Castor

@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
7 - Meteor

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
5 - Atom

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
17 - Castor

@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. 

 

Luke_C_0-1616620170228.png

 

dhanyaantonyp
5 - Atom

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
17 - Castor

Hi @dhanyaantonyp 

 

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

Luke_C_0-1639145108674.png

 

Labels