Import Multiple Excel Sheet from same File

[SomuGS]

Hello,

I'm trying to import (add data) from multiple sheets (tabs) from a single excel file. I see a solution to combine multiple excel files into here, but it not helping in my case.

I want to add all the data from multiple data sheets into one data set. Also, wanted to mention that, all the data tabs do not have the same number of columns but they have the columns headers that are same that should be stacked.

Any help is much appreciated!

[cjaneczko]

Take a look at these two links. You should be able to use a batch macro in combination with this flow chart to pull all the tabs you need.

https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Input-Batch-Macro-AKA-the-Extr...

[danilang]

Hi @SomuGS 

If your files always contain the same number of sheets, there's nothing stopping you from using one Input Data tool(or Dynamic Input if the filenames change) for each sheet.  This is a simple, fast and easy to implement solution that people often overlook.  The only reasons to get more complicated are if a) the sheet names change or b) if you have a large number of sheets to read from. 

Dan

[SomuGS]

@danilang 

Thanks for looking at this. But I'm not quite following you note "If your files always contain the same number of sheets". I have excel with numerous tabs and each tab have a number of columns where are the few columns that same and others not the same. What I'm wanting is to add all the tabs data into one tab, which would all columns from respective tabs added and the corresponding data stacked one below the other. Just like the sample that I've attached here. 

[SomuGS]

@cjaneczko 

thanks for looking into this. But I should have said this earlier - I've little to no exposure to macors in alteryx. 

I took a stab as the provided link (Input Batch Macro - AKA the Extra Dynamic, Dynamic Input) and followed these steps.

1. Downloaded & installed the updated version of Batch import.

2. Tried to run the "TestWorkFlow". Selected>>the directoty>>File Spec and ran the workflow and I get errors

    > Error: Input Batch (5): Record #1: Tool #7: Record #1: Tool #4: Can't find the file: "D:\Users\xxxx\AppData\Roaming\Alteryx\Tools\InputBatch\Rollforward.xlsx". Make sure the file path is correct.

   > ErrorLink: Input Batch (5): https://community.alteryx.com/t5/*/*/ta-p/741718?utm_source=designer&utm_medium=resultsgrid|Record #1: Tool #8: No valid fields were selected.

Correct me, isn't this for solution for adding all the files in a specific folder? And, get the error

What I'm looking is to add multiple excel sheets/tabs with different set of columns. However, the column header (column names) are constant and I'm just trying to c

[K_Vinogradov · Accepted solution]

Hi @SomuGS!

You got very close to solving the problem with the batch macros. Essentially batch macros can be considered a separate workflow that iterates over each row of the data you input in it. By providing the batch macro a sheet name and the file location, you can iterate the workflow so that it loads the same file for every sheet in it, and then combines (unions) the data together upon output.

Attaching the workflow that I believe will help you achieve the desired result. In case you would like to know more about creating batch macros, feel free to explore the workflow as I provided some comments that would help you understand the functionality of it, and check out these videos from the academy to know more about them!

https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923
https://community.alteryx.com/t5/Interactive-Lessons/Creating-an-Iterative-Macro/ta-p/657925

[SomuGS]

@K_Vinogradov 

Thanks for this this I tried with the WF and it worked, but there is an issue. I'm not able to get the sheet/tab name in a column once the batch macro has finished running. The one you provided was also taking considering the result tab and adding to the final outcome. The result tabs is what I actually I'm wanting the batch macro to do.