How do I ETL name-value pair data?

eec

• July 06, 2012 00:00

• Answered

Follow

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

• 

  eec

  • July 06, 2012 16:09

  0

  Comment actions Permalink

  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
• 

  admin

  • July 09, 2012 08:56

  0

  Comment actions Permalink

  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
• 

  eec

  • July 09, 2012 14:33

  0

  Comment actions Permalink

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

Please sign in to leave a comment.