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.
- The schema of each file were different.
- 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.
- 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.
- Download few custom components like..tUnpivotRow and tFileInputTextFlat
- Know how to use user components.
- We understand java. (A little bit)
have divided this into 4 parts.
- Create a column mapping
- Unpivot all columns except primary columns into rows.
- Use logic to manage order of the primary columns.
- load data into main file by looking up with column mapping.
Lets look at them one by one.