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 Knowledge Base

Definitive answers from Designer Desktop experts.

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

JordanB
Alteryx
Alteryx
Created

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 dynamictool or create a batch macro to read all of thesefiles in.

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

However, if the field schema's differyou 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 youwish 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 thecog 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 ServiceRepresentative

Attachments
Comments
AKGE
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

 

 

 

 

 

 

 

NingZhu
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? 

pantulapraveen
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.

Sneha_Lonkar
6 - Meteoroid

Hi @

 

 

 

Jose_Mondragon
5 - Atom

Hi Jordan, we are trying to read 162 xml files from a folder using your macro but only obtain the value from the file we selected inside the input tool. Do you know why macro is not reading all xml files?

 

Thanks in advanced

 

José

lepome
Alteryx Alumni (Retired)

@Jose_Mondragon 
Odds are that the Action tool is not updating the Input Data tool optimally for your use case.  

Jose_Mondragon
5 - Atom

Hi Lisa, 

 

Do you know how to configure the Action tool?

lepome
Alteryx Alumni (Retired)

@Jose_Mondragon 
There's a picture in the original post showing an example of Update Value.  That image shows that the Input Data tool is configured for Excel files.  If you have not already set that tool for .xml, then do that first.  (Depending on what you want to do with the file, you might want to read in as a .csv with no delimiter (\0) and then parse after the fact.)  Then you'll be able to update the path and filename from the Control Parameter.  If you aren't able to figure it out from the help and other resources, then you might want to contact the Virtual Solution Center.

keeprollin
7 - Meteor

@asifk Hello, I saw your comment on this post where you have mentioned that you were able to create a macro that allows reading multiple worksheets from a workbook with different schema. I'm fairly new to Alteryx and I have encountered a similar issue, which I'm not able to solve based on the solution mentioned. I have a directory which has three excel workbooks with multiple sheets within them. They all have same columns, however, one of the worksheets in workbook A has few columns with different schema (their data type is different) from the rest. Could you please help me understand what was your solution?

raghuc
5 - Atom

@JordonB

I have followed your example but getting different output.  For example If I process all the xlsx file with the same schema it works, but slightly changing the schema position it is not working. Please see my screeen shots

 

if you look at the final output, on extreme right you will see column "county"  and another column " county" just two columns before that.  In one of the file, I have changed the column order and that is the reason I see that column. But the input control property that I have selected " auto configure by name". Why it is not picking the same column that already exists instead of creating new column. Any inputs highly appreciated.

 

macromacroworkflowworkflowfinal outputfinal output

 

 

MeghanGohil
8 - Asteroid

Thank you!  This is super helpful.