Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Alteryx Macro

Sureshpa
7 - Meteor

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.

 

9 REPLIES 9
grossal
15 - Aurora
15 - Aurora

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
7 - Meteor

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
ACE Emeritus
ACE Emeritus

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
15 - Aurora
15 - Aurora

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

 

grossal_0-1588618332155.png

 

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

 

Best

Alex

Sureshpa
7 - Meteor

Thanks a lot sir for your prompt response.

 

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

 

 

 

jarrod
ACE Emeritus
ACE Emeritus

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
7 - Meteor

Sir,

 

Please explain..

grossal
15 - Aurora
15 - Aurora

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.

 

grossal_0-1588661571736.png

 

CReW Macros are available here:

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

jarrod
ACE Emeritus
ACE Emeritus

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:

jarrod_0-1588679201424.png

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:

jarrod_1-1588679332689.png

 

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)

jarrod_2-1588679414870.png

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. 

Labels