community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

How to: Dynamically rename output files

Alteryx
Alteryx
Created on

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

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?

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

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?

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

 

 

Meteoroid

BINGO! This worked exactly as needed - Thank you!

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!

 

 

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.

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

 

 

 

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

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

 

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


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. 

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

 

 

 

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