Select Excel File and Import Different Worksheets

[32bit]

Hi everyone,

This is my first post here and I'm a new Alteryx user. My background is creating apps in C#/SQL Server and Access/Excel with heavy use of VBA.

The way that I want to use Alteryx is to create analytical apps where a user will select certain files to produce a certain output. Pretty straightforward, but I'm running into a roadblock on this one issue. I usually have one Excel file called MappingTables.xlsx which can have 1 to 10+ tabs which contain different data. I want the user to be able to select one file and it reads all in. All of the examples that I can find only deal with multiple worksheets where the tabs contain the same headers/data to combine into one. This isn't like that.

The company firewall won't allow me to upload files, so below are a couple of examples with randomized data to illustrate 2 tabs. Their content really doesn't matter.

What I want to accomplish is each tab is read into a separate table. The user should not be prompted to select a tab name or have to select "Import only the list of sheet names." 

I see that I can create my own tools using the API and SDK to help with this, but I would prefer to see if Alteryx has an elegant solution using the built-in tools or macro ability. Any pointers in the right direction is appreciated. I'd be thrilled with maybe a link to a previous solution or something. I haven't been able to find anything.

Thanks so much for your time.

MapName1

ACCOUNT	Trade_Type
123	Type 1
456	Type 2

MapName2

DRF	Trade_Type_Summary	ICI_TB_Principal	ICI_TB_Interest	ICI_TB_PnL	SAP_TB_Principal	SAP_TB_Interest	SAP_TB_PnL	A / L
BC	BC	2M45287-00	2M35296-01	2M50799-02	100000000	200000000	300000001	Asset
FP2	BC	2M52741-00	2M12345-01	2M50799-02	100000001	200000001	300000002	Liability

[mmenth · Accepted solution]

Hi @32bit,

In general when you're trying to load in multiple sheets at once with different schemas you'll have to use a batch or iterative macro, as the dynamic input tool will try to union everything and will throw errors. Batch and iterative macros will do the same thing, but there is a setting in which you can say that the output data may have different schemas. Attached is an iterative macro I made that does this, you'll probably just have to parse the output to make sense of it.

Best,

mmenth

[32bit]

Hi @mmenth

Thanks for posting that. It's an interesting solution to combine multiple mappings into one big table with different columns. I added an Append Fields tool to the end of the macro to add a column for the worksheet name. That could prevent problems with mapping tables having the same column names. I appreciate it!