Reading in multiple files with different field schemas
This article is part oftheCSMacroDevelopment Series.Thegoal of this series is to communicate tips, tricks, andthethought process that goes into developing good, dynamicmacros.
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 can be used if all your files have the same field schema.
As a result, you can create a simple batch macro which can be used repeatably to bring in multiple files at one time.
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.
Now got to View>>Interface Designer (ID) and click on the wrench icon within the ID window.
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.
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'.
Attached is an example workflow (Version 11.0) which you can use as a template to build off.