community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Identify Hidden Excel Sheets With Alteryx Python Tool

Highlighted
Alteryx
Alteryx

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.

 

image.png

 

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.

 

image.png

 

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 = Alteryx.read("#1") 

#Placeholder for file path
input_file = "" 

#Placeholder for output DF  
output_df=pd.DataFrame()

#Iterate through input files list
for index, row in input_df.iterrows():
    input_file = row[0].replace("\\","/") #Read Full Path
    #print(input_file)
    book = xl.open_workbook(input_file) #Open the workbook
    for sheet in book.sheets():    #For every sheet
        #print(sheet.name)
        #print(sheet.visibility)
        #print(input_file)
        
        df = pd.DataFrame({"File Path":row[0],"Sheet Name":[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...

 

image.png

Cheers,

David Matyas
Sales Engineer
Alteryx
Alteryx Certified Partner

Nice.

 

also..

 

 

nerd.gif

Pulsar

So cool. Thank you @DavidM !!

Labels