topic Excel file to multiple MySQL tables with conditional logic in Talend Studio

Excel file to multiple MySQL tables with conditional logic

Anonymous — Sat, 16 Nov 2024 13:11:16 GMT

Hi,
My client has decided to use Talend Open Studio as a data import tool for some of their systems, and have asked me to investigate suitability.
So far I have been able to import CSV file into a single MySQL table. However, I'm completely at a loss as to how to do the following:
- open an Excel file
- iterate through the rows and populate Table A in a MySQL database with some of the fields. IF recrod is new, insert, if not update, EXCEPT where an incoming field value is NULL
- for each row, also populate some child tables, using the primary key of the row above as the foreign key, and doing the same INSERT / UPDATE regime as above
I have tried so many things but can;t figure out the child table or the conditional branching for Insert Update and for NULL / NOT NULL
I would really appreciate guidance on this - ie what items to use, in which order and what triggers to fire off (if any? )
Many thanks in advance

Re: Excel file to multiple MySQL tables with conditional logic

Anonymous — Tue, 07 Dec 2010 13:41:06 GMT

can u give the detail description of ur issue please
asi am nt able to understand what u want to say

Re: Excel file to multiple MySQL tables with conditional logic

Anonymous — Tue, 07 Dec 2010 15:54:07 GMT

most of the dbOutput components contain a field: "Action on data" This can take care of your update/insert logic.
you can use a tFilterRow or a tMap output filter to separate out NULL rows.
if you are generating your keys with sequences/autoincrement, you can insert child rows by using the "Additional columns" in the advanced settings of your DB output.

Re: Excel file to multiple MySQL tables with conditional logic

Anonymous — Tue, 07 Dec 2010 16:29:43 GMT

most of the dbOutput components contain a field: "Action on data" This can take care of your update/insert logic.
you can use a tFilterRow or a tMap output filter to separate out NULL rows.
if you are generating your keys with sequences/autoincrement, you can insert child rows by using the "Additional columns" in the advanced settings of your DB output.

Many thanks John,
I'll look into these in more detail and try to move things along.
Just one thing, where you say I can use a tMap to filter out NULLs, are you saying that a tMap can conditionally pass a value across to the mapped destination? That's pretty clever if so

Re: Excel file to multiple MySQL tables with conditional logic

Anonymous — Wed, 08 Dec 2010 17:57:42 GMT

@Sparrowhawk
That is exactly what I'm saying. In the attached screenshot I've highlighted the buttons in the tMap that allow you to do this.