kettle / auto-referencing table

I have an excel sheet with poeple, each people has a father and a mother that is in the same poepl开发者_运维知识库e sheet. My exel table looks like that :

poeple --- father --- mother

john -------- tony ----- jane

tony -------- jack

I would like to import the datas to an Oracle database table that look like :

id --- poeple --- father --- mother

0 -----jack

1 -----tony-------- 0

2 -----jane

what should be my workflow ?

3 ----john -------- 1-----------2

It would be easier to at least start by loading the data into a table with the surrogate ID:

people father mother
------ ------ ------
john   tony   jane
tony   jack

Then you can add rows for the fathers and mothers not already in the "people" column:

insert into mytable (people)
( select mother from mytable
  union
  select father from mytable 
)
minus
select people from mytable;

That will give you:

people father mother
------ ------ ------
jack
tony   jack
jane
john   tony   jane

You can then add a surrogate ID for each row and use that instead, if you need it.