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
@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
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.
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.
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!
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.
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
Hello,
Sorry but i am unable to import your workflow, please help
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.
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 |