Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Import Multiple sheets in from one Excel file

kayogunbo
7 - Meteor

Dear experts,

 

I need your help, i am new to Alteryx and so learning things on the fly. Done several research on this topic and i have not been able to get a clear way of resolving this.

 

Situation:

I have an excel file that contains several worksheets and i would like to import the file into workflow once and be able to access the sheets. This excel file have several master data that would be linked up to other data in my transactional data.

 

The imported sheets will then be used as part of other business logics that i am working on.

 

kayogunbo_0-1635556877607.png

 

 

The alternative is for me to create multiple input points and import the sheet one after the other but i have a feeling that there might be a better way of doing this hence my posts here.

 

Note: The schemas are not same and i do not want the data merged on import.

 

Many thanks!

 

Kay O

9 REPLIES 9
pdave87
11 - Bolide

@kayogunbo thanks for posting this question.

 

I have few questions:

1. How many sheets are we looking at from the source data?

2. What is the structure of each sheet? Is it same column names but different column sequence? Or each sheet with different column name i.e. it has totally different structure?

 

My initial suggestion is to import excel file with multiple worksheet as a table with - List of sheet name & full file path. Replace filename in the new column with sheet name (with full file path). This will be the preliminary step to pull the source input if we wish to solve through batch macro to generate a common output across all sheets.

 

If the structure of each sheet is different then it's a different discussion and we will be happy to take a look at dummy reference data.

 

Hope this helps.

 

Regards,

Pratik

mceleavey
17 - Castor
17 - Castor

Hi @kayogunbo ,

 

I've attached a workflow and some tools that do this for you.

Given what you are trying to do you will need to load them all in this way, then filter them to each sheet, then use a cleanse tool on each to remove the unwanted columns.

 

Hope this helps,

 

M.



Bulien

kayogunbo
7 - Meteor

Thanks Pratik. The structures are different. I have attached a sample Excel file.

 

The are master data file that are consolidated into a single workbook instead of having them scattered all around. Currently, i have 2 sheets and it could likely increase before i finish building the workflow..

 

I hope this helps clarify what i am trying to do?

 

Many thanks!

Kay O.

kayogunbo
7 - Meteor

Hi @mceleavey,

 

Thanks for the sample workflow. It ran but it didnt output anything that i can use.

 

Can you help check with the dummy data that i attached?

 

Many thanks!

Hammed
7 - Meteor

Hi @kayogunbo

 

You could do with a macro but the issue with this is that macro always stacks or unions sheets in the file. A walkaround will be to read the file full path when configuring the macro, then perform a few transformations and pick up the sheet you wanted with the filter tool. 

Hammed_0-1635666448328.png

 

Workflow attached for reference. 

 

 

danilang
19 - Altair
19 - Altair

Hi @kayogunbo 

 

If you don't want the data merged on input, and the sheet names in your input file don't vary, the best way to do it is to use a separate Input Data tool to read in each of your sheets.  This follows the K.I.S.S. principle and avoids adding any unnecessary complexity to the workflow.  You can use annotations to label each of the inputs to make it clear what they are and also perform any clean-up on a sheet-by-sheet basis, before moving on to the main part of your workflow

 

The other, dynamic methods are useful if you don't know what the the sheet names are before hand, but they all add complexity to the workflow and have the drawback of merging the data in some way.   If you have any columns in the master data sheet with the same name, i.e. ID, Value, etc. these will be merged and assigned a type that covers all possible cases.  If the data from one ID column is a string and the other is numeric, the unioned output will be a string.  You can split the data based on sheet name, and use the Autofield to convert the fields, but you may get small but significant differences in field length and type, esp, String vs. V_String.

 

Dan

khushb373
5 - Atom

Hello,

 

Sorry but i am unable to import your workflow, please help

PuffinPanic
9 - Comet
9 - Comet

Hi,

 

This workflow has been a life-saver for me, but now I've got a problem that I'm struggling to solve.

 

I have updated the workflow slightly so that only the excel workbooks I want go into the macro, but otherwise it's the same. 

The problem I'm having is with a worksheet which has 4 tabs to be processed, and the 2nd macro is only processing 2 of the tabs.

The tabs are named:

a - evaluated

b - evaluated

a additional

b additional

 

The 'additional' tabs are both processing, but neither of the 'evaluated' tabs are. 

 

I'm sorry I can't share the files or workflow itself, but if you have any suggestions as to where I should look to try to resolve this issue, I'd appreciate it.

 

Thanks

 

PuffinPanic.

flying008
15 - Aurora

Hi, @kayogunbo 

 

What are you want get the output result data ? 

 

录制_2023_12_21_09_29_14_783.gif

 

FileNameJoint Venture (JV)DescMappingGLMap
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV1Dummy JV 1NG - Map1  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV2Dummy JV 2NG - Map1  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV3Dummy JV 3NG - Map1  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV4Dummy JV 4NG - Map1  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV5Dummy JV 5NG - Map1  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV6Dummy JV 6NG - Map1  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV7Dummy JV 7NG - Map2  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV8Dummy JV 8NG - Map2  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV9Dummy JV 9NG - Map2  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV10Dummy JV 10NG - Map2  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV11Dummy JV 11NG - Map3  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV12Dummy JV 12NG - Map3  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'JV13Dummy JV 13NG - Map3  
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10010Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10020Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10030Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10040Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10180Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10240Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10410Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10050Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10070Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   50670Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   50720Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10250Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   60062Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   11630Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10520Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'   10500Salary
Labels