Import Multiple sheets in from one Excel file

[kayogunbo]

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.

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

[pdave87]

@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]

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.

[kayogunbo]

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]

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]

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. 

Workflow attached for reference. 

[danilang]

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]

Hello,

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

[PuffinPanic]

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]

Hi, @kayogunbo 

What are you want get the output result data ? 

FileName	Joint Venture (JV)	Desc	Mapping	GL	Map
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV1	Dummy JV 1	NG - Map1
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV2	Dummy JV 2	NG - Map1
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV3	Dummy JV 3	NG - Map1
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV4	Dummy JV 4	NG - Map1
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV5	Dummy JV 5	NG - Map1
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV6	Dummy JV 6	NG - Map1
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV7	Dummy JV 7	NG - Map2
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV8	Dummy JV 8	NG - Map2
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV9	Dummy JV 9	NG - Map2
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV10	Dummy JV 10	NG - Map2
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV11	Dummy JV 11	NG - Map3
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV12	Dummy JV 12	NG - Map3
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'JV$'	JV13	Dummy JV 13	NG - Map3
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10010	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10020	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10030	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10040	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10180	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10240	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10410	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10050	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10070	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				50670	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				50720	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10250	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				60062	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				11630	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10520	Salary
D:\Load\Multi Sheet Import Example 2\_externals\1\Dummy.xlsx|||'Salary$'				10500	Salary