We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

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

Anuj_saxena90
5 - Atom

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

IDRank
14
25
3ABC

 

File B

ID

Rank

42
73

 

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

IDRank
14
25
3ABC
42
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

6 REPLIES 6
csmith11
11 - Bolide

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.

 

csmith11_0-1631621925425.png

 

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.

 

csmith11_0-1631622493950.png

 

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

 

csmith11
11 - Bolide

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
5 - Atom

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. 

IDRank
14
25
3ABC
14
25
3ABC

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

not getting what exactly I am missing. 

csmith11
11 - Bolide

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

Anuj_saxena90
5 - Atom

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
11 - Bolide

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.

 

csmith11_1-1631703714939.png

 

 

The inside of the multisheet macro looks like this:

csmith11_3-1631704493229.png

 

 

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.

 

 

 

Labels