Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Output Data - new tab with date of run

KyleF
7 - Meteor

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.

9 REPLIES 9
rafalolbert
ACE Emeritus
ACE Emeritus

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?

CharlieS
17 - Castor
17 - Castor

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. 

KyleF
7 - Meteor

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.

Emil_Kos
17 - Castor
17 - Castor

Hi @KyleF,

 

Do you have overwrite sheet option choosen?

 

Emil_Kos_0-1614631995617.png

It works only for the date part. Soource:

 

https://help.alteryx.com/current/designer/datetime-functions

 

Emil_Kos_0-1614632282065.png

 

You can check this function to get both:

 

ToDateTime(DateTimeToday())

 

KyleF
7 - Meteor

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.

mceleavey
17 - Castor
17 - Castor

You don't need the todatetime() function. If you want the datetime, use datetimenow() and if you want just the date, use datetimetoday().

 

M.



Bulien

KyleF
7 - Meteor

That got the time correct, but the error says it's an "invalid sheet/range specification for Excel" 

KyleF
7 - Meteor

It's the : I will figure out how to remove/replace it.

mceleavey
17 - Castor
17 - Castor

Hi @KyleF ,

 

You can use the Regex tool with the function "Replace". use [:] as the string and replace with an underscore, for example.

 

M.



Bulien

Labels