One Star

Split a single excel column in to multiple columns based on condition!

Hello I am having an issue in spliting an excel column into columns and attached an example and any help is apreciated.
N.B -Consider the empty cells are there to separate countries.
- also Country and date are in one cell.
-some countries have one link the other have two or three links.
Best Regards,
Biruh

Best Regards,
Biruh
2 REPLIES
Community Manager

Re: Split a single excel column in to multiple columns based on condition!

Hi Biruh
Below is an example job to implement your request.
start code on tJavaFlex:
// start part of your Java code
int index1=0;
int index2=0;
String country="";
String date="";

main code on tJavaFlex:
// here is the main part of the component,
// a piece of code executed in the row
// loop
index1=out1.newColumn.indexOf("(");
index2=out1.newColumn.indexOf(")");
country=out1.newColumn.substring(0,index1);
date=out1.newColumn.substring((index1+1),(index2-1));
globalMap.put("country",country);
globalMap.put("date",date);

Result printed on the console:
Starting job forum26293 at 09:29 26/09/2012.

connecting to socket on port 3337
connected
.----------+---------+------------------------.
| tLogRow_1 |
|=---------+---------+-----------------------=|
|Country |Date |link |
|=---------+---------+-----------------------=|
|AREGENTINA|10/10/201|http://wwww.messi.com |
|AREGENTINA|10/10/201|http://wwww.maradona.com|
|ITALY |10/8/199 |http://www.baggio.com |
|ITALY |10/8/199 |http://www.maldini.com |
|ENGLAND |4/4/199 |http://www.beckham.com |
|FRANCE |3/4/199 |http://www.cantona.com |
'----------+---------+------------------------'
disconnected
Job forum26293 ended at 09:29 26/09/2012.

For more details, please see my screenshots.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Split a single excel column in to multiple columns based on condition!

Many Thanx Shong,
It works and hopefully it will help the rest who have a similar issue.

Regards,
Biruhale