Output Data - new tab with date of run

[KyleF]

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.

[rafalolbert]

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]

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]

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]

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())

[KyleF]

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 · Accepted solution]

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]

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

[KyleF]

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

[mceleavey]

Hi @KyleF ,

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

M.