how to insert row dynamically into file? tRowGenerator not sufficient?

One Star

how to insert row dynamically into file? tRowGenerator not sufficient?

Hello !
I'm using TOS 2.3.3 in Java.
Here is my problem : I would like to get some rows from a file (tInputDelimitedFile) and insert some new rows and put all of them (row coming from the file + created rows) into an output file (tOutputDelimitedFile).
But, these new rows depends on the rows coming from the file.
According to the data from file, I will add a row or not.
for example,
I have in my input file the data :
A 0 P1
A 1 P2
A 2 P3
B 1 T10
B 2 T20
In my output file, I must have :
A 0 P1
A 1 P2
A 2 P3
B 0 UNDEFINED
B 1 T10
B 2 T20
so the line "B 0 UNDEFINED" has been added. It is added because the value 0 is required but it is not in my input file. We always put UNDEFINED in value when it doesn't exist.
I would be able to developp it in a Java routine.
But can I get the rows coming from the file in paramater of a Java function ?
Otherwise, maybe a component exists, which can generate row according to my first row ?
I've seen the component tRowGenerator but don't know how to use it ? and mostly how to particularize the generation with the data of my input file.
Could you help me ?
Thanks
One Star

Re: how to insert row dynamically into file? tRowGenerator not sufficient?

I want to know this too though have not had an immediate need to do this in Talend.
I have done this fairly easily in Informatica. Read the original file with the unique first key column (A, B, C etc. ). Then generate 3 rows for each key and put 0, 1 and 2 for the 2nd column (in INFA it uses Normalizer to make 3 rows from 1). Read the same file as another source and do an outer join match. For the 3rd column, if null (so only in the first source but not in original file), put UNDEFINED.
Talend has tUnique and tNormalizer. tMap will do the outer join. Will that work?
Sean
One Star

Re: how to insert row dynamically into file? tRowGenerator not sufficient?

Since you know B 0 doesn't exist, that is static, can't you do a outer join and assign your "undefined" for the NULL value
One Star

Re: how to insert row dynamically into file? tRowGenerator not sufficient?

I think OP wants to fill all possible holes. "B 0" is just an example. I am elaborating on my post a bit more in case it helps.
I am assuming you have all instances of the first key (A, B, C in column 1). Also you know that for each of this key, you will have 3 rows, 0, 1 and 2. Then the normalize will create these 3 rows for each unique input key.
So you read in only the unique keys A, B and C from file1 (really this is the same file but here you read only the first column)
For each key, normalizer adds 3 rows. This gives you all possible key combinations So your output is
A 0
A 1
A 2
B 0 (important)
B 1
B 2 etc.
Then you can do an outer with the file2 (where B 0 does not exist). The outer join will keep all the rows from file1 and matching rows from file2. So you get
A 0 P1 (since match was found b/w keys A 0 on file1 and file2)
A 1 P2 (same as above)
A 2 P3 (same as above)
B 0 UNDEF (no match in file2 but you keep the row from file1. Third column is NULL or you can define it as N/A, UNDEF etc.)
B 1 T10
etc.
This will fill out any holes.
Thanks.
Sean
Regards,
Sean
One Star

Re: how to insert row dynamically into file? tRowGenerator not sufficient?

Hello
Thanks for these answers.
I think you need more precision for my problem.
In fact, there is not always 3 rows for each key (A, B ... etc). I realize that my example was not complete.
I can have :
A 0
A 1
A 2
B 1
B 5
B 12
B 15
C 6
and the output would be :
A 0
A 1
A 2
A 15
B 0
B 1
B 5
B 12
B 15
C 0
C 6
C 15

the lines "A 15", "B 0", "C 0" and "C 15" has been added.
Indeed, i need to add the row with the zero but also the end of the interval if it doesn't exist (the end of the interval is known and it is always the same : in my example it's 15)
So you can see that :
1- I don't know how many lines there will be for each key, so i think i can't unfortunately use the solution with tNormalizer suggested by psm2000 (sean)
2- I want to fill several holes but 2 maximum (the start 0 and the end 15) : i didn't said it first because i thought it was the same problem that for the 0. so i don't want to fill all the holes.
asethi, i am not sure to understand what you want me to do : could you precise your idea ? which component must I use for the outer join ? and where do you define that the null value will be UNDEFINED ?
sorry, but i'm a starter with Talend.
Thanks.
One Star

Re: how to insert row dynamically into file? tRowGenerator not sufficient?

I am sorry, i don't understand the situation fully, but if you know exactly which fields should be defined, then it should be straightforward, but i am not sure of your problem sorry
for outer joins and rejects you use tMap. For switching an "undefined" in place of nulls you can use the ? : operator in java. You can see the tutorials for tmap, they are very good. I got started with that.
One Star

Re: how to insert row dynamically into file? tRowGenerator not sufficient?

asethi, maybe i'm wrong but the outer join and reject with tMap work with at least 2 input.
they are linked by a foreign key. and the rejects are the elements which are not found thanks to this foreign key.
but in my case, i've only one input file and must add the missing lines.
i don't know how to do that.
One Star

Re: how to insert row dynamically into file? tRowGenerator not sufficient?

Did you ever solve this problem? What i was trying to say is that I think you know what your missing lines should be, so you can generate a table of them and then do a join. You know for the A,B,C all 0 - 15 should exist.
One Star

Re: how to insert row dynamically into file? tRowGenerator not sufficient?

This post is very old but I want to answer for people who will search how to do it. You can use THash component in Technical.
1. Put your schema into THash output.
2. Make a line On component ok from your THash output to an other schema (an SQL input for example)
3. Make a line from your component (Sql input for example) to an other THash output.
4. Configure your second THash output to reference on your first THash output
5. From your second THash output, make a line On component Ok to a THash Input
6. Configure THash Input to reference on your first THash output
7. Make a main line from your THash Input to a log row component
8 You will notice that the row have been inserted.