Output Data - new tab with date of run
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Output
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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())
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That got the time correct, but the error says it's an "invalid sheet/range specification for Excel"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It's the : I will figure out how to remove/replace it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @KyleF ,
You can use the Regex tool with the function "Replace". use [:] as the string and replace with an underscore, for example.
M.
