Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

multiple excel file from single excel workbook with multiple tabs

Ekta
8 - Asteroid

Hi all , I am new to alteryx and much help will be appreciated. 

problem : i have a excel file with multiple tabs , i am attaching it.

I need to extract the unique value from each tab and copy it in new excel files.

so for one excel workbook i need to have multiple excel file.

Its quite urgent , please help me with this,

i am attaching input excel and desired output excel

 

Book1 is input and out1, out2, out3 are outputs

 

Thank You

9 REPLIES 9
estherb47
15 - Aurora
15 - Aurora

Hi @Ekta,

 

It's not as straightforward as you would think. You need to grab the full filename and path from the input tools (one for each sheet that you're importing). Easy enough, enable the "Output File Name as Field" to be "Full Path"

 

image.pngThen, you can use a Formula tool to replace the original file name in that "Full Path" field (Book 1 in your example) with whatever you want to name each new file. I used Output concatenated with the Employee name field.

image.png

 

Finally, an Output tool can be configured to output to different sheets or files, as long as you check the "Take File/Table Name From Field" option. Choose to Change the Entire File Path, and use the FileName field. To remove that field from the output, uncheck Keep Field in Output. Also a good idea to set up the output tool to Overwrite Sheet (Drop) for #3 Output Options

image.png

Hope that helps. You can use the attached workflow, just replace the inputs with your own files.

 

Cheers!

Esther

cmcclellan
13 - Pulsar

I took a different approach, but it was a good challenge :)

 

 

Ekta
8 - Asteroid

Thank you so much , this workflow worked perfectly fine:)

dsevilimedu
7 - Meteor

Thanks for the solution @estherb47 

I was able to use part of your model to achieve part of my results. There is one thing that I am missing though

Is it possible to create a summary worksheet based off the multiple worksheets? Attached is the output from the model you created with few columns that I manually added and the summary sheet.

 

In nutshell, I would like to add a sheet and provide summary based off of the two sheets? Thanks much!

 

Thanks,
Deepika

 

estherb47
15 - Aurora
15 - Aurora

Hi @dsevilimedu 

 

So glad my model helped! You could write a brand new sheet into the same Excel workbook with another Output Data tool, in order to get your summary data in.

 

You might want to use a Block Until Done, in case Alteryx has trouble writing the multiple files to the same workbook.

 

Cheers!

Esther

dsevilimedu
7 - Meteor

Thank you @estherb47 

Is it possible to share a sample model, if you have it already? 

 

I really appreciate your help on this

 

 

Chhamanshu
7 - Meteor

 

I have 3 excel workbook with 5 sheets each. I wanted to combine all the excel workbooks using Alteryx macro into one excel workbook.

 

Can it be done in alteryx ? I f yes how ?

 

Please find the attached excel workbook below.

Chhamanshu
7 - Meteor

@cmcclellan @estherb47 Please find my question below. Do you have any solution ?

 

I have 3 excel workbook with 5 sheets each. I wanted to combine all the excel workbooks using Alteryx maco into one excel workbook.

 

Can it be done in alteryx ? I f yes how ?

 

Please find the attached excel workbook below.

cmcclellan
13 - Pulsar

Yes, probably very easy to do but you need to start a new post rather than adding to one that's already been answered 🙂 

Labels