Dynamic Input tool use of .xls file extension

Question

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 Answer

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 Answer

.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?