Reading multiple tabs with different schemas

[pdubois003]

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!!

[cduepner]

Thanks Dan-  I'm still a little lost...I'm sure there is something really obvious here and I have just been looking at it too long and am not connecting the dots the way I should be.  I added the formula tool, how do I need to format this? 

[DanM]

@cduepner

your file name and sheet name format need to look like this - Sheet Test.xls|||`Sheet1$`.

So your expression would be [file name]+'|||'+[Sheet Names]

you would then send that to the macro where it would update those same variables, but use the same file path.

[Zenani]

can you please change the version of the workflow I am unable to open it

[cduepner]

Hi @Zenani - I had only provided a screenshot of the workflow to illustrate the input settings.  I could not attach the actual workflow as the input file had sensitive information in it.

[SUSHANT_SIDHA]

It is only loading first sheet and if possible can you explain where to look for output.

[cduepner]

The visual for this is just a screenshot, the actual input contains sensitive information, therefore I cannot post it online.

[nickjermer]

I leveraged this exact macro. After updating to Alteryx v2019, this macro no longer works... at all... It just returns one line from the first file. How can we build this macro for this use case in 2019?

[gteuten]

Hi Ben,

I found your macro really helpful for aggregating all of my data, but unable to figure out that caveat of the browse tool. In the browse tool, I can see the data, and others tools note the records are moving through the workflow; however, I cannot see the data when exported or in any other tool. I've tried exporting the data directly from the browse tool results, converting the browse tool to an output tool, etc. Do you know how I can get the aggregated data back to a normal, viewable format?

Thanks for your help!