In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Creating a list of all excel sheets in a directory

carrera
5 - Atom

Hi all,

 

I am a total newbie in Alteryx Designer and need your help. I would like to create a list with all sheets of excel files from a directory. I am not interested in the content of the sheets (yet). I just want a list in the form:

 

RecordSheet NamesPath
1Sheet 1...\file1.xlsx|||'Sheet 1$'
2Sheet 2...\file1.xlsx|||'Sheet 2$'
3Sheet 3...\file1.xlsx|||'Sheet 3$'
4Sheet 1...\file2.xlsx|||'Sheet 1$'
5Sheet 2...\file2.xlsx|||'Sheet 2$'
6Sheet 1...\file3.xlsx|||'Sheet 1$'
7Sheet 1...\file4.xlsx|||'Sheet 1$'
.........

I tried a batch macro for this: Read in Multiple Excel Files, with Multiple Tabs that have Different Schemas (alteryx.com) but omitted the second part (macro), since I don't want to open the sheets.

Unfortunately, this does not work as desired. Does anyone have an idea how to solve this?



14 REPLIES 14
apathetichell
19 - Altair

You basically want a directory tool hooked into a batch macro - macro is set up for input and just takes the sheet names  from the batch macro and choose the "sheet names only" option. - then a macro output... Kinda like the attached file...

carrera
5 - Atom

That's what I was looking for! Thank you!

vbmike73
7 - Meteor

First of all, thank you for this solution. I downloaded it and after changing the path in the Directory tool, it does exactly what I want it to do. The only thing that puzzles me is that the Input Data tool in the macro has a path to a file on your C-drive, or at least that's what appears to be the case, since I don't C:\Users\micha folder. C:\Users\micha\Downloads\XML STR008R2_Excel.xlsx

 

When I change the file in this tool to something on my system, the macro stops working. I can't figure out how it can work only with this file. I must be missing something. Any help would be greatly appreciated.

TimN
13 - Pulsar

That file reference is just a placeholder or model.  If you want to change to a placeholder that is meaningful to you, I think you can select an xlsx file on your pc but make sure to select Import a list of sheet names.

 

TimN_0-1667936911044.png

 

vbmike73
7 - Meteor

Thank you!

apathetichell
19 - Altair

Yup - you need a placeholder in a macro/app for setting the base execution. you do not need to include the file - and this can show as an error prior to execution. Ideally you can reset this to a test file on your own system -but (and this the key part...)

 

Your action tool connected to your input/output data tool must be adjusted for the new location. Depending upon the setup of the action tool (ie if it's in update value mode) - it is looking for a specific string to replace (say a path to my harddrive). If this string is in the tool - that's fine - it works. If this string has been modified in the tool - but hasn't been modified in the action tool - then the action tool will not be able to update the value and the macro won't work.

 

 

JoshuaB
Alteryx
Alteryx

There is a way easier method to do this with just the one input tool.... use the Asterisk operator. 

JoshuaB_0-1668006353421.png

That filename field has what you need and if you also need to parse it out to file and sheets that's just one more tool ;) 

Joshua Burkhow | Chief Evangelist @ Alteryx | Follow me on LinkedIn and Twitter
vbmike73
7 - Meteor

I love this very simple approach! I was able to get it to work with a couple test files that were different variations of the same file. However, when I try to do it with another folder that contains various types of XLSX files, I get the schema error.

 

The file xxxxxx has a different schema than the 1st file in the set and will be skipped

 

How do I get around that?

apathetichell
19 - Altair

@vbmike73- @JoshuaB 's solution needs this part "I am not interested in the content of the sheets (yet). I just want a list in the form"  to be true...

 

If you want to read in the files (and you have schematic differences)  you'll need to use a batch macro...

Labels
Top Solution Authors