How to implement an add if not available in the database in Pentaho?

Question

How do I implement, or what steps do I use to create a transformation that compares a table and a list . For example a database table name Schools and an excel file with a huge list of names of Schools.

if the entry in the excel is not seen in the database, it should then be added to the database table.

I'm not quite sure if I can use the database lookup step, it does not tell if a lookup fails. insert update step doesn't seem a solution as well, for it requires some ID value but no ID is present on the list of schools in the excel file

Answer 1

This what worked for me,

excel file --> 
select values (to delete unnecessary fields) -->
database lookup (this will create a new field, and will set null if not found)     -->
filter rows (get the fields with null output from lookup) -->
table output (insert the filtered records) 

Answer 2

Based on the information that you provided a simple join with table insert step will do your task. You can use the Merge rows step for comparing both the data stream (excel and database). The merge rows step uses the key to compare two streams and add a flag field which marks the row as new, identical, changed, deleted. In your case you would like to insert all the rows that are marked as new by using table insert step.

Please check the below links for more reference.

Merge rows, Synchronize after merge