Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Select Excel File and Import Different Worksheets

32bit
8 - Asteroid

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

 

ACCOUNTTrade_Type
123Type 1
456Type 2

 

MapName2

 

DRFTrade_Type_SummaryICI_TB_PrincipalICI_TB_InterestICI_TB_PnLSAP_TB_PrincipalSAP_TB_InterestSAP_TB_PnLA / L
BCBC2M45287-002M35296-012M50799-02100000000200000000300000001Asset
FP2BC2M52741-002M12345-012M50799-02100000001200000001300000002Liability
2 REPLIES 2
mmenth
11 - Bolide

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
8 - Asteroid

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!

Labels