Import multiple excel tabs from multiple files with different schema

Hi,

I have around 70+ excel workbooks having multiple tabs in each workbook. All workbooks have a tab among them which is named the same across all workbooks (For eg: A 'sheet1' tab is available in all the workbooks) .I would like to extract the same named tab from all the excel sheets and combine it into one dataset. However, the schema of the tab differs from time to time and workbook to workbook, hence I would not be able to use wildcard or any other commonly used tools. The main issue over here is the schema of the tabs is different and I am having trouble using straight forward options.

I do understand that there is neat explanation on building a macro which helps if you have a workbook and multiple sheets, or multiple workbooks with a single sheet. However my case is different and would need some additional help.

NOTE: I am using an old version of Alteryx (2018.2.6.51223) and am not able to run any of the given macros in the

previous posts. It would be really helpful to provide formulas as text and workflow screenshots for me to recreate the workflows.

Thanks in advance.

Regards,

Harsha

Developer

Macros

Accepted answers

ChrisTX

See the solution on this page:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Get-worksheet-names-from-excel/m-p/344867

Macro to read all sheets, even if the schema (layout) is different on any one Excel sheet

All comments

JoeS

Hi @Sriharsha-3134

You should be able to use one of the solutions that read in multiple workbooks but a single sheet.

From my understanding that is what you are looking to achieve?

You need to create a batch macro, where the control parameter is the file path of the excel file.

Within the batch macro and an input tool configured reading one of your workbooks and the sheet you need.

Use the control parameter to update, using an action tool, the filepath part of the input tool, replacing everything apart from the sheet name.

Hopefully that helps.

danilang

Hi @Sriharsha-3134

Since Alteryx files are just XML, you can be able to change the version number in the macros that you've downloaded. Just open the .yxmc(or .yxmd for a standard workflow) file with notepad and change the second line from <AlteryxDocument yxmdVer="2019.x"> to <AlteryxDocument yxmdVer="2018.2"> and save it. Once this is done the macro will open in your version of Alteryx.

Note: if the macro was inside a .yxzp package, you'll need to extract it first before you can make the version number change.

Dan

ChrisTX

This is a nice Knowledge Base article about file versions:

Adjusting Alteryx Files for Different Versions
How to fix the error: This document was created by a more recent version of this application and cannot be read
https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versions/ta-p/26237

For importing Excel files when the schema / tab layout may change:

Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480
Option #3 walks through Batch Macro with file paths

Sriharsha-3134

Hi Chris,

Thank you so much for the links. The Alteryx versions information is useful and will keep that in mind from next time.

I had gone ahead and used the Batch macro given in the link you shared, however I have been getting an error (PFA), asking for a sheet name to be specified. I am sure that I am missing something but am unable to figure it out. Also I observe that the full path column in the directory tool input consists of only paths ending till filename and do not extend to the sheet name.

Thanks,

Harsha

Alteryx Error.PNG

Alteryx Directory Full path.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703