Load all the sheets of a file at once in 'File Browse'

[Sousou]

Hello Community,

I would like to add all the sheets of a file at once, currently I use 3 "File Browse" to select the 3 sheets of the same file.

Is it possible to exploit all the sheets of a file with a single File browse?

Like this:

thank you in advance for your help

[Paulteryx]

Great question, and one I've come across several times. Fortunately, there's a relatively straightforward DIY method. Check out this article written by a member of the amazing Alteryx Support team that walks you through the process!

[Sousou]

HI @Paulteryx,

Thanks for your answer,

but i think it's not exactly what i want, because in my case, i want to use a file browse into Alteryx Server Gallery, so i know how to load multiple sheet from a file in Alteryx Designer with a Directory tool, but i don't know, how to do the same thing in Alteryx Server with a file browse.. do you understand what i mean ?

thank's for your help

[DavidP · Accepted solution]

I think this may be more what you want, if my understanding is correct.

In order to load all the sheets, you 1st have to load all the sheet names. The formula tool and dynamic input tools do this. It doesn't matter what sheet you select in the file browse, the formula tool ensures that the <List of Sheet Names> is loaded by the dynamic input tool. 

The 2nd formula tool then creates file paths for each of the Sheets and a Batch Macro is used to load the data from each Sheet, retaining the file path in the output data.

The formula tool after the macro then extracts the Sheet name and 3 filter tools separates the data per Sheet Name - you can write fancier logic here if you have variable number of sheets, or use a more complicated macro.

In your example, there are no column names. If there were, you'd have to edit the dynamic input tool in the macro not to read data from line 1. You'd also have to write some logic to remove the null columns.

Workflow and macro attached.

[Sousou]

Hello @DavidP,

Thank's a lot for your answer, your solution seems to be what i want :) !! but unfortumately i can't try it right now because you are using a more recent version of Alteryx, so i can't open your file, i will ask to update my version asap !

I thank you again for your help and I hope it will work on my side

[DavidP]

Which Alteryx version are you using?

[Sousou]

This one: 2018.1.3.42973

[DavidP]

You can try opening both the yxmd file and the yxmc file in a text editor and change the version from 2018.4 to 2018.1 and save the changes.

It sometimes works if the versions are not too far apart.

[Sousou]

@DavidP thank's again for your time & help

I can't find something about version in my text editor, as you can see below....

I will be back on monday, i can't use alteryx during my weekend, hope we will find a solution for the next week.. thanks and enjoy your weekend

best regards

[DavidP]

The yxzp file is a zip file. If you change the file extension to .zip you'll be able to extract the yxmd and yxmc files. You'll then be able to open those files in a text editor - they're in XML format. The version is on the 2nd line.