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?

[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!!!!