Database Lookup possible?

CloverDX Platform
1

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

2

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