Output to Excel spreadsheet and change tab names

Hi all,

This is a great community! Already answering many of my questions

What I'm trying to do is the below -

I want to output my data into an Excel spreadsheet with multiple tabs. The tab name will be coming from a field (column). Let's say I have a column with the header "Capital Cities" - I want my tabs in my output file to be called London, Paris, Berlin, etc - you get the gist!

What I've succeeded in doing when I'm creating an output Excel spreadsheet is either

Sheet1London

Sheet1Paris

Sheet1Berlin

LondonSheet1

ParisSheet1

BerlinSheet1

This works - but I want to get the naming of the Excel tabs (sheets) better?

How do I get rid of the "Sheet1" in the name?! I can't figure it out - I'm getting errors.

Many thanks for any hints!

Input

Output

Preparation

Accepted answers

DataNath

No problem @athina!

So in your Output Data tool, you just need to choose an Excel file. This simply acts as a placeholder and so it can be any Excel file in any location. The way you're configuring the Output Data tool will replace this anyway with your custom filepath and so to start with, just set it up as normal by selecting any Excel file and any sheet - that's why I have one in there called Multiple sheets.xlsx - this is just populating that part of the configuration so I can continue.

Where I have my Formula like this:

You need to replace everything up until \Multiple sheet example.xlsx with your own folder path.

You need to replace Multiple sheet example with whatever you want to call the file.

You need to replace [Region] with your [Capital Cities] field.

Leave the +'|||'+, the .xlsx and all other bits untouched. And make sure to select the 'Change Entire File Path' in your Output Data configuration. If this still doesn't work, can you please provide screenshots or your new workflow with your new attempt?

I've attached my workflow for you to reference.

Multi sheet output.yxzp

All comments

DataNath

Hey @athina, I recently built a macro that will do this for you!

All you need to do is download it, insert it at the end of the canvas (right click > insert > macro > navigate to where you saved it) where you want to output your data - instead of an Output Data tool - and select the 'multiple sheets' option; the configuration is pretty straightforward but let me know if you run into any issues.

https://community.alteryx.com/t5/Public-Community-Gallery/Output-to-multiple-Excel-files-or-sheets/ta-p/951995

In your current approach, are you just adding the sheet name you want to the full filepath you bring in? If so, you'll need to first trim off the bit after ||| (i.e. your Sheet1$).

Emmanuel_G

Hi @athina ,

Here is a test to answer to your question.

Let me know if it works as you want.

Cheers,

Output many sheets from excel file.yxzp

athina

Hiya,

Many thanks for taking the time to respond

Haven't looked at the macro yet, I'm really hoping I can just solve this in an easier way (only have about 2 days of Alteryx experience!)

I've included a screenshot of what I'm currently doing and as I said it's semi working - the problem is that Alteryx is then asking me for a Sheet name which I have no option of skipping or saying no to, I have to give it something. It then appends "Sheet1" to the final output and I'm getting Sheet1London etc

But if I delete the |||Sheet1, then it's failing with an error and it's telling me I need to specify a Sheet name.

There's probably something obvious that I'm missing!

Screenshot 2022-07-07 162515.png

Screenshot 2022-07-07 162515 - Copy.png

Screenshot 2022-07-07 163043.png

DataNath

Hey @athina the macro is an easy approach - it's just a case of downloading it and adding it to your canvas like any other tool and there's less configuration than the Output Data tool!

If you're keen to keep your own, you just need to make a new field that will make a filepath for you, like so (obviously using your own directory path and filename, and replacing Region with your Capital Cities field):

Then in your Output Data tool, you can configure it like so to replace the entire filepath - with the replaced, clean sheet name (without Sheet1), from this field:

Multiple sheets output.png

When you run, it should output nicely to the same file, but different sheets:

All the macro does is automate this so you don't have to do the formula and Output Data configuration yourself!

athina

Hiya, this looks great! Macros are awesome but I'm really trying to get the hang of this hopefully - so I'd rather do it a bit more manually to begin with. Or at least give it a go before I give in and use a macro

I've tried all the steps in your screenshots and I'm still getting the "you need to specify a Sheet name" error

I'm clearly doing something wrong, let me try and understand this -

What are the two files?

There's a file called 'Multiple sheet example.xlsx' - is this the output file...?

There's also a file called 'Multiple sheets.xlsx' - what is this??

Sorry! And thanks

DataNath

No problem @athina!

Where I have my Formula like this:

You need to replace everything up until \Multiple sheet example.xlsx with your own folder path.

You need to replace Multiple sheet example with whatever you want to call the file.

You need to replace [Region] with your [Capital Cities] field.

I've attached my workflow for you to reference.

Multi sheet output.yxzp

athina

Perfect, it worked!

I hadn't realised that one of the files would work as a placeholder - so I hadn't created one before running the workflow

Fixed that - and it's all fine now!

(It also didn't help that I had a Select tool thrown in there - in which the Filepath field was initially unchecked...)

Many thanks again for the help, very much appreciated 😊

DataNath

Ah no problem at all, glad we could work it out together! Feel free to mark the solution so that others with a similar issue can easily find it have a great day and let us know if you have any further questions!

athina

I've accepted as solution

have a good weekend, thanks for the help!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703