XLS Dynamic Input

[clarrock]

Hi Team

I have a file that is in xls format.. And whose table name or sheet name changes everyday

How do I use the dynamic input tool to input the file from a directory

I seem to get errors when doing so. 

Please do send a simple workflow with a template you used - it would be really helpful

Thank you

[Emil_Kos]

Hi @clarrock ,

The solution that will take the newest file from a particular folder will work for you? 

If yes please check this link for a sample workflow:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-dynamically-run-the-most-rec...

[Qiu]

@clarrock 
I think this should work for you.

Révélation

[clarrock]

@Qiu I think your taking a xlsx file from the directory tool not an xls file..as I get this error

 

[atcodedog05]

Hi @clarrock 

xls is a old version(97–2003) of xlsx. Alteryx cant directly read sheet names of xls. 

Here is a post where python code is used to read xls sheet names.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Listing-the-list-of-sheet-names-from-X...

Additionally if you can rename extension xls to xlsx it will be more flexible.

[atcodedog05]

Hi @clarrock 

Was able to crack it. 

Workflow:

Make sure you select the folder with files in the directory tool.

Output:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[clarrock]

Hi @atcodedog05

This is a good solution

But I'm getting this error -  what could be the reason 

Please advise

[atcodedog05]

Hi @clarrock 

Can you share the complete screenshot which tool is causing this issue.

I can help you based on that.

[clarrock]

@atcodedog05

It's append fields

 

[mceleavey]

Hi @clarrock ,

I've written a couple of useful macros for this.

I've attached the example workflow and the tools. The first macro dynamically reads in the sheet names from all .xlsx files (you'll need to change the extension references to .xls but the functionality is the same). These can then be attached to the fullpath field and fed into the second macro to load them in.

You can apply filters to remove the ones you don't want, and you can sort by creation date descending and sampling the first 1 to take the latest (grouping by name if you want the latest one for each filename etc.)

Anyway, I hope this helps.

M.