Dynamic Input tool use of .xls file extension

[JoeMolinaro]

A problem arose in a workflow that uses a Dynamic Input tool used to input a list of files contained in rows of an Excel file where a file with an .xls extension is not being input when many others with the .xlsx file are being input just fine.  During trouble-shooting, an exact copy was made of the .xlsx file that contained the list of the files to input but instead it was saved with an .xls file extension.  This was done to create a different Dynamic Input tool that could be configured with the .xls extension.

The Excel files holds file names and paths that are to be input by the Dynamic Input tool and with certainty the worksheet names and paths are all correctly referenced.  The Dynamic Input tool configured with a format using .xlsx files finds both the file paths and the worksheets when the file input has an .xlsx file extension.  Whereas the one configured using the format .xls finds only the files path and not the worksheet resulting in an error saying:

Error: Dynamic Input (49): Error opening table: Microsoft Access Database Engine: The Microsoft Access database engine could not find the object 'Worksheet Name'.  Make sure the object exists and that you spell its name correctly. If 'Worksheet Name' is not a local object, check your network connection or contact the server administrator.

A logical solution would be to simply save the file to be input that has an .xls file extension as a file with the extension .xlsx.  That is, if the file was able to altered in anyway and of course in this case it is not.  Why does the .xls extension engage the Microsoft Access database engine in a different way than the .xlsx extension does?

[grossal · Accepted solution]

Hi @JoeMolinaro,

the problem with the XLS files is the following: XLS does not provide the "list of sheetnames" in the metadata, therefore Alteryx isn't able to read in the sheet names.

There are two possible solutions to it:

1) Hardcode the list of sheet names into your workflow build the correct path (file.xls|||SheetName$)

2) Build a workflow that converts the XLS file to a XLSX and than work with the XLSX. You might need a delay tool from the CREW-Macros or use chained apps to make sure it's written before you try to read it.

I hope this helps.

Best

Alex

[BrandonB · Accepted solution]

.xls is a legacy file type that needs to leverage the microsoft access database engine using the Microsoft driver: https://www.microsoft.com/en-us/download/details.aspx?id=13255

Rather than combining both in one dynamic input, could you instead have one part of the workflow that handles the xlsx files and the second that handles xls. Then these two streams could be unioned together. Would this work for you?

[JoeMolinaro]

Hi

Breaking the workflow up into one for the .XLS files and a separate one for the .XLSX files was the first thing I attempted however I was still getting an error saying: Unable to open archive for unzipping.  It turns out that in the file that contains the list of files and worksheet names to input, the ones with an .XLS file extension MUST have the $ wildcard after the name FILENAME|||WORKSHEETNAME$ whereas the ones in the list with an .XLSX extension do not need it - FILENAME|||WORKSHEETNAME no dollar sign input fine.  As soon as updated all the files in the list with the .XLS file extension to include the "$" wildcard at the end of the worksheet name, the workflow input everything on the list without errors.

Thanks so must for your help. It was truly appreciated.

Joe

[JoeMolinaro]

Hi

Option one worked.  I created a separate Dynamic Input tool configured with the .XLS file format to be used exclusively for the files on the list with the .XLS file extension.  It turns out that in the file that contains the list of files and worksheet names to input, the ones with an .XLS file extension MUST have the $ wildcard after the name FILENAME|||WORKSHEETNAME$ whereas the ones in the list with an .XLSX extension do not need it - FILENAME|||WORKSHEETNAME no dollar sign input fine.  As soon as updated all the files in the list with an .XLS file extension to include the "$" wildcard at the end of the worksheet name the files with the .XLS input fine in their stream and the files with the .XLSX extensions input like they did normally.  Now I just need to put the two streams together and I will be all set.

Thanks so must for your help. It was truly appreciated.

Joe