Batch Macro not combining excel columns from multiple tabs into like columns

I am trying to input an excel file that has 24 tabs. Each tab has the same data in the same columns (All column A's are Data Type 1, all column B's are data type 2, etc.) End goal is to have all column A's from all tabs in the same column. Currently, the batch macro is adding columns for each tab resulting in >2,000 columns. I did have this batch macro working as I expected it to, but I had to update the source file to our new Master copy.

For Example:

Input Tab 1 Name = Bill's dogs

Column A = Owner Name

Column B = Dog Name

Column C = Breed Type

Input Tab 2 Name = Jane's Dogs

Column A = Owner Name

Column B = Dog Name

Column C = Breed Type

See screenshots attached for current vs expected result.

Please see screenshots attached for context.

Input Activity Config.PNG

Expected vs. Actual Result.PNG

Workflow.PNG

Update Value Action Config.PNG

Batch Macro

Accepted answers

jpet03

My input is an excel file. After experimenting for a while it turned out that the issue was that I did not have text in every cell in the first row, only in the columns I needed for the workflow. Once I added some dummy data to have text in the first row of each column, it seemed to work just fine.

Thanks for the assist on this and providing your insight.

All comments

Luke_C

Hi @jpet03

That definitely seems strange. One more thing to check, in the macro, what are the output settings if you go to the interface designer?

jpet03

Thanks for the reply Luke. Is that in the output action I have on the bottom right of the original screenshot? I only have the Output Name and Anchor Abbreviation fields. Thanks.

Luke_C

Hi @jpet03 no you'd find this under View>Interface Designer

jpet03

I have tried each option to no avail

Luke_C

Are you able to share some sample data?

apathetichell

what version of alteryx are you running. Can you turn off Amp? Is this 2023.1? You should be able to control what you are trying to control with schematically consistent files. I would posit - that most cases you see this kind of result it's because your field names which you think are schematically consistent ARE NOT schematically consistent. I'd recommend changing all of your fieldnames to integers and re-running. you can do this via field info/record id - you then rejoin that to your main stream using dynamic rename (with your column headers coming in from the right anchor and being mapped to your record id fields)... if you do this - save it - run your workflow - and you still see an issue (ie column mappings not reflecting 1/2/3... n - your issue is more opaque.

jpet03

attached is a sample set. Should have both the start of the work flow and Batch macro.

Thanks

Sample workflow.yxzp

apathetichell

are you using a DVW tool - if not how are you getting your data from SAP? TBH - without two file it's hard to tell what's off here - but my hunch is schema difference combined with how you are changing your column names.

Set your macro to autoconfigure by position and see what your columns look like.

jpet03

Thanks for the assist on this and providing your insight.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280