Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Identify Hidden Excel Sheets With Alteryx Python Tool


I just have had another stroke of brilliant geeky mood today.


Working with a customer to bulk load files from Excel using Dynamic Input tool + Batch Macro. CHECK. Awesome use case saving tons of time on consolidation and beyond.


BUT.... Alteryx does NOT distinguish between HIDDEN and ACTIVE FILE SHEETS.

What if you have 100 sheets in Excel workbook. Half of that hidden.

And you need to work only with those ACTIVE sheets? Man, oh man!


Nothing that a lovely evening of Big Bang Theory + Python in Alteryx would not solve.




Typically I like to write 3 lines of Python code and then ramble about it in 6000+ words on Alteryx Community.


This time I will keep it short.


The workflow (attached) is using DIRECTORY tool to load FULL PATH of the XLSX files you want to get the list of all SHEETS from. Streaming this into the Python code tool that relies on XLRD package extracting all sheets + HIDDEN info flag with it.


Note: The sky is the limit from then on - you should be easily able to take that table and use it together with some basic logic (filter tools) + dynamic input/ batch input macro to only load ACTIVE sheets. Or whatever.




The code used:



# List all non-standard packages to be imported by your 
# script here (only missing packages will be installed)
from ayx import Package
Package.installPackages(['xlrd']) #Extract data from Excel spreadsheets

from ayx import Alteryx
import xlrd as xl
import pandas as pd

#Read data from connection #1
input_df ="#1") 

#Placeholder for file path
input_file = "" 

#Placeholder for output DF  

#Iterate through input files list
for index, row in input_df.iterrows():
    input_file = row[0].replace("\\","/") #Read Full Path
    book = xl.open_workbook(input_file) #Open the workbook
    for sheet in book.sheets():    #For every sheet
        df = pd.DataFrame({"File Path":row[0],"Sheet Name":[],"Hidden":[sheet.visibility]})   #Construct the datafrrame based on File Path, Sheet Name, Visibility of sheet
        output_df = output_df.append(df,  ignore_index = True, sort = False) #Append to the data frame placeholder

Alteryx.write(output_df,1)    #Write to the output  



That's all folks...




David Matyas
Sales Engineer
Alteryx Certified Partner







14 - Magnetar
14 - Magnetar

So cool. Thank you @DavidM !!