Pentaho : read csv file and update column from database

Question

I have a csv file with 4 column : userid, email, firstname, lastname the column email is emtpy for all user and i want to get it from database.

so i need to read this file (with CSV file input) and for each line, get the value of "userid" and create a sql request to my database to retrieve the email and put it in csvfile

[csv input] --> [Select Value] --> [Table Input]

is this correct ? because i get an error in Table input step

connection is ok, and my request is : "select email from users where userid = ?"

and i have checked "Replace variable in script" and Insert data from step : Select Values.

Thanks for any help

Answer 1

You can use the "Stream lookup" step to lookup values from another stream.

1. You need to query userid and email from the database using "Table input"
2. Use "Stream lookup" to get the email matching your key.
3. In "Stream lookup" you need to specify the lookup step to "Table input", "keys" that use to lookup and "fields" that you want to retrieve from "Table input"

Another approach you can use "Database lookup" as well