How to rename a template in excel after copying and inserting the data

I have a template in excel file. I have designed a workflow to copy the template file from a folder to output folder in order to preserve the template using a batch command and then inserted all the data that I require into the template. But I would like the template to be renamed after the data has been inserted with the date the workflow has been run. For example: Output_yyyy-mm-dd.

Any help will be appreciated.

Output

Date Time

Accepted answers

All comments

Qiu

@Nithinpanicker
Can you just rename the file during the copy process?😁

DataNath

Hey @Nithinpanicker, once you know where you'll be saving the file, you can use the Formula tool to create a custom filepath with today's date added as so:

In your Output Data tool, you can then bring in this full path to use as your save location, by selecting the following:

Custom filepath.png

When you then run your flow, you'll get the following:

I have attached my dummy workflow. Hope this helps but let us know if not so we can assist you further!

Custom filepath today's date.yxmd

grazitti_sapna

Hi @Nithinpanicker , you can try this.

I hope this helps!

Thanks!

Nithinpanicker_workflow.yxmd

Nithinpanicker

Hi @Qiu,

I have tried this with success, but when I need to merge my data to the template, I do not know how to configure alteryx to output to the file with today's date.

Nithinpanicker

Attaching a screenshot of my current workflow. The top workflow copies the template to the output directory. The middle, merges data into the template and the bottom finally renames the output file.

The issue I currently have while running this workflow is that, when I run this the first time, the top and middle workflows run fine and the rename command gives me an error i.e, it copies the template and pastes it in my output folder and inserts the data into the file. But the filename renames unchanged.

On a rerun of the workflow again without doing anything else, it renames the file.

I do not understand why I have to run this workflow twice to get the filename renamed.

Thanks for your help.

CopyRename Template.PNG

DataNath

Hey @Nithinpanicker if I'm looking over this correctly, I don't think you actually need the bottom stream for renaming your file. You could just do this in the middle stream by using the methods outlined below and simply appending the date to the filename. There won't be any risk of overwriting the template as the final output file name will be unique to today's date.

Nithinpanicker

Hi @DataNath,

Thank you for your response.

Could you please tell me what I need to do append the date to the filename. When I try appending the date, it only renames the sheet name and not the file name itself.

Thanks

DataNath

No problem @Nithinpanicker.

See my solution above with the dummy workflow attached. All you'll want to change in your case when creating the custom filepath is swapping out where my directory/path is for your own. I just used 'Output_' as well so you may want to change that. When it comes to the Output Data tool, make sure you change the entire filepath, using this newly created filepath - the configuration is shown above in my screenshot!

Nithinpanicker

Hi @DataNath,

I tried doing this, but now it does not seem to be writing to the template anymore. The top stream copies the template. The middle stream just renames the excel data now

DataNath

@Nithinpanicker if you select to 'Preserve formatting on Overwrite' in the Output Data tool configuration, then select the range (this will just be all the cells your template covers), does this remedy the issue?

Nithinpanicker

@DataNath

Unfortunately that did not work. Now while running the workflow, I have 2 outputs. The template which is copied from the template to output folder and the data set which is renamed with the data but not inserted into the template.

DataNath

If you change the 'Output Options' to 'Overwrite Sheet (Drop)' does that solve the issue? Still keeping all other config the same. Apologies, I'm no longer at my laptop with an Alteryx install so can't play around myself, though I'm fairly sure it's just an issue in the Output configuration.

DavidP

Hi @Nithinpanicker

I think what you should do is when you copy the template with the Run Command tool, insert the date stamp in the DOS command so that the new file is created with the date stamp already in the filename. Then when you're writing data to the new file, you just have to make sure that the correct filename is used.

This post shows an example of how to do this.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Editing-and-saving-a-macro-enabled-file-dynamically/m-p/950195#M233720

Nithinpanicker

@DataNath

No worries. No it still does not seem to be working. Its still not merging the data onto the template. The file renaming appears to be working but its not entering on to the template. Do you think maybe I can setup an event to run a batch file to rename the file after running the workflow?

DataNath

@Nithinpanicker I've just been looking at the screenshot of your current flow again and had a thought.

For the bottom (3rd) stream, could you not just:

- Dynamically input this most recently copied template file (where the data has been populated)

- Create the custom file path (with date appended to name) as outlined in previous posts

- Instantly output the file again, configuring the output to overwrite this file

In theory, this should just bring in the file from your middle stream (which you mention is working fine) and then instantly output it again, applying the rename from your custom formula.

grazitti_sapna

@Nithinpanicker, this can be achieved using a blob tool as well instead of a batch command, if you have an existing template then you can create a new file output_2022-06-15 and then store the data in that file where the date would be dynamic by using DateTimeNow().

In the template copier tool container, I used the path of the template file to be copied and stored in a blob then using the formula tool made changes to the file name and then output the template using blob output.

This is my template

Output Template

This actually worked for me, if it will be possible for you to share a replica of your workflow containing dummy data I can try to implement the same.

I hope this helps!

Thanks!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703