This week got a request from one of my client to process over 100 csv files into one master file with a unique schema using TOS. There were multiple challenges.

  1. The schema of each file were different.
  2. These files were placed in different folders.

Point 2 was easy to handle with subdirectory option in tFileList component, but point 1 was a monster. I searched internet for 2 days and found no solution, except this one.Even this talks about the schema order being different and not for different schema altogether.

Hence I had to find a solution for the same and eventually I did. This is an elaborate workaround, but works…

First thing First…lets talk about prerequisites.

  1. We know the max number of columns these files have. If 1 file in 100 file has 20 columns which is max, then we take that number.
  2. Download few custom components like..tUnpivotRow and tFileInputTextFlat
  3. Know how to use user components.
  4. We understand java. (A little bit)

have divided this into 4 parts.

  1. Create a column mapping
  2. Unpivot all columns except primary columns into rows.
  3. Use logic to manage order of the primary columns.
  4. load data into main file by looking up with column mapping.

Screenshot 2019-06-21 at 1.34.59 PM

Lets look at them one by one.

  1. Create Column Mapping        
  2. Unpivot        
  3. Manage Order           
  4. Data Load