Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

CS Macro Dev: Reading in Multiple Files with Different Field Schema - Batch Macro

Alteryx
Alteryx
Created

Reading in multiple files with different field schemas

 

This article is part of the CS Macro Development Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros.

 

The Directory tool can be very powerful when reading in multiple files from a folder, either on your local drive, or network location. Once you have the list of file paths you can use the dynamic tool or create a batch macro to read all of these files in. 

 

The Dynamic Input tool can be used if all your files have the same field schema. 

 

However, if the field schema's differ you will receive the warnings below when using the Dynamic Input tool to read in the full file paths.

 

As a result, you can create a simple batch macro which can be used repeatably to bring in multiple files at one time. 

 

Reading Field schema 1.png

 

 

Inside the Macro - The first step is to create your workflow.

 

1) Bring in an input tool and select one of the files you wish to read in. In this example I use .xlsx files, but you can choose whatever file format you wish to read in, however, it will need to be the same file format for all files you are reading in.

 

2) Add a control parameter above your input tool. This allows you to pass one file path at a time from outside your macro. There is no configuration needed on this tool.

 

3) Drag from the 'Q' of the control parameter down into the lightning bolt of the input tool. This will add in an action tool. In the Action tool highlight the file path in the configuration window.

 

This will now add it into the 'replace a specific string' option at the bottom of the configuration window. All this means is that the value you pass through your control parameter will replace this in the input tool. 

 

4) Add a macro output to the Input tool. This will allow the data to flow out of the macro back into your original workflow. 

 

2018-11-14_12-54-26.png

 

 

 

Now got to View>>Interface Designer (ID) and click on the cog icon on the left hand side of the ID window.

 

2018-11-14_12-56-42.png

 

 

In the output mode change the option to:

 

- Auto configure by Name (Wait until all iterations Run) - Auto Configure by Name will union the fields with the same names

 

- Auto Configure by Position (Wait Until All Iterations Run) - Auto configure by position will union field 1 etc. in the same position for every file read in.

 

 

2018-11-14_12-57-55.png

 

This will now remove the warnings you were getting with the dynamic input. 

 

Inserting the macro into your workflow

 

1) File>>Save As - Save the macro in a location where you can access it. Alteryx will know this is a macro workflow because you have added a control parameter, as well as a macro output. 

 

2) In a new canvas you can then right click on the canvas>>insert>>>Macro).

 

3) If you are reading in a list of files you can then use the directory tool to access the folder with the files in it.

 

4) As this is an xlsx example I will need to add the sheet name for each of the files. If you are reading in a different file type you will not have to do this step.

 

5) You can then configure the control parameter and select 'Full path'.

 

2018-11-14_13-02-01.png

 

 

Attached is an example workflow (Version 11.0) which you can use as a template to build off. 

 

If you would like to build a batch macro there is an excellent video on our demand training web page: http://www.alteryx.com/on-demand-training

 

*Although this macro has been tested it may not work in all scenarios. Please comment on the thread and I would be happy to assist.

 

Best,

 

Jordan Barker

Client Service Representative

Attachments
Comments
5 - Atom

@JordanB 

 

I've just started working with Alteryx and came across this macro, but I cannot get any results even after following all information provided and updating my file type to .xls in both the directory and the macro. Can you please help? 

 

 
 

1.PNG2.PNG3.PNG

 

 

 

 

 

 

 

5 - Atom

Hi, @JordanB 

Thanks for your guidance. It helps me combine one tab from each Excel. 

May I ask, if I need two tabs from each Excel, how can I do it? 

5 - Atom

I used the steps as suggested, but still I am getting same error saying excel is with different schema.  Excel has same column headers.