Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Discussions

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

Output Data - new tab with date of run

KyleF
6 - Meteoroid

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

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

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

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

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

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

 

M.

KyleF
6 - Meteoroid

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

KyleF
6 - Meteoroid

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

mceleavey
16 - Nebula

Hi @KyleF ,

 

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

 

M.

Labels