Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Discussions

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

Extracting data from multiple sheets of the same Excel workbook

simonmay
5 - Atom

Hi,

 

I'm trying to create a workflow that takes in an Excel file and performs different extractions on each sheet, then outputs each clean sheet to a single consolidated output file.

 

The first step works as expected in designer.

 

Input.PNG

The user selects a file with the File Browse tool. Then the action updates a Text Input with the file path (sans sheet name).

 

The file path goes to multiple Python tools with Alteryx.read and the code extracts the data needed from the relevant sheet (the code looks for a specific sheet name) and outputs a dataframe. There are 15 of these extractions, with checkboxes to enable or disable them:

extraction.PNG

Finally, the dataframes go to an output file.

 

Like I said, this all works as expected in Designer. The issue is when I save to the Gallery and try to run the app, I get the following error:

 

The jobPython Extraction v19did not complete successfully and resulted in the following errors:

  • --------------------------------------------------------------------------- FileNotFoundError Traceback (most recent call last) <ipython-input-2-90ab1f6b3a77> in <module> 66 67 # Create consolidated PS dataframe ---> 68 df_cons_PS = extract_proj_summary(pack) 69 # Send dataframe to output anchor number 1 70 Alteryx.write(df_cons_PS,1) <ipython-input-2-90ab1f6b3a77> in extract_proj_summary(pack) 4 5 # open workbook as specified in parameters as wb element ----> 6 wb = xlrd.open_workbook(pack) 7 8 # extract the names of all worksheets e:\program files\alteryx\bin\miniconda3\envs\jupytertool_venv\lib\site-packages\xlrd\__init__.py in open_workbook(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows) 109 else: 110 filename = os.path.expanduser(filename) --> 111 with open(filename, "rb") as f: 112 peek = f.read(peeksz) 113 if peek == b"PK\x03\x04": # a ZIP file FileNotFoundError: [Errno 2] No such file or directory: 'P:\\PUBLIC\\Accounts\\FP&A\\Projects\\BI Tool\\Power BI\\New F packs\\PBI files\\F-Pack EVO\\EVO Data\\Python Data\\F Packs Input\\F1 2020\\F1 2020 - BLD - Post.xlsm' (Tool Id: 1)

 

No matter what file I select, I always get this FileNotFound error.

 

I'm concerned that Alteryx Server doesn't allow a Text Input to handle the file path and instead requires a Data Input. If this is the case, it will demand a sheet name from the user, making the entire workflow pointless as the extraction will only work for that 1 sheet and the other 14 won't work.

 

Does anybody on here have experience with workflows that operate on multi-sheet workbooks in this way? If so please can I ask for some assistance or advice with the above error? It would be extremely appreciated.

 

A copy of the workflow is attached.

 

Many thanks,

Simon

1 REPLY 1
DavidP
17 - Castor
17 - Castor

The way you would normally do this is to take the filename or fullpath name you got from the file browse tool and add list of sheet names with a forumal tool like this. 

 

You can then use a dynamic input tool to load the list of sheet names.

 

DavidP_0-1588491426293.png

 

Alternatively, use the wildcard XLSX macro from the CreW Macros.

 

DavidP_1-1588491556526.png