I have Excel spreadsheets that I'm importing that sometimes don't have a specific named tab in the file. Is there something along the lines of a CASE or EXISTS SQL statement that be performed and not fail the attempted opening of a tab that isn't there?
To further clarify, on most days tabs A, B, C will exist in an xlsx spreadsheet, and through an Input file I'm attempting to bring in worksheet A, by the default SQL "Select * from `A$`". On days when it doesn't exist, I need to avoid the error "`A$` does not match a sheet or named range in C:\Etc\etc.....\file.xlsx".
Thanks
Solved! Go to Solution.
Another way to approach this might be to dynamically list all the sheets in the XLSX files and then read them into Alteryx. This way Alteryx will read whatever sheets are present at that particular time, regardless of if 'A$' exists or not.
This article has a module which you can download to take a look at the above logic.
Best,
Jordan Barker
Solutions Consultant
I created a more clever directory macro to replace the standard directory. If you select "Excel Sheets Separated", then it provides you all the sheets that are available to pull. It adds a new field to the output of a Directory called "FullPathAndSheet". Then you could use a Dynamic Input tool and bring everything in. This would potentially eliminate the error caused by nonexistent sheets you are having.
You need both macros in an appropriate folder for it to work.
Thanks for the nudge in this direction. Utilizing your macro for differing schema's, and Paul's macro for a modified Directory tool, this worked like a charm !!