Import Data from Multiple Excel files's Specific Sheets through Batch Macro

Hello - I know this is a common issue and there are lot of posts on this topic. I have a similar issue but slight different. I tried my best from community posting but was not able to resolve it.

I want to import 2 specific sheets which have different schema from multiple excel files (all files are saved in one folder) union them and then through batch macro combine all outputs. I want to open both sheets at the same time in my Macro Workflow so in the Input Data Tool I specified the name "West" and "South". When I run this workflow stand alone I am not getting any error. But when I run Main Workflow which has a directory tool & Batch Macro Tool I am getting an error message "No sheet specified you must specify a sheet". I am confused why asking for specify a sheet when I already specified both sheets in Macro Workflow Input Data Tool.

I have attached both Main Workflow and Macro Workflow along with my two sample excel files. I would appreciate if some one help me in fixing this problem. Not sure which workflow need to be fixed. It is my understanding that I do not need to use dynamic inputs tool as I am using the batch macro. Thank you in advance and hope some experts will help me.

Muhammad Anwer

Note: I am using Alteryx version 2018.4

If you need any another explanation please let me know.

Workflow

Error Message

Tips and Tricks

Batch Macro

Interface Tools

Accepted answers

atcodedog05

Hi @anwerm2

I tried out your macro it works fine for me.

Folder structure:

Data is the folder where i have all the files

When i run your app and set the path to this folder it works.

Referring to the location where files are saved.

Output:

Please check and let me know. Make sure your folder has the .xlsx files

I have attached the zip folder.

Hope this helps 🙂 feel free to reach out.

Batch.zip

anwerm2

You are absolutely right my batch macro workflow is working fine. I tested it with my real excel source files. I don't know what I was doing wrong. Thanks you all for helping me

All comments

atcodedog05

Hi @anwerm2

In your macro do the specific changes and try.

Both action tool should configured with replace string like below.

Currently its replacing ABC Project.xlsx|||`West$` instead of ABC Project.xlsx and your sheet name is getting missed out.

Please check and let me know.

Hope this helps 🙂

echuong1

You can use a standard batch macro method to achieve this. The only difference is the full paths that you are passing through the batch macro will need to be filtered for the sheets you want.

In the example below I'm importing two files. One file has two sheets while the other only has one. I am only looking to import sheets that are named "sheet1." I can use the filter tool to narrow down to the sheets I want to import, before going into the macro.

2020 US Customers.xlsx

Customers workflow.yxzp

2019 US Customers.xlsx

mceleavey

Hi @anwerm2 ,

you need to first read the sheet names from the Excel files, then append them using a formula to the Fullpath tool that comes out of the Directory tool. You need to include the pipe separator as well:

[Fullpath]+"|||"+[Sheet Name]

anwerm2

Hi @atcodedog05 As suggested I made the changes now I am getting this error message "file not found". Attached are my two workflows. Thank you in advance

Macro WF.yxmc

MainWF.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703