Random Sample from Multiple Files

[efaherty]

Hi All! I'm relatively new to Alteryx, hoping this is an easy ask.

I have 200 excel files that I need to pull random samples from. I am looking for 10 random records from each file unioned together into one output file (so 2000 records total in the output). Is this something I'd use a macro for? All of the files are located in the same folder.

Thanks in advance!

[apathetichell]

Yes. you need a macro. at least one.

I'd recommend reading up on how to use a batch macro to pull in multiple files with multiple schema (assuming they have multiple schema). I do not know what you are doing with multiple sheets but that is often a consideration. The sample tool part is easy as you can just connect that prior to the macro output... 

[ArtApa]

Hi @efaherty - I attached a solution that may work for you. The following elements are important:

1) Your workflow would need a batch macro:

2) Batch macro would read all your Excels:

3) You also need a Random % Sample tool inside your macro:

Hope that will work for you.

[danilang · Accepted solution]

Hi @efaherty 

You only need a macro for this if the schema is different in any of the files.  If they are the same, then you can use the following

Use a wild card to read all the workbooks from the directory.  Add a random number to each row.  Sort by filename and random number.  Use a Sample tool to extract the first X records for each file from sorted dataset.

Dan

[efaherty]

Thanks all for the responses! The schema's are the same in each file so @danilang's solution worked perfectly!