Generate output with multiple sheets with datetime stamp on each run

[Sasthana25]

Hi All, 

I have few workflows with two or more outputs to be generated in different sheets of a file. 

The logic and workflow is working fine however, with each run I have to go deleted the file from the output folder and then run it again (as it throws sheet already exist error)

In order to make it more dynamic for client I want to have a logic where new output is generated every time with date time stamp - without someone deleting the previous from the folder. Attached is the screenshot for reference. Any suggestion is much appreciated. 

Thanks,
Swati 

[Christina_H · Accepted solution]

There's an option in the output tool to use a field as the file name.  If you want to create a whole new Excel file every time, use the Change Entire File Path option since I think File/Table Name refers to the sheet name in this case.  Simply generate your whole file path in a formula tool, incorporating the date in the name.

Alternatively, use the option higher up to Overwrite Sheet or Range.  This won't do anything involving time stamps, but will allow Alteryx to overwrite the existing sheets rather than erroring.

[messi007]

@Sasthana25,

You can use a formula tool to prepare the path with the date time now and then use it as an output.

Attached the workflow:

Attached the workflow,

Regards

[Sasthana25]

Thanks for your swift response @Christina_H 
Do you have any example to show where you suggested to Simply generate your whole file path in a formula tool, incorporating the date in the name.

Thanks in advance. 

Cheers!!!

[AkimasaKajitani]

Hi @Sasthana25 

You can use DateTimeNow tool and "Change Entire File Path" option at Output Data tool.

The logic is that make the file path from the current time by DateTimeNow tool and change the file path of Output Data tool using "Change Entire File Path" option.

This is the expression to make the output file path.

"c:\test"+DateTimeFormat([DateTimeNow],"_%Y%m%d_%H%M%S")+".xlsx|||Sheet1"

This is "Change Entire File Path" option sample.

Check "Take File/Table Name From Field"

Set "Change Entire File Path"

Field Containin... option is set the File Path field.

Not Check "Keep Field in Output"

Please check the uploaded workflow about the detail of workflow 

[Christina_H]

@Sasthana25 I think the other responses have you covered for this, but here's my version:

[Engine.WorkflowDirectory]+"Output file "+ToString(DateTimeToday())+".xlsx|||Sheet1"

This will create a new file in the same folder as the workflow, called Output file YYYY-MM-DD.xlsx and the data will be on Sheet1.

[Sasthana25]

Thank you so much for the solution @Christina_H . I tried this and it worked. However, two separate files is getting generated and the use case demands two outputs in two different sheets within the same file. 

Would appreciate if that can be resolved too. 

Thanks,
Swati

[Christina_H]

@Sasthana25 Two different sheets from two different output tools? That's just a case of making sure the file names are the same for both and with different sheet names. You might also need a Block Until Done so that they don't both try to access the file at the same time.