Alteryx Designer

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

Convert multiple excel tabs from the same excel to individual file with no .xlsx extension

Highlighted
8 - Asteroid

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.

 

 

Highlighted
ACE Emeritus
ACE Emeritus

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...

image.png

You can do this for .xlsx, .csv, .flat, etc...

 

Hope that helps!

John

Highlighted
8 - Asteroid

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. 

Highlighted
ACE Emeritus
ACE Emeritus

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.

Highlighted
8 - Asteroid

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.

Highlighted
ACE Emeritus
ACE Emeritus

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.

 

Highlighted
8 - Asteroid

This worked for me. Removing the .xlsx did the trick. Now I don't see the .xlsx extension.

 

Appreciate it.

Highlighted
8 - Asteroid

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.

Highlighted
ACE Emeritus
ACE Emeritus

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

Highlighted
8 - Asteroid

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.

Labels