Dynamic Input Template - Worksheet Name

Jes · 2021-01-14T14:37:29+00:00

Background: * I am building a workflow where I am dynamically inputting 2 files (today and last business day) from a single folder directory into Alteryx. * Each excel file has a date stamp (MM.DD.YYYY) as seen below, which contains a single worksheet. * The worksheet name = excel file name, and so is unique to each file.…

Jes

Background:

I am building a workflow where I am dynamically inputting 2 files (today and last business day) from a single folder directory into Alteryx.
Each excel file has a date stamp (MM.DD.YYYY) as seen below, which contains a single worksheet.
The worksheet name = excel file name, and so is unique to each file.
A new file is generated every day.
The data table/schema in the excel files are the same.

To identify the 2 most recent files, I am using Directory Input, identifying the date stamp from file names, sort dates in descending order, and using a sample tool & filter to identify today & last business day's files.

Problem

In order to use the Dynamic Input tool, I need to select a file as a Data Template. Given that each excel file has a worksheet name that is unique to itself, I am not able to use Dynamic Input tool.

Any suggestions on how to get around this issue?

If I can wild card the input template's worksheet name, that'd be ideal.

Dynamic Processing

Accepted answers

AngelosPachis

Hi @Jes ,

I don't think the problem is the Dynamic Input tool and that each excel file has a different name; after all the dynamic input tool wants you to select a template, so assuming that the schema of all your files was the same, the dynamic input should be able to read through all of those fine.

I think the issue that you will come across sooner or later is that your different Excel files themselves are of different type. If you look closely, the icons for your different excel files are different, and that's what probably causes your dynamic input tool to error.

For example, a .xlsx is different from a .xls although if you open it in Excel, it looks the same.

The answer to your question is a batch macro, and to create that batch macro you can consult one of Alteryx's Weekly challenges

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-180-Thousand-File-Challenge/td-p/461356

Happy to help out more if you can provide a representative example of the files you would be using, you can mask the data or mock them up, as long as you have those different files we can see in the screenshot, with same filename/sheetname and some data within.

Hope that helps,

Angelos

All comments

AngelosPachis

Hi @Jes ,

For example, a .xlsx is different from a .xls although if you open it in Excel, it looks the same.

The answer to your question is a batch macro, and to create that batch macro you can consult one of Alteryx's Weekly challenges

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-180-Thousand-File-Challenge/td-p/461356

Hope that helps,

Angelos

mceleavey

Hi @Jes ,

I've attached a couple of macros I wrote and an example workflow.

Basically, the first macro reads the sheet names, then builds the fullpath for those sheets. Then the second macro reads them in individually using themselves as the template.

This will fix the exact problem you're experiencing.

Multi Sheet Import Example.yxmd

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema xlsx.yxmc

Jes

Angelos, you were right the difference in file type was causing the issue and not the input template's worksheet name.

Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703