Multiple tabs different schema excel input to Alteryx

Question

Hi Community

I'm investigating how to input an excel sheet with multiple unique tabs into one Alteryx workflow. There is a lot online regarding this subject and I've found a macro that appears to work but I'm having trouble seeing my separate tabs within my workflow so that I can  start analysing them.  If there's a more appropriate solution, then I'm open to that also. I will actually will have a input file for each month so eventually need to analyses 12 input sheets (each with 5 unique tabs), which I want to consolidate using the summary tool.

In attached workflow, I can run the macro but I can't break the data down to see all 5 individual tabs in separate tools so that I can modify each one.

Thanks
Sures

Input Methods.yxmd

Multiple tabs test.xlsx

ChrisTX · Answer

Is your Directory tool returning 2 records or 0 records?  The screenshot doesn't show the Results pane.

In Windows File Explorer, it might be nice to show your file extensions.  Use View > Options > Change folder and search options > View tab at the top > un-check box for "Hide extensions for known file types".  Then click the button "Apply to Folders" at the top (and Yes in the pop-up), and click OK.  The new view setting will apply to all folders, then you can see the full file name including the extension.

See https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-List-of-Excel-Sheet-Names/td-p/367528

add a Formula Tool before the Dynamic Input to your FilePath Field
Use this formula:

[FilePath] + "|||<List of Sheet Names>"

See attached workflow

In the last 2 tools, click the button for Edit and point to an example template Sheet from one of your files.  With the Dynamic Input tool, if any Sheet from a different file has different columns, you'll get an error.

The format of sheet "Mar ME_Mkt Ops" in file 1 must exactly match the format of sheet "Mar ME_Mkt Ops" in file 2.

If the formats are not the same, you'll need to use a batch macro.

Chris

Multiple tabs different schema excel input to Alteryx.yxmd

sureslala · Answer

Hi Chris

Using the 'Directory Tool' can I bring into my canvas multiple excel files and all their separate excel tabs? I want to bring in 2 files each having multiple tabs. I was then going to use the filter tool to separate each tab?

Where have I gone wrong here pls?

Thanks

Sures

ChrisTX · Answer

I'm guessing you've seen these posts:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-Different-Field/ta-p/13370

And this one is helpful: https://www.thedataschool.co.uk/nick-jastrzebski/beer-and-loathing-using-a-batch-macro-to-import-multiple-sheets-from-excel

It sounds like you're trying to read in multiple Excel files, with multiple sheets, with a different layout on each sheet, and combine the data from same sheet names, from the various Excel files.

And it sounds like you want separate data streams for [the output of the combined data from the same sheet names]

If you feed in file names and sheet names into a batch macro, the macro will stack records on top of each other, from the same field names in the various sheets.  It doesn't sound like you want data from different sheets to be combined.

In your case, I think you should.....

Read in all of your file names and sheet names using the Directory tool

Separate your single Directory output stream into 5 separate streams using 5 Filter tools (sheet name = "Mar ME_Mkt Ops", sheet name = "Basis Curves_Instrument", etc)
Use 5 Dynamic Input tools

Alternatively, you could....

Use the batch macro like you're doing now

Hope that Alteryx imports [sheet 1, field a with data type 1] as the same data type on sheet 2.  (since the data will be stacked from different Sheets, based on field name)

Use 5 separate Filter tools to separate your one data stream (from the macro) into 5 separate streams (sheet name = "Mar ME_Mkt Ops", sheet name = "Basis Curves_Instrument", etc)

Use 5 Data Cleansing tools with option Remove null columns, to get rid of columns that "don't apply" to specific sheets.  But this could inadvertently delete a field you want to keep on a specific sheet.

Chris