Search code examples
databasecsvtalend

Insert into CSV file from two tables using Talend

I have two tables command and commandItem:

command: code, date, amount
commandItem: commandCode, price, label

I would to get this output in a CSV file:

Command1;26-01-2018;100
Command1;9;label1
Command1;81;label2
Command1;10;label3
Command2;26-01-2018;50
Command2;10;label1
Command2;20;label2
Command2;20;label3

Can you tell me how to do it using Talend?

Solution

  • I don't have access to my test database at the moment in order to show you a solution with tables, so I did it with files, but the general idea is the same :

    enter image description here

    The idea here is to read your commands table, and for each row, write it to file (in Append mode); and before passing to the next row, write (to the same file, in Append mode) the related rows from items table (you need to replace the part where I read items file + tFilterRow by your database input component, containing a filtering condition like so: WHERE commandCode = (String)globalMap.get("row1.code") in order to get items for the current command only)

    enter image description here

    The settings of both tFileOutputDelimited components are identical and look like this :

    enter image description here

    You need to set "Custom flush buffer size" to 1 in order to flush each row immediately to file, otherwise the order of rows won't be garanteed in the output file.

    Output:

    Command1;26-01-2018;100
Command1;9;label1
Command1;81;label2
Command1;10;label3
Command2;26-01-2018;50
Command2;10;label1
Command2;20;label2
Command2;20;label3

    I bet there are other (better?) ways to achieve this, but it's the first that came to mind.