How do I ETL name-value pair data?

CloverDX Platform
1

I cannot seem to find a control or a way to ETL a flat file like this:

firstname|bob
lastname|lyons
address1|123 yellow brick road
city: Emerald City
State: AZ
zip: 34344

firstname|jane
lastname|doe
address1|54321 long street Street
address2|aprtment 5
city| Springville
State: OH
zip: 55555

firstname|jack
lastname|straw
address1| 65 Pine Way
city| Longdale
State: GA

Thanks in advance for any suggestions.

- Edd

2

actually, the data has a consistent separator:

firstname|bob
lastname|lyons
address1|123 yellow brick road
city|Emerald City
state|AZ
zip|34344

firstname|jane
lastname|doe
address1|54321 long street Street
address2|aprtment 5
city|Springville
state|OH
zip|55555

firstname|jack
lastname|straw
address1|65 Pine Way
city|Longdale
state|GA

my apologies

3

Hi eec,

1] read lines into records with metadata [key, value] (mark value as optional for separator ####)

2] use Reformat component to translate [key, value] into [id, key, value] (ignore separator lines, but increment id when occurs). So your data will look like:

0|firstname|bob
0|lastname|lyons
0|address1|123 yellow brick road
0|city|Emerald City
0|state|AZ
0|zip|34344
1|firstname|jane
1|lastname|doe
1|address1|54321 long street Street
1|address2|aprtment 5
1|city|Springville
1|state|OH
1|zip|55555

3] use http://doc.cloveretl.com/documentation/ … pivot.html for translation into records (Key=id Field defining output field name=key Field defining output field value=value). Output records must have metada like: [firstname, lastname, address1, …]

I hope this helps

4

Thank Jaroslav for the reply, that looks like a nice solution and will try that out
edd