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

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.

Developer

Output

Tips and Tricks

Accepted answers

JohnJPS

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.

JohnJPS

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

All comments

JohnJPS

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

kmoon

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.

JohnJPS

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.

SheetNameFileName.yxmd

kmoon

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.

JohnJPS

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.

kmoon

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.

JohnJPS

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280