Varying Columns in a Load

[melissapluke]

Hi all,

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!

[DavidM · Accepted solution]

Is the | always separator and the format doesn't change? If yes, for getting C value you can use:

subfield(@1,'|',6) as C

and for determining if there is any D value:

if(wildmatch(@1,'|D|'), subfield(@1,'|',8),0) as D