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

I have read the following - https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480 ; 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.

Screenshot 2024-08-20 125811.png

Workflow

Accepted answers

binu_acs

@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

Multiple_Sheet_20082024.yxzp

All comments

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.

binu_acs

Multiple_Sheet_20082024.yxzp

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?

Screenshot 2024-08-21 045317.png

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.

Screenshot 2024-08-21 054244.png

binu_acs

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

Screenshot 2024-08-21 075844.png

Screenshot 2024-08-21 075943.png

binu_acs

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

sslattery17

I have set the action tool to match the path and tried it both ways as noted no data is pulling

Screenshot 2024-08-21 081325.png

binu_acs

@sslattery17 it should work, are you getting the full file path from the first macro?

sslattery17

It appears so...The fp shows the sheet names....also just to confirm; the second macro should have the path where all the files are correct? so I am assuming the File1 sheet 1 is looking for the first on the file directory and should be bringing in the columns in the sheets?

Screenshot 2024-08-21 083041.png

sslattery17

It appears so...See below.

The fp shows the sheet names....also just to confirm; the second macro should have the path where all the files are correct? so I am assuming the File1 sheet 1 is looking for the first on the file directory and should be bringing in the columns in the sheets?

Screenshot 2024-08-21 083041.png

binu_acs

that is correct, in the second macro are you passing the fullpath as argument

sslattery17

Yes. It is pulling an error for one of the files.

Screenshot 2024-08-21 085843.png

binu_acs

Can you make sure file which is showing in the error message is not corrupted

apathetichell

Is that error at runtime or on canvas? Look at your browse tool going into the macro - do you have that file/sheet combo? If the error is occuring because the original file the macro was set up with uses that file/sheet - and you (the user) do not have it - it should clear once run.

sslattery17

Sorry for the delayed response. Network went down.

Issue Resolved. Thank you again for your support, time and help.

The sheet was not corrupt, for some reason the data cleansing left a space at the end of that sheet name so manually deleting it; resolved the error and the pathway error resolved by putting all the remaining workflow in a disabled container.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335