Alteryx Macro

[Sureshpa]

Dear Team,

I have some data for search in a folder, where 100 excel file.

How do i search data in 100 excel file in a folder.

[grossal]

Hi @Sureshpa,

do all files have the same schema? Are we talking about Single-Sheet Files or multiple sheets within a file?

What do you want to search for? Are you able to provide some sample data?

Best

Alex

[Sureshpa]

Sir,

All 100 files are same.

There are 3 sheets in a file. but we need only one sheet which name is 'Allocation'.

I want to map some data with 100 excel file (Allocation sheet).

[jarrod]

If they are the EXACT schema, then you should be able to pull in the tabs by using the wildcard * in the input tool:

.\Excel File Name*.xlsx|||`Allocation$`

the easiest way to accomplish this is to load up a single file and choose the tab you want then manually put the * into the input tool directly and run the workflow.

[grossal · Accepted solution]

If you place your workflow in the same folder as your files, this settings should work:

If your workflow is not in the same folder, than replace the dot in the front with the folder path. 

Best

Alex

[Sureshpa · Accepted solution]

Thanks a lot sir for your prompt response.

Just want to know if file schema is not same then what is the solution.

[jarrod]

Batch macro where the control parameter updates the file path. Also set the macro settings (interface window) so that it waits for all iterations.

[Sureshpa]

Sir,

Please explain..

[grossal]

I agree with @jarrod that the Batch Macro would be a good option. If you want to learn more about Batch Macro, check out this article:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

However, Batch Macros aren't the only option. You could also use the Wildcard Input from the CReW Macros.

CReW Macros are available here:

http://www.chaosreignswithin.com/p/macros.html

[jarrod · Accepted solution]

As @grossal mentions, you can use crew macros (which are batch macros when it gets to the function) see screenshot below of internal CReW_WildcardXLSXInput.yxmc:

in that macro, you see above, it uses a control parameter to update the input tool with a new file path to use each record you pass through. the formula tool you see before it is creating the file path which is the full file path plus the sheet name.

inside that batch macro looks like this:

i prefer to make my own macros sometimes due to the specific nature of what I may need at the time, but CReW could work for you in this instance just as well. (you can also just copy out that sub macro and use it if you wanted too)

The other step i was referring to is the Interface Designer - Properties window (if you don't see it choose View -> Interface designer or Ctrl + Alt +D)

selecting the correct output mode is crucial to this success of this batch macro because it tells alteryx to run all the iterations, wait then union by Name. This is equivalent to dropping each file onto the canvas and connecting to a union tool and this gets around the multiple schemas issue you were having by just using the input tool.