I would like my output to be to an excel file, but create a new tab with a timestamp of the run as the sheet name.
I've tried a number of different ways. It seems like it needs a sheet identified, but doesn't like it when I make the name dynamic (or my coding is off). I seem to get errors unless I just have it replace the file entirely.
Solved! Go to Solution.
Hi @KyleF,
This should do the trick, formulate filename|tab using Formula Tool: "output.xlsx|" + DateTimeFormat(DateTimeNow(),"%Y_%m_%d_%H_%M_%S") and Change File/Table Name for the Output Data Tool. Example is attached.
Thanks,
Rafal
#Excuse me, do you speak Alteryx?
Hi @KyleF
This should be possible, so let's start with a basic example. Here's how I would write an Excel file with a sheet named with today's date. Here's an example Formula to create your file path string that include a sheet name of today's date:
"%temp%\TestOutput.xlsx|||"+DateTimeToday()
This will return ""%temp%\TestOutput.xlsx|||2021-03-01" and this value can be used by the Output tool as the path. I've attached an example of this in action.
Now this is just a general case, there may be other complications like if you're writing to a file that already exists or using Render tools. Let us know how this goes and we'll take it from there.
It worked the first run, but then it has an error because the "sheet already exists" if run a second time. Does DateTimeToday() only return the date or the time as well? My file location is a LONG string, so I wasn't sure if the size of my string was just too small and truncating.
Hi @KyleF,
Do you have overwrite sheet option choosen?
It works only for the date part. Soource:
https://help.alteryx.com/current/designer/datetime-functions
You can check this function to get both:
ToDateTime(DateTimeToday())
I can use the overwrite, but a question on the ToDateTime(DateTimeToday())
I tried that at 1:40pm and it seems like it returned 03-01-2021 00:00:00
It said that Excel doesn't like that format for a sheet, and it isn't the correct time.
Thanks for all of the help.
That got the time correct, but the error says it's an "invalid sheet/range specification for Excel"
It's the : I will figure out how to remove/replace it.
Hi @KyleF ,
You can use the Regex tool with the function "Replace". use [:] as the string and replace with an underscore, for example.
M.