Help consolidating multiple .csv files with similar data but different column headers

Question

Hi Alteryx Community,

I'm struggling to consolidate multiple CSV files into one file. I'm testing it with 2 files to begin with but would need to scale it to 100+ files. Having an automated solution is what I'm trying to achieve. The files have similar data but the issue is that they don't have any consistency in column headers. I.e. in one file a column is named "percentage complete" and in another file its named "%complete" but they're essentially the same field and contain the same data type. This is the case throughout all the files. I've created a translation file that follows the below structure:

* (File 1) Percentage Complete = Percentage_Completed
* (File 2) %Complete = Percentage_Completed
* (File 1) Start = Start_Date
* (File 2) Startdate = Start_Date
* File 1) End = End_Date
* (File 2) Enddate = End_Date

...

Each file also contians the key columns needed for consolidation activity but there are also other unique columns that aren't necessarily needed.

I want to standardize the column headers using the translation file and then stack all the data plus an extra column that shows which file the data came from.

Has anyone been able to do this before in an automated way or have any suggestions on how to solve this? Thanks in advance 🙏🏼

abacon · Accepted Answer

@Fdata This workflow does what you are looking or I believe. You will need to open the macro and map your translation file to replace my text input, I would format the file as I have formatted my text input file though.

What you are doing is using a batch macro to get the data from each CSV in the directory, then you use a cross tab tool to get the column headers in one column and the data in the other. Join it to your lookup table to find the old values, replace with then new values, then transpose the data back into a table before it is sent back into the main workflow.

If you have any questions, please let me know and if this solution works for you, please mark it as a solution so others may find it faster.

abacon