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

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


Accepted Solutions
Community Manager

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.


All Replies
Community Manager

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

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!

 

Community Manager

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.

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 

 

Community Manager

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/

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch