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

Reading multiple tabs with different schemas

pdubois003
5 - Atom

Hi guys, 

 

I am trying to import multiple sheets from an xlsx file using the dynamic input tool.

 

When i run the workflow there is an error in 'Dynamic input' saying that the schema is inconsitent with the first file.

 

I looked into the metadata and the error is due to 1 column that is considered as a different data type by Alteryx (Transaction # considered as V_String in first sheet and double in other sheet).

 

I would like to find a way to make it work without using a macro.

 

I just throw some ideas here (Is there a way to do these things ?)

-Dynamically standardize data as V_String for all sheets before import.

-Ignore error messages and still import data as V_String

-Flag sheets where there is an error and change data type to the same as template.

 

Thank you very much!!

 

17 REPLIES 17
BenMoss
ACE Emeritus
ACE Emeritus

Lets start with the question you didn't want us to ask...

 

Why not a macro?

 

Ben

pdubois003
5 - Atom

Because I want the solution to be as simple as possible as I will do a demo to new alteryx users.

BenMoss
ACE Emeritus
ACE Emeritus

We actually use this kind of problem to demo batch macros and it forms part of our training on this subject.

 

If this is for demo purposes and you want to simply show off the dynamic input capability, then as you have hinted at, your only options are to ensure every tab has the same schema, then you will be good.

 

Otherwise, there is no option but to macro.

 

Ben

pdubois003
5 - Atom

Do you have the exact macro I should be using to achieve this ? Thank you very much for your help!

BenMoss
ACE Emeritus
ACE Emeritus

There is a macro pack called the 'CReW macro pack', it's extremely popular with Alteryx users and contains a number of macros that many people use on a day to day.

 

One of those macro's is the 'wildcard xlsx' which does what you need. So perhaps you could use this as a demo of using external sources to help with your analysis.

 

But if you wish to demo the functionality itself it's relatively straight forward; I assume you are at a point where you have the full paths for each tab in the workbook.

 

so, for example,

 

'C:\Users\BenMoss\Desktop\Superstore.xlsx|Orders2019'

 

and

 

' C:\Users\BenMoss\Desktop\Superstore.xlsx|Orders2018'

 

If so, then it's a really straight forward process; the macro should look like the image below. I have highlighted key confiugrations in red...

 

2019-03-19_15-55-21.png

 

And here is an example of how that can be intergrated into a workflow (of course you shouldn't use a text input for the paths and instead make a dynamic process, but as mentioned, this is the point at which I Was building from)...

 

2019-03-19_16-00-38.png

 

I've attached this simple macro to the post for your benefit.

 

In order to view the results you MUST have a browse tool on the output anchor, otherwise only one record will come out.

 

Ben

shoedog
5 - Atom

Hello

 

I have a very similar situation except I am trying to consolidate 14 out of the 15 tabs (sheets) into one sheet.  I receive the different schema error when I apply the file with the dynamic input.  I am trying the batch macro but the output just spit show the 14 sheet name and the browse show no data.  

 

How do I get to one consolidated sheet for the 14 sheets?  

danchips24
7 - Meteor

How do you get this macro to work when you have multiple sheets within one excel that have different schema as opposed to completely separate excel files that have different schema?

cduepner
5 - Atom

Hello- I feel like I'm in a similar boat and I am unclear how to utilize this solution.  I have a spreadsheet with 4 tabs, I want to bring those 4 tabs together, but am running into different schema error (which I suspect is stemming from how the field types are coming through).  Any suggestions?

DanM
Alteryx Community Team
Alteryx Community Team

@cduepner and @danchips24,

 

You can accomplish this by setting the Input tool to read in the "Import only sheet names" and having the file name as a field. You will then use a Formula tool to format the file name with the sheet name extension as you seen in the Input tool when bringing in one sheet. You will then use Ben's example and have the control parameter update the file name along with the sheet.

 

DanM

 

Labels