Adding date to file name

[MRPP]

Hello Community !

I am a beginner level Alteryx user tryin to add today's date to output (.xlsx) file name. I did go through few posts but all of them, do run and produce output, but when I try to open get an error message that the file is corrupt, etc. Alternatively, suppose I have 6 files in a folder, say: File_1, File_2 .... File_6, is there a way to Alteryx would be able to add today's date as (say, for ex:) File_1 25-May-2022.xlsx etc.? Any help is welcome !

[Sebastiaandb]

Hi @MRPP ,

This should help you out :-D!

Make sure to change the paths in the input and formula tool :-).

Greetings,

Seb

[MRPP]

Thank you Seb, but the output I get is an excel with only one column with dates, maybe I am missing something. Like I said, I am still novice trying my best to grasp. Sorry, if what I missed is simple !

I did change the path to input, but wasn't sure how (or what) to change in the formula tool.

If you could add bit more detailed steps.

Thanks in advance !

[OllieClarke · Accepted solution]

Hey @MRPP 

Here's basically the same approach as @Sebastiaandb but broken down.

Firstly, when you input your file, make sure to output the full path as a field

This will add a column to your data called FileName, which will be the location of the file, its name and the sheet you're reading in

We can use the text to columns tool to split this field based on the repeated pipes which separate the file from the sheet. Change your delimiter to |, and tick the skip empty columns option.

Now we've got our sheet and file as separate fields:

I used a select tool to rename these columns, drop the original FileName and also drop the empty FileName3.

We then need to create a new Filename, but including today's date.

We can use a formula tool to do this

I first create a column called FullPath which is our FileName1 (renamed as File), but without the .xls extension (if you're working with .xlsx files then you'll need to update this formula to be as below)

Replace([File],'.xlsx',
datetimeformat(datetimetoday(),'_%d-%b-%Y'))

I use the datetimeformat() function to format the current date as _dd-mm-yyyy and replace the .xls with this

When I read in my filename, Alteryx added a $ to the end of the sheet name and wrapped it in `, so I used the ReplaceChar() function to remove these.

I can then combine my FullPath field, with its file extension followed by 3 pipes, and the cleaned sheet column to end up with a new complete fullpath field

I then drop my File and Sheet fields as I don't need them anymore before finally outputting my data.

In the output data tool, I set a temporary filelocation to write to, but ensure the extension is correct.

I then choose to overwrite Sheet as my output option, so If I run the workflow multiple times the same day, I don't get an error (you might want a different option here depending on your use case)

Most importantly, at the bottom, I tick the option to take filename from field

I choose 'Change entire file path' from the dropdown, as this lets me set the file and sheet name,

My field is FullPath (which we've just made), and I untick 'Keep field in output' as we don't want this in our output data.

And that's it :)

Hope that helps,

Ollie

[MRPP]

THANKS A TON @OllieClarke for your help. It worked like a charm. More so, for the detailed step-by-step process with images. I re-created your 'flow' by reading those steps, so yes, it was a learning experience too !