Creating a list of all excel sheets in a directory

[carrera]

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:

Record	Sheet Names	Path
1	Sheet 1	...\file1.xlsx|||'Sheet 1$'
2	Sheet 2	...\file1.xlsx|||'Sheet 2$'
3	Sheet 3	...\file1.xlsx|||'Sheet 3$'
4	Sheet 1	...\file2.xlsx|||'Sheet 1$'
5	Sheet 2	...\file2.xlsx|||'Sheet 2$'
6	Sheet 1	...\file3.xlsx|||'Sheet 1$'
7	Sheet 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?

[apathetichell · Accepted solution]

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]

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

[vbmike73]

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]

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.

[vbmike73]

Thank you!

[apathetichell]

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]

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

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 ;) 

[vbmike73]

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]

@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...