I am writing data from a database to a output file in .xlsx format. The data is run each day and gets added to individual tab with date name (<Filename>.<Today's Date>). For this I have used formula tool and inserted DateTimeFormat(DateTimeToday(),"%Y%m%d"). This is saved under the .xlsx file. So if everyday file is run, the tab will show multiple sheets with different dates.
However, I want to convert this individual sheet to a separate file that would have no .xlsx format.
Is there any way to do it?
It is not mandatory to have the output file only in .xlsx format. I need the file name as <Filename>.<Today's Date>.
Your response would be appreciated!
Thanks.
Solved! Go to Solution.
Hi @kmoon,
For most output file types, you can specify the filename as one of the fields, towards the bottom of the Config Panel of the Output Tool.
So, for instance, use the formula tool to calculate the desired filename, say, as [calculatedFilename], and then do...
You can do this for .xlsx, .csv, .flat, etc...
Hope that helps!
John
Hi @JohnJPS,
I did it the similar way you mentioned.
For excel, if I save in .xlsx format, I would get everyday data in separate tab like <FileName><Todays Date>.
However, I would like to see each tab converting to a separate file so that they are visible from the folder itself and not by going to .xlsx and retrieving them.
Any thoughts?
Thanks.
To illustrate, I've attached an example that generates some random data and outputs it to both a single file with 31 tabs which is I believe what you're already doing, and also 31 individual files, which is I believe what you're looking to do. The two approaches are so similar that I'm not entirely sure what might be missing in what you've already tried, so hopefully this example will contain clues to get you pointed in the right direction for your actual data.
Hi @JohnJPS,
I appreciate your expedite feedback. The output file is shown as <RandDate.xlsx>. I want to get rid of the .xlsx extension and want to show the file name as <RandDate> only.
Any thoughts?
Thanks.
In the Formula tool, for the filename, I used:
"C:\dev\Alteryx\tmp\out\" +
DateTimeFormat([randDate],"%Y%m%d") +
".xlsx|||" +
DateTimeFormat([randDate],"%Y%m%d")
... to eliminate the .xlsx, just take it out (e.g. from the 3rd line):
"C:\dev\Alteryx\tmp\out\" +
DateTimeFormat([randDate],"%Y%m%d") +
"|||" +
DateTimeFormat([randDate],"%Y%m%d")
... please note that the sheet name will still be named as a date... if you just want "Sheet1" there, make the corresponding modification:
"C:\dev\Alteryx\tmp\out\" +
DateTimeFormat([randDate],"%Y%m%d") +
"|||Sheet1"
... hopefully this gives you the insight to name your output file pretty much anything you want.
This worked for me. Removing the .xlsx did the trick. Now I don't see the .xlsx extension.
Appreciate it.
Hi @JohnJPS ,
With respect to what you mentioned before, could you also let me know how do I get rid of the header in final output?
Would appreciate your response.
Thanks.
Hi @kmoon,
Sure, for most output types, look in the Output tool's config panel for "First Row Contains Field Names"
Just un-check that to get rid of the headers in the output file.
Hope that helps!
- John
Hi @JohnJPS
It works for .csv output file but not .xlsx file. The option is available only for .csv.
Curious if Alteryx could apply this for all the output file.
However, it solves my purpose.
Thanks.