Combine multiple excel (month-end) files with Different Schema and different field names

[goutdelete]

Hi All,

I'm pretty sure similar questions have been asked before somewhere; however either I'm still too new to Alteryx or just incapable of understanding some of the discussion so I haven't yet to succeed here even I tried to mimic some of the discussions.

I have some sample files here to illustrate the task at hand:

- A handful of month-end file to combine all data into Alteryx workflow for further analysis

- All the report run date is on the file name (as sample files)

- Over the time some more elements (fields) may be introduced so not all file have the same column counts

- Over the time certain field name may have a different convention (or just because different people did the excel work and called it differently, in this sample case the first column can be either called library card number, account number.. etc it all refers to the same thing)

- Sometimes even same filed name may have slightly different schema, some office would use numbers for account number while some may use text.

The ideal result file is to conbime everything into one big sheet/ table, with a new column to refer to the report date (from the file name).  Since the data points were increasing over time, it's ok to have certain elements empty for those prior months.

Any chance someone may be able to help me out please?

Thanks in advance!

[AngelosPachis]

Hi @goutdelete ,

You will need to create a batch macro for that. Try searching for something between the lines of "build a batch macro to read files of different schema" and you should find quite a lot of content in the alteryx community but from external websites as well.

Hope that helps,

Angelos

[bensilv]

Hi @goutdelete 

This may give you exactly what you need:

• https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

Ben

[binuacs]

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi...

[neilgallen]

@goutdelete In this situation a batch macro (as outlined above) would be your best bet, however you're in a pickle with the different column names. A batch macro would be capable of accounting for a different order of fields, but if they are named something different (account number, library card number, etc) then there is not a simple method to work around this.

You could either manually check the input files and correct field names, or there may be a more elegant solution using the data profile tools but I can't work one out at the moment.

Good luck!