Multiple Sheets from Excel - Uploading and Merging

[Yeonsujen]

Hi All,

I need some help with uploading and merging multiple Excel sheets to Alteryx.

I have the attached "Example_MultipleSheets.xlsx" file and created the following workflow:

For the input files, I manually upload each one and select the different sheets from the Excel file. There are sometimes "Notes" and "Hidden Files" sheets that are not needed. Also, each sheet needed has a different number of rows I need to pull from so I need to manually adjust each "Select Records" tool. I then get the attached "Output File.xlsx".

Even though this workflow works, it looks quite messy and it can be very tedious when I have an Excel file with 20+ tabs and need to manually upload them and select records for each one like above. Is there a better, efficient way of doing this?

Thanks in advance!

[messi007]

@Yeonsujen,

You can do it using a batch macro :

Below the macro

Attached the zipped workflow.

Hope this helps,

Regards,

[Yeonsujen]

Hi @messi007, thank you for your solution!

I apologise in advance since I'm still a beginner and have never used a batch macro. I have some questions below:

Is there a way I can select individual sheets from the "List of Sheet of Names"?

For example, I don't want to load the "Notes", "Hidden 1" and "Hidden 2" sheets.

Also from the output, there are a number of rows that are not needed. As mentioned above, each sheet required has a different number of rows I need to pull from. Is there a way I can remove all rows where the "Assignee Last Name" are all [Null]?

Finally, do I need to have "Reader.yxmc" saved anywhere in the same workflow? I'm not fully aware of how this works, sorry!

[messi007 · Accepted solution]

@Yeonsujen,

I added a comment on the workflow to answer your questions.

You have to save the workflow and the macro in the same folder.

Best regards,

[Yeonsujen]

@messi007 Thank you for answering my questions in the workflow! Super helpful to know which formulas/tools to use, much appreciated 🙂

[Yeonsujen]

Hi @messi007,

Sorry, it's me again! I am trying to use your workflow and macro for a different file. I have changed the file on the "Reader.yxmc" macro and used the first sheet as a sample (just like in your original macro for the "Amy Green" sheet):

However, now when I run this on the "ReadSheets.yxmd" workflow, it doesn't pull all the data from the different Excel sheets and only repeats from the first Excel sheet in the macro file:

I'm not sure what the issue is, could you please help me?