I have a situation where I am trying to parse data in a flat file that has varying column names.
Simplified example of the data: Sample 1 A|John|B|123 Lane|C|345|D|765|E|Age30 Sample 2 A|John|B|123 Lane|C|345|E|Age30
I am loading the data like this and it is working for this part:
Load textbetween(@1,'A|','|C') as Name and Address, subfield(@1,'E|',2) as Age From Table;
However, I want to load the data after 'C|', the value of 345 in this case, as a field and if the data after 'D|', but before '|E' exists, I'd like to load that as a field as well, and have it null if it doesn't exist.
Does anyone know how to solve something like this? I'd appreciate any suggestions. Thanks!