Extracting data from multiple sheets of the same Excel workbook

simonmay — Wed, 29 Apr 2020 15:49:31 GMT

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.

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:

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

Re: Extracting data from multiple sheets of the same Excel workbook

DavidP — Sun, 03 May 2020 07:39:35 GMT

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.

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

topic Extracting data from multiple sheets of the same Excel workbook in Alteryx Server Discussions

Extracting data from multiple sheets of the same Excel workbook

Re: Extracting data from multiple sheets of the same Excel workbook