Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
There's still time to register for the Q3 ACE Panel - Spatial Edition presentation tomorrow! | Need more information about the event? Check out the post here

How to: Dynamically rename output files

Alteryx
Alteryx
Created

One of the great features of the output tool is the option to take the file or table name (or part of it) from a field. It allows you to append a suffix, prepend a prefix, change the entire file name, or the entire file path. It also gives you the option whether to keep the field on output.

 

One instance where this is especially helpful is if you have a process that runs regularly but you don't want to overwrite the output file every time. You can use the datetimetoday() function (date time functions) to find today's date and then use that to update the filename. 

 

For excel files, things work a little differently because of the filename format as filename.xslx|||SheetName.

 

The option Change File/Table Name will update the sheet name, not the file name.

 

In order to update the filename, you have to select the option Change Entire File Path. Upstream, you will have to create a field that contains the entire file path. Here is an example of a full file path that uses today's date as the filename: 

 

"C:\Users\username\Documents" + DateTimeFormat(datetimetoday(),"%Y_%m_%d") + ".xlsx|||Sheet1"

 

See the attached workflow for examples of updating an excel file and a comma delimited file with today's date. 

Attachments
Comments
5 - Atom

This is a great "How to Guide" - thank you for this HenrietteH.

 

Is there a way not to have the column with the "file path" in the created output file?

 

Regards

 

G.

6 - Meteoroid

Unfortunately, I'm on Alteryx 10.5 (with no way to update anytime soon) - is it possible to please upload one that is compatible with older versions? Or provide screenshots of your method?

7 - Meteor

@Sharbucks : You can download the version provided and open it in textpad/notepad and change the version number (see below) to the version you need and you should be able to open the workflow.

 

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="11.3">

6 - Meteoroid

Your original post was very helpful in getting me started on a solution for a project I'm working on - Thank you! However, I'm 90% of the way there and need help with the last bit.

 

The process I'm working on is a workflow that will be used more than once per day, so the output filename will need to include today's date AND time. Is there a way to get date & time added to the output filename in Excel? When I export to yxdb format the date & time are included in the filename, but I cant seem to get it to work in Excel. It errors out if I change the DateTimeToday function to DateTimeNow. Any thoughts?

7 - Meteor

@dcraft

 

This has worked for me in the formula tool: DateTimeFormat(datetimenow(),"%Y_%m_%d_%H_%M_%S") to get the date and time appended to the file name..

 

 

6 - Meteoroid

BINGO! This worked exactly as needed - Thank you!

7 - Meteor

Is it possible to perform this process on an existing excel template? I.e. output data to a specific sheet, rename the excel template, save.

Alteryx
Alteryx

Hi @RSK

 

Sounds like what you are trying to do is the opposite of what the example in the original post is about - the original post was meant for cases where you want the output tool to write a new file but without having to edit the tool in Alteryx. 

 

This post might be closer to what you are trying to do, as is this one

 

If neither one of those answer your question, try posting to our discussion board

 

Thank you!

 

 

7 - Meteor

Hi HenrietteH,

Thanks for responding. I've read through both those posts as well as many others. I've yet to find a solution/example. Really, how hard can it be to output data to a specific pre-formatted sheet in an existing XLSM template on my desktop, rename the file with a timestamp and save it? LOL. I've been doing it in MS Access, Brio,Hyperion for years.

6 - Meteoroid

Is there a way to output an Excel file with the filename from a field, as well as multi-tabbed? It seems this can't be done concurrently.

 

I am specifically trying to write a 6 tabbed Excel, to a file with the current date in the filename.

 

Thanks!

Alteryx
Alteryx

Hi @JBarry2017

 

If you select "change entire file path" as the option in the output tool, you can change any part of the file name and multiple parts of it at the same time. 

 

E.g. this workflow reads in the CO Store Files from our sample data (C:\Program Files\Alteryx\Samples\en\SampleData\CO Store File*.yxdb) and then creates three output files with two tabs each. The files are based on "Region", the tabs are based on "Type": 

1-29-2018 4-27-02 PM.png

 

 

 

7 - Meteor

 hi @HenrietteH,

I build a workflow where i have only one file .xlsx with more sheet. I want to rename the name of the output file by a specif column at the same time I want to fix the sheet.

i choosed in the output tool the flag to  "take file/table name from field" and choosed the option "change entire file path".flussi gallery.PNG

about formula tool i use this kind of solution, but when i run the work flow in the gallery give me an error that it couldn't able to acces

formula.PNG

5 - Atom

@HenrietteH
 

Thank you for the information you posted above on - ‎01-29-2018 01:28 PM

 
 
Can you please show the Configuration detail for the file output in this example? Thanks!
 
 
 
 
2018-03-27_13-19-15.jpg
7 - Meteor

Hi HenrietteH,

I chose this kind of solution, select formula tool (yellow arrow) --> formula tool  configuration --> select column, chose "add column" eith the namen "Filename" like you--> and insert this code in the canvas of configuration tool  "Distributed File System path"  + [the name of file] + DateTimeToday() + '.xlsx|||'the name of sheet'.

  1.  "Distributed File System path"  is the name of dfs path, if we need to allows access to files from multiple hosts
  2.   [the name of file] , in this case we have to specify the name of file and its relative sheets
  3.   DateTimeToday() , if you need to schedule your app, it's a good idea to add the date of run
  4.    '.xlsx|||'the name of sheet'.  when you finish to add the information, that you wanto to se in the name of file, you have to insert the name of sheet.

Between the formula tool and the output tool , i use the "Block until Done" if you need more than one sheet

 

The "output data" tool configurations give us the choice to  "take file/table name from field" and after put the flag I choise the the option "change entire file path" and write "Filename" the new column generates with the formula tool. Remember to un flag the option "keep field in output", if you don't want to see the filename column in the output file.

 

I think all explanations are done , see you soon

 

5 - Atom

^^ Thanks!

In the end, I created a Datetime field in a formula step, and then in the final output used the configuration shown below. What i get is a new tab created in the same file each time it runs, with the tab name of Date_ and then today's date and time. It's perfect for what I need. 

2018-03-28_9-42-15.jpg


6 - Meteoroid

Hi HenrietteH,

 

I have a workflow that I need to run every month and I need it to save as a new file with the date of the run each time. This solution seems to work, however, it is only saving one tab from the workbook and I need it to save all of them. Can you tell me how to accomplish this?

 

Thanks!

Alteryx
Alteryx

Hi @rachcamp7

The example I use has a static sheet name "Sheet1":

"C:\Users\username\Documents" + DateTimeFormat(datetimetoday(),"%Y_%m_%d") + ".xlsx|||Sheet1"

 

In your example, you would have to create different sheet names dynamically by using the sheetname in a column: 

"C:\Users\username\Documents" + DateTimeFormat(datetimetoday(),"%Y_%m_%d") + ".xlsx|||"+[SheetNameColumn]

 

[SheetNameColumn] would contain the name of the sheet you want the record to go to. 

8 - Asteroid

Solution I came up with lately:

 

Using directory tool I made two data streams:

 

First reads the actual file using Dynamic Input.

Second extracts 'FileName' attribute from directory and gets renamed with formula tool.

 

Then 'append fields' tools adds additional attribute 'FileName' which inserts file name to every row.

 

Output tool renames the file based on 'FileName' attribute and drops is from final results.

 

Capture.PNG

 

 

 

5 - Atom

Is there a way to remove the underscore or dash from the time on a file name? I know a colon can't be used. Can it be blank in between the hour and minute, or even not have a space at all?  Not a matter of life death, just aesthetics and my OCD asking.

 

This is how it currently looks:

2019-05-29-16_18.xlsx

 

I would like either:

2019-05-29-1618.xlsx

 

or:

2019-05-29-16 18.xlsx

Alteryx
Alteryx

Hi @channo01 

 

That's a good point! I don't like the underscore either. 

 

You can do that as part of a datetimeformat() statement, e.g.: datetimeformat(datetimenow(),"%Y_%m_%d %H%M") will return: 2019_05_29 1735

 

You can find a list of all specifiers available in the help documentation under Functions

 

 

8 - Asteroid

Hi @HenrietteH, thanks for providing this example. Is it possible to not include the "FileName" field in the final dataset?

 

Thanks

Alteryx
Alteryx

Hi @akasubi 

 

There is an option to not include the FileName field in the output tool:

 

2019-10-07_13-10-44.png

 

6 - Meteoroid

Same question/need as @jjonesjr 

 

Have an excel "template" file where Alteryx workflow will update only one of the many worksheets in the file. Is there a way to rename the file while keeping all the other worksheets (that Alteryx isn't updating or creating) in place?

5 - Atom

Yes @nickbecks and @jjonesjr !  I have this same ask.  Almost like an overwrite sheet and then update filename.

7 - Meteor

Hi I'm trying to update a WF so that it automatically creates a new Excel file with the run date of the file say Inventory_IO 2020.06.24, and when it runs tomorrow the date update to 25, and so on.

But I can't get it to work, I have to save it to a server (shared drive) Folder location on our (L Drive is)

\\DUB-AV-FS01\Production$\16.  Industrial Technologies\16.16 Distribution Operations\12. MTS Stockouts & Future Stockouts but I'm getting the attached error can someone please explain what I should do? I need the server path to run a tableau query later in the process. TIA Folks

Capture.PNG

Alteryx
Alteryx

Hi @Karl_Spratt 

It looks like you might be missing a "+" in between your file name and [Today]

 

HenrietteH_0-1593026068908.png

 

5 - Atom

Does anyone have anything similar where they are creating a dynamic .xlsx file with today's date, and emailing the file?  I'm not seeing a way to use the dynamic file once it's created.

 

Thanks!

6 - Meteoroid

Hi @Karl_Spratt 

 

Try dynamically creating the file name in a formula tool, then Render tool and 'Group Data Into Separate Reports' Group on your filename you created, and modify Filename by replacing Entire Path.

 

Then off the output of the formula tool have a Summarize with First filename field, and apply that as attachment in the following email tool.

5 - Atom

To apply the dynamic file name to an existing excel template I was able to do the following:

 

Pick up my excel template using the Blob Input, create a new unique URL/filepath with a formula tool (including DateTimeNow & Username) to then copy the template to using Blob Output.

 

I then added a Block Until Done before the Blob Output, to ensure the new renamed template was ready to output to, and appended the new URL field to my output data.  This URL field, with the addition of sheet name and output range in another formula tool, can then be used in the Output Data tool to 'Change Entire File Path' and 'Overwrite Sheet or Range' while 'Preserving Formatting on Overwrite'.

5 - Atom

Hi @HenrietteH 

 

I was using datetimenow instead of datetimeformat, because I need a versioning if I run it multiple times a day.

This works fine; expect when the workflow takes longer (more than 1 minute), the output is split into multiple files.

 

I.e. I get a file XXXXX 200910 1152.xlsx and XXXXX 200910 1153.xlsx 

I checked the sum of rows; they are not the same (therefore split output). Also, the total of rows adds up what the metadata in Alteryx shows for the total number of rows.

 

Is there a way to prevent that or do you have another idea how to add versioning to the xlsx filename? I don't necessarily need the hours, minutes, and seconds, if it could be YYYY MM DD followed by V1, V2 etc that would be great. However, I don't know how to implement the latter.

Hi @tracyh Could you help share a demo of your workflow? I'm also trying to figure exactly this problem. I'd be very appreciate if I'm able to see the workflow and each tool's configuration.

 

5 - Atom

Hi @thuyduongnguyen,

 

Below is the section of the workflow which allows you to select the template file using Blob Input (only input required is File Name), produce a field to hold the new file path within the Formula tool (named Output_Path in this case) and configure the Blob Output (as below) to use the new Output_Path to Replace Entire Path With Field.

Blob Input Output.jpg

 

Further along the workflow, the Output_Path field can be added to the data as a new field using 'Append Fields'. Here I have used the Formula tool to add a specific sheet name and range to the 3 different flows of data that I want to output to 3 separate worksheets in my Excel workbook.

 

OutputFilePath.jpg

 

When it's then fed into the Output Data tool, you can use the new OutputFilePath field in the 'Take File Name from Field' option, as below.

 

OutputFilePath2.jpg

 

Hope this helps.

Hi @tracyh, thank you so much for your reply. I have built the workflow based on your suggestion.

My goal: TodayDate_Report_Location.xlxs (e.g.: 2020_09_22_Report_HCM.xlxs)

 

Below is snippet of my workflow:

Blob Input: I choose the file path of output template in File Name

Output Path formula: I add dynamic content (datetimeformat) and some fixed text ("Report")

Blob Output:  exactly same as your setting 

thuyduongnguyen_4-1600774238045.png

I have a whole workflow processing the input data before coming to Filter of [Location].

Then I need to use these [Location] as a part of the report name, so I put them in Output_File_Path ($A2:DV9999 is the range of formating I'd like to keep)

 

thuyduongnguyen_5-1600774238050.pngthuyduongnguyen_6-1600774238061.png

 

Output Data: I can’t leave “Write to File or Database” blank cause it will run into error.

So I just choose the template output together with the range. Is it ok?

thuyduongnguyen_7-1600774238069.png

I think it is really close now to reach the goal.

I’d really appreciate if you can take a look and point out my mistakes here.

Thank you very much and hope to hear from you soon.

5 - Atom

Hi again @thuyduongnguyen,

 

I think the only thing you need to change here is to introduce the [Location] part of your file name in before the Blob Output. Otherwise you are creating the template file using [Output_Path] and then trying to populate [Output_File_Path] + [Location].xls

 

The only thing I changed in the second formula tool was to add the Sheet name and range.

 

Did you confirm that the template was copied to [Output_Path] by the Blob Output? You may also need to add the .xls file extension to [Output_Path] here if not.

 

As for the “Write to File or Database”, I think you can enter anything as it completely overwrites. I populated mine with the target folder location but no file name and it worked fine.

 

You seem to be very close!