Database Lookup possible?

rondevoo

• December 14, 2011 00:00

• Answered

Follow

Hello,
I've looked for several hours to find a solution to implement a simple database lookup.

here is a simple example of what i will do.

i have a csv file with following conent:

Product Category Subcategory
Sony Tv 42" Plasma
Samsung Tv 42" LCD
....

And a database schema with 3 Tables

Table_Product:
product_id, product_name, categoryid

Table_Category:
category_id, category_name, subcategory_id

Table_Subcategory:
subcategory_id; subcategory_name

Is it possible to read the csv make a lookup in the subcategory if the subcategory exists get the id if not write it and get the id then do the same with category and product?

Thanks for your help
Peter

• 

  psimecek

  • December 16, 2011 10:28

  0

  Comment actions Permalink

  Hi Peter,
  
  Have you tried to use lookup tables?
  http://www.cloveretl.com/documentation/ ... ables.html
  
  You can easily create one lookup table for each of your tables, as described in here:
  http://www.cloveretl.com/documentation/ ... ables.html
  
  CSV file can be read by the UniversalDatarReader component, read records then sent (e.g. via SimpleCopy component) to instances of the Reformat component - one instance per a lookup table. In each Reformat component you will need to define a custom transformation that uses a CTL function lookup in order to search for a record in DB.
  
  An example of lookup tables usage can be found at RealWorldExamples (which you can add into your workspace by New -> CloverETL Examples Project).
  
  Pavel Simecek
  
  Javlin

Please sign in to leave a comment.