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

[anwerm2]

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.

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

[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 

[atcodedog05]

Hi @anwerm2 

Sorry my bad you need to do one more change.

In both the input data tool of macro can you change it to below.

 and check

I have attached the macro too.

Without the excel file i wont be able to run the workflow from my end

[atcodedog05]

Hi @anwerm2 

Please provide the excel file too. We can work on fixing your workflow with that.

Without input file we cannot run the workflows

Happy to help 🙂

[anwerm2]

Hi atcodedog05

I am still getting the error message. As desired I have attached all four files. Thank you a lot for helping me.

[mceleavey]

Hi @anwerm2 ,

I've attached a macro (Macro WF) that I think does what you need. 

Let me know if I'm close. I've bundled a macro that's needed within that  as well that you may need to save to the macro folder.

M.

[mceleavey]

Hi @anwerm2 ,

I've attached an app with embedded batch macros.

This allows the users to select a folder, the macro will then pull all files that fit the File Specification in the Directory tool (you will need to adjust this accordingly) and the Directory path selected by the user.

It will find those files that have "South" and "West" as tabs, append those tabs to the fullpath and load them all in, creating a union of them all.

I'm not sure if you want two outputs, one for each tab, or just all in one, but I've built it to output one file, which you can change accordingly.

I'm batched the workflow containing the macros, and I've attached the macros which you should save into your macro folder as they will be useful down the line.

Hope this helps, let me know how you get on.

M.