Hi,
I'm struggling to get one of our ETL jobs working as flexibly as is needed.
We have a data file like so:
A,B,C,D,F1,F2,G1,G2,H1,H2 and so on (In reality there are 9 columns per "set" rather than 2 in this example)
There are an arbitrary number of column pairs on the end and is different on every row.
We essentially want to produce something like this:
A,B,C,D,F1,F2
A,B,C,D,G1,G2
A,B,C,D,H1,H2
I've tried using tFileInputRegex to produce A,B,C,D,pair_string columns and then used tDenormalize but the tDenormalize component only appears to use 1 column so we'd end up with:
A,B,C,D,F1
A,B,C,D,F2
Using a really wide table and then using split row is fine, except that we have to "hard-code" a maximum number of pairs.
It feels like I need some sort of tNormalizeMultipleColumns or tExtractRegexFields_Recursive component to do this.
I'm hoping I've missed something obvious and someone can just go "you need to whack together X,Y,Z and it'll work fine!"
Help!
Thanks,
Rob