I need to merge an excel file with same data on all tabs but the column order is different. I did try dynamic input tool but it merged based on position.
Hi @Mario36, it's funny that the dynamic input tool doesn't have an option for configuring based on name. Maybe this is something you could submit as a suggestion? I was able to solve your issue by outsourcing the Union portion of the tool to a macro. See the attached workflow. Basically, the macro does the work of bringing in each sheet, but then the macro output is stacking the data (specifically by name).
There is an idea for this topic, if more people could upvote it Alteryx may consider it.
https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Read-in-multiple-files-tabs-with-different-field-schema/idi-p/427448
Hi @Mario36 ,
I built a couple of tools to do this.
The first reads the Excel tab names, these are then added to the fullpath and the second tool loads them all in and merges them according to the column names.
I hope this helps,
M.
The Batch Macro approach highlighted in this article should help as you can merge by name and postion.
Thank you everyone for your solutions. What if one or more columns have the same data but the column names are a bit different from the rest.
Sheet 04 on the attached excel is the one I wanted to be merged.@alexvornsand Your solution wasn't able to append all the data. I believe they appended only the common items.
@mceleavey Your solution helped. Could you assist in resolving the above block ?
If your column names are different then you will need to rename them. There's no way for Alteryx to know they're the same.
If your columns are named consistently different then you can use a mapping input and the Dynamic rename tool to rename.
However, you will need to split them on filename rename, then union.
This is a stark example of Excel being a really bad data source.
@Mario36 if you knew that you only one column of each of several data types, you could use the Field Info tool to identify which columns contain which pieces of information. Then you could join on the column types to a mapping dataset which relates column types to standard names, which would get you a crosswalk between your old column names and your standardized ones. Finally, you could use a Dynamic rename tool to update the names. This feels like a lot of work that isn't very generalizable, though. Probably it makes sense to re-evaluate the data at the source.
Additionally, in this example, you're going to have a hard time differentiating between your Cost and Quantity columns.
This helpful video from @robcarroll provides great instruction on how to use macros for anyone not familiar. It helped me after spending quite some time wasted on these treads. Macros can be very confusing as far as steps and tools needed in sequence.
https://www.youtube.com/watch?v=MjGxzsMJINQ
Last year I followed the instructions in the video linked above. It was really good. Now I wanted to revisit it but it is no longer available. :-(Why? Please bring it back.
