Five Stars

how to import 1st row value (Title) into all rows

Hello, 

I'm trying to figure out if it is possible to import 1st row value (cell A1) into all the rows in a separate column.

I am importing a CSV into MySQL with the csv being in the following format:

 

Hello World! 
  
Namecode
John 1
Moyya2
Sidi3
Omenk4

 

desired output in MySQL:

 

NamecodeTitle
John 1Hello World!
Moyya2Hello World!
Sidi3Hello World!
Omenk4Hello World!

 

is this possible to do in Talend ?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars

Re: how to import 1st row value (Title) into all rows

Connect your data source (I'm assuming a tFileInputDelimited) to a tMap.

I'll assume you have two columns defined; "ColA" and "ColB".

 

"ColA" will have the "Hellow World!" in the first row.

 

Hello World! 
  
Namecode
John 1
Moyya2
Sidi3
Omenk4

 

In your tMap create a tMap variable of type int called "count". In the Expression for that variable, put the following code....

 

Var.count!=null ? Var.count+1 : 1

That is essentially keeping a count of your rows. There are easier ways of doing this, but this is teaching you something useful about the tMap variables (they keep their values between rows).

 

Now create another tMap variable called "Header" of type String. Ensure this is below the "count" variable (variables are processed from top to bottom). Now in the value expression of this you want to assign it the value of "ColA" ONLY for the first row. So you do the following (making use of the "count" variable).....

Var.count==1 ? row1.ColA : Var.Header

Here you are saying, if the count is 1 then set the value to be that of ColA. However if the value of count is not 1, set Header to be set to itself.

The rest is easy. Simply create another column in your tMap output and assign the value .....

Var.Header

Done.

Rilhia Solutions
5 REPLIES
Twelve Stars

Re: how to import 1st row value (Title) into all rows

Hi, this is essentially what I answered in another thread today.....

https://community.talend.com/t5/Design-and-Development/File-Handing/m-p/28104#M5731

Rilhia Solutions
Five Stars

Re: how to import 1st row value (Title) into all rows

Hi Rhall, 

Thanks for the reply, 

I had a look at the other thread but still no idea on how to proceed.

I am fairly new to Talend so excuse me if the answer is obvious in the other thread!

 

Twelve Stars

Re: how to import 1st row value (Title) into all rows

Connect your data source (I'm assuming a tFileInputDelimited) to a tMap.

I'll assume you have two columns defined; "ColA" and "ColB".

 

"ColA" will have the "Hellow World!" in the first row.

 

Hello World! 
  
Namecode
John 1
Moyya2
Sidi3
Omenk4

 

In your tMap create a tMap variable of type int called "count". In the Expression for that variable, put the following code....

 

Var.count!=null ? Var.count+1 : 1

That is essentially keeping a count of your rows. There are easier ways of doing this, but this is teaching you something useful about the tMap variables (they keep their values between rows).

 

Now create another tMap variable called "Header" of type String. Ensure this is below the "count" variable (variables are processed from top to bottom). Now in the value expression of this you want to assign it the value of "ColA" ONLY for the first row. So you do the following (making use of the "count" variable).....

Var.count==1 ? row1.ColA : Var.Header

Here you are saying, if the count is 1 then set the value to be that of ColA. However if the value of count is not 1, set Header to be set to itself.

The rest is easy. Simply create another column in your tMap output and assign the value .....

Var.Header

Done.

Rilhia Solutions
Five Stars

Re: how to import 1st row value (Title) into all rows

Hi Rhall, 

Great explanation and very clear example in understanding the use of variables of Tmap! It achieved the desired result.

You mentioned that there are easier ways of achieving the same result; if it's not too much to ask, I'm also interested to know what are the other approaches to solve the same problem.

Thank you 

 

Twelve Stars

Re: how to import 1st row value (Title) into all rows

I was really just talking about the sequence "count" when I said that. There is a Talend routine called "Numeric" which has the method "sequence". This makes it much easier to carry out counts over rows. This is described here http://talend.tips/2016/09/20/quick-tip-passing-sequence-row-numbers/

Rilhia Solutions