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 NamesPath1Sheet 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?

Creating a list of all excel sheets in a directory

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?

Batch Macro

Accepted answers

apathetichell

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

all the sheets.yxzp

All comments

apathetichell

all the sheets.yxzp

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.

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

JoshuaB

Yes. Yes, and This is correct @apathetichell Just don't want users to jump to a batch macro IF it's not necessary

vbmike73

But I'm not interested in the content of the sheets. I only want a list of the sheet names from each file. However, it seems that if the files are different, I get the schema error. This method only appears to work for Excel files with the same types of sheets maybe? What's odd is that if I take the same file, and make copies of it while adding different sheets to each file, this method will work. Can't figure this out.

apathetichell

Hi - I think there is some confusion here... i don't know what "This method only appears to work for Excel files with the same types of sheets maybe" means here... If you are reading sheet names only - <List of Sheet Names> works for every .xlsx file - this does not work for .xls - which is a totally different format.

If you are reading in the columns - ie the schema of the sheet - you are reading in data from the sheet - and you have to use a batch macro approach if the schema on the sheets (or the files) is (are) different. It doesn't matter if you are reading the data in the columns or the column headers - you need to do this. If you just want .xlsx sheet names - @JoshuaB 's method will work.

vbmike73

Appreciate the help, but I think this is not entirely true. After more research, I've learned that the schema error comes from the max sheet name length. If the max sheet name length is not the same for every XLSX file in the folder, you will get the schema error. So if one file has a single sheet that has a 10-character name, and another file has a single sheet with a 12-character name, you get the error. As soon as I added a sheet to all my files that had a 15-character name (the name itself is irrelevant), then it worked just fine. It's an odd error, but at least I know now what is causing it. And if anyone else needs to know, the max sheet name length for an Excel sheet is 31 characters, but this really has nothing to do with the cause of this error.

Thanks to everyone for the help!

adelany

Very helpful, thank you!!!!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703