Different Header Different Schema Problem

Question

Hi Alteryx Community,

I'm looking for a macro to help clean up some Excel files. Each file has different headers and columns and each of the file consist of 1million line item, which makes it hard to work with them.

Here's what I need the macro to do:

* Match columns that are similar across files with different headers.
* Make all the headers the same.
* Deal with any columns that are missing or extra.
* Clean up the data, like removing spaces and fixing formats.

I know this might be a bit tricky, but I'm sure there's a way to do it with Alteryx.

I've attached a sample input file and what I want the output to look like. If you have a macro that can help or any advice, please let me know.

Thanks for your help!

Book1.xlsx

PorcedanDyedrops · Answer

Hi Qiu,

Thanks for the macro, but it didn't fix the header names. They're still different in each file. I need something that makes all the headers the same.

Any other ideas?

Qiu · Answer

@terrellchong 
You put your input data into one Excel Sheet, which makes it more difficult.

Here is a macro should help with multiple files import for dealing different schema.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Import-Multiple-sheets-in-from-one-Excel-file/td-p/840836