Excel Sheets Different Schemas _ Help Understanding "The Ultimate Input Doc"

[sslattery17]

I have read the following - https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/The-Ultimate-Input-Data-Flo... ; however, I still have not been able to resolve my issue with sheets being different schemas.

1. I have tried using Formula with Output column FullPath and the following [FullPath]+"|||Sheet1$"

Each month client sends reports we audit; sometimes their sheets are named with property name (Madera at NorthBank) other times just sheet 1 as an example.

2. Cannot seem to get the maco configured to work either..

I am 3 weeks into using this tool figured out a long watching videos and reading.  But I am lost on this one. Any additional guidance would be great.  

[apathetichell]

search on this forum for how to open multi-sheet excel files using a batch macro.

[sslattery17]

The Data School - How to import files with different schema using a Batch Macro

 I have followed these instructions, and I get an error on the action tool Error: Different File Schema Macro (32): Record #6: Tool #4: The Action "ToolId:4" has an error: The filename or connection string does not identify a known format. Check all parameters.

This is a similar error I received before with the batch macro i built.  Can you tell me if anything was left out of the instructions attached. 

[binuacs · Accepted solution]

@sslattery17 use batch macro if your input files are having different schema, also you need to two batch macro one for reading the sheet names and another for combining all the sheet information. Sample workflow attached

[sslattery17]

Okay I will try this first thing.  I have looked at this so much and tryed so many different ways to get it to work; I am spinning.  I will let you if I get it to work.  SS

[sslattery17]

@binuacs 

Thank you.  I understand the batch maco sent. The issue I am still having is the macro is working for a single excel file with multiple sheets but I have 30 files different schemas all with the 1first sheet being the property name.  When I create the connection its only bringing in one of the files and only finding the 1 sheet with that listed property name.  I am missing something in the instructions or understanding somewhere. Should the input be a dynamic input in my situation?  

 

[sslattery17]

Okay I figured out the batch macro and it seems to be working but not sure about the last result of the second part of the maco.  The last column of the results window shows List of Sheets Names  Should this be something different?  On the second macro; its set to FileName if I set it to full path or sheet name there are errors and no data is returned. 

 

 

[binuacs]

@sslattery17 Are you using same batch macros in your workflow? seems to be you are using the same batch macro which gets the sheet name . Can you check and confirm? also make sue your second macro settings like below

[sslattery17]

Yes I am using the same batch macro you sent updated the connection since I am on a network drive.  I have tried using file settings with and without updating my network path...

  

[binuacs]

@sslattery17 that error is fine, what is your action tool settings