Import multiple excel tabs from multiple files with different schema

[Sriharsha-3134]

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

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

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/t...
   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

[ChrisTX · Accepted solution]

See the solution on this page:

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

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

[Sriharsha-3134]

My problem has 2 parts, first part was to extract sheets from multiple workbooks with different schema which is solved. The second part of the problem is I need to make a single excel workbook with all the sheets that have been extracted from these multiple workbooks. The sheets have to be placed as individual sheets in the workbook. Is there a way to do this?

[ChrisTX]

To output data into one Excel file, separate sheets with different formats, see this post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Output-to-Excel-Multiple-sheets-in-a-s...

[Sriharsha-3134]

Hi Chris, 

Your solutions have been perfect and really helpful. Thanks for all the guidance, really feel good with all the support, However,I still have another question, how would I be able to link my input batch macro to the output workflow? Would be really helpful.

Thanks,

Harsha 

[ChrisTX]

Right-click on your workflow and choose Insert > Macro (at the bottom of the list)

Your macro will need to be located in one of the folders listed here:

   Options > User Settings > Edit User Settings > Macros tab

If you're just starting to use Alteryx, there are many learning options on this web site, under Menu > Academy

Attached is a list of web pages that may be helpful