We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Splitting Excel File to multiple Outputs

jsilavong
8 - Asteroid

Hi, 

 

I am looking to have an excel file with multiple sheets to be split into separate files for each sheet. If someone could provide a test workflow that I could adjust would be awesome. 

 

Thanks, 

Jon

12 REPLIES 12
JarekSkudrzyk
11 - Bolide

@jsilavong hi, you can create the path for file to write (in separate column of your dataset) and then use it in output tool:

JarekSkudrzyk_0-1651673588536.png

 

Sebastiaandb
12 - Quasar

Hi @jsilavong 

 

Here you go, this should work for you :-):

 

You basically feed the sheetnames to the batch macro that batches these sheets and outputs them to separate files :-). 

 

Sebastiaandb_0-1651674426124.png

 

Just make sure to change the paths and you're set to go :-)!

 

Greetings,

 

Seb

 

jsilavong
8 - Asteroid

Thanks @Sebastiaandb 

 

When I download your workflow I only get the below... am I missing something?

 

jsilavong_0-1651676060432.png

 

alisonpitt
11 - Bolide

@jsilavong you can do this by using dynamic input, creating new filepaths and then outputting. Please see attached.

 

Essentially:

  • Use input tool to get a list of sheet names
  • Feed that into Dynamic Input using Field: Sheet Names
  • Parse out the tab name and build a filepath
  • Clean up :)
  • Output to multiple files using the filepath you built
Sebastiaandb
12 - Quasar

Hi @jsilavong 

 

I added a new version that has relative paths so as long as you keep the TEST file in the same directory as the workflow/macro, you'll be fine (see attached)!

 

 

 

 

 

 

If you want to alter the old version, this is the way to do that:

 

You have to do a couple of steps to make it work in your environment :-):

 

1) Change the path of the input tool to your local/network path:

Sebastiaandb_0-1651730224898.png

 

2) Right click the  macro and click open macro, then change the path for the input tool again:

 

Sebastiaandb_1-1651730280372.png

 

3) In the macro change the path in the formula tool to your own local drive/network drive:

 

Sebastiaandb_2-1651730328171.png

4) Just to make sure, do the same for the output data tool:

 

Sebastiaandb_3-1651730358362.png

 

5) Just to make sure, check both action tools to see if the path is updated and still does the right thing:

 

Sebastiaandb_4-1651730409356.png

 

Sebastiaandb_5-1651730425262.png

 

Then press ctrl+s to save the macro and run the workflow again. It should work, the errors it dropped at your machine were purely because of the workflow/macro referring to my machine instead of yours. 

 

 

jsilavong
8 - Asteroid

@Sebastiaandb 

 

Thank you for clarifying. I was able to use the workflow successfully after adjusting the paths. My output from the test file though is not what I expected. Its combining all the sheet data from my input into one output file. I wanted to divide the input excel file by sheet into multiple files with only the sheet data on each. 

 

jsilavong_0-1651753287138.png

 

jsilavong_0-1651753439408.pngjsilavong_1-1651753449107.png

 

 

jsilavong_2-1651753460735.png

jsilavong_3-1651753478231.png

 

jsilavong_4-1651753495061.png

 

Sebastiaandb
12 - Quasar

mmm @jsilavong, that's weird. For me it's spitting out multiple Excel's based on every sheet you have in your source like:

 

Sebastiaandb_0-1651753690954.png

Sebastiaandb_1-1651753711316.png

 

 

jsilavong
8 - Asteroid

@Sebastiaandb 

 

Do you see anything wrong with my attached?

Sebastiaandb
12 - Quasar

Hi @jsilavong ,

 

Yeah i found it!

 

Open the macro and see:

 

Sebastiaandb_0-1651756184368.png

It wants to replace "BOO" in the formula tool, that was the dummy i gave to the name of the Exceloutput to be replaced by a different name.

To work you need "BOO" in the formula tool as well like:

Sebastiaandb_1-1651756241142.png

 

I'm sure it will work afterwards :-).

 

Greetings,

 

Seb

 

Labels
Top Solution Authors