load and standardize 100 files that have inconsistent headers, schemas, sheet names

[AdminYogesh]

Hello Community members,

I am working on how to load and standardize 100 Source input files that have inconsistent headers, schemas, sheet names, and file formats, but contain the same underlying data, into one unified table in Alteryx.

I have added  #3 sample Dataset for Working purposes.


I would appreciate it if I could get any help from you guys!!

[davidskaife]

Hi @AdminYogesh 

Here is a way of doing it, utilizing Batch Macros to read in the files - split out into one for xlsx files and one for csv - this runs on the assumption that all the csv will be the same, and all the xlsx will be the same, but you can adjust accordingly.

Have annotated the tools accordingly.

[AdminYogesh]

Hi @davidskaife, but the field name should be the same in the final output. If you can see that 3 different files have different field names, and that should be dynamic. Change: this will be a common field
(Employee ID
 First Name
Last Name
Department
Salary
Start Date )

[davidskaife]

Hi @AdminYogesh 

In that case simply rename the columns to your desired headers in the final Select tool