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 Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.
SOLVED

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

Sousou
8 - Asteroid

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:

 

exceltest.PNG

excelsheets.PNG

 

thank you in advance for your help

13 REPLIES 13
Paulteryx
11 - Bolide

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
8 - Asteroid

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
17 - Castor
17 - Castor

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.

 

all sheets app.png

Sousou
8 - Asteroid

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
17 - Castor
17 - Castor

Which Alteryx version are you using?

Sousou
8 - Asteroid

This one: 2018.1.3.42973

 

Capture.PNG

DavidP
17 - Castor
17 - Castor

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
8 - Asteroid

 

@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

 

 

Capture3.PNG

 

 

Capture2.PNG

DavidP
17 - Castor
17 - Castor

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.