How to create a batch macro for reading multiple files with different schema

[Anuj_saxena90]

Hi Experts,

I am new to alteryx and getting some issues in reading multiple files with different schema. I tried to create a batch macros but got stuck somewhere. Here is my query with example-

File A

ID	Rank
1	4
2	5
3	ABC

File B

ID	Rank
4	2
7	3

File A has mixed data type . It has string also whereas in File B it does not have. Hence when I am reading all files in my  workflow all files having different schema like File A are getting skipped. 

Final expected output-

File C

ID	Rank
1	4
2	5
3	ABC
4	2
7	3

Can someone please help me with how to get this output ? Is there any other way creating macros if not then how to create it,

Thanks in advance

Anuj

[csmith11 · Accepted solution]

You will want to create a macro like the attached.

You will need to adjust the template file to be your file type instead of csv.

Now you can use a directory tool pointed at where ever your input files are.

Configure your macro to use "FullPath" as the control parameter, and watch your data get loaded in.

If this response is helpful, please consider marking it as the solution to help other community users.

[csmith11]

In case you are wondering how to use the macro,

1.Download the macro from my first reply and open it in Alteryx, Change CSV config to your file type. Save and Rename.

2.Create another new workflow. 

3.Place a directory tool pointed to your input folder on the new workflow.

4.Right Click on Canvas, Click Insert, Click Macro, Click on the Macro or browse for it. Configure as shown above.

5.Add Browse Tool, Run Workflow, and Review.

Please let me know if you have any other questions.

[Anuj_saxena90]

Thanks for your reply. I tried to replicate your solution but I am missing something hence in my output file it is replicating File A only 2 times. i.e. 

ID	Rank
1	4
2	5
3	ABC
1	4
2	5
3	ABC

so it is still skipping File B(which also shows in warning in macro) .

not getting what exactly I am missing. 

[csmith11]

Mind sharing a copy of the workflow you are using so that I review the configuration?

[Anuj_saxena90]

I can not upload the file but here are the detail steps which I performed to create macro-

1. Drag the input data tool and connected to a folder having all files(\*.xlsx).

2. Drag control parameter tool

3. Connected control parameter tool to Input data tool. After this update value tool automatically pops up and configured.

4. Connected macro output tool with output of input data tool.

5. For macro output tool --> Interface designer-->Properties-->Output mode(configure by name)

6. Save macro.

7. Repeat steps which you mention in reply 2.

[csmith11]

For Excel Files, you'd need to either use a formula to specify the sheet names you'd like to load, or use a macro to dynamically load all sheet names (if loading all sheets) for each file before using the multi load macro.

The inside of the multisheet macro looks like this:

Pay close attention to the use of the <list of sheet names> 

This tool opens each excel file 1 at a time to collect a list of the sheet names.

The action tool serves to replace just the specific string "FileA.xlsx" while still maintaining the <list of sheet names>  suffix.

The formula tool replaces the suffix with each sheet name that is loaded.

A row for each sheet name is returned from the macro providing a Fullpath for each sheet. This is the parameter that should be used in the Multi File Load Macro.

Please let me know if this is able to resolve your issue.