Saturday, 19 January 2013
Talend tFileInputExcel transformations - remove linefeed from cells, split name to first name last name, doing initcap operation
Problem - Read excel file using talend tFileInputExcel component and then do some basic transformations like - splitting name into first name and last name using delimiter most probably single space, replacing linefeed character in one cell text of excel by some other character most probably space character, also getting initcap done for first name and last name
Solution - There could be more then one ways to achieve above, but one quick way i will display is using tJavaRow to get input from tFileInputExcel component and then use java functions to do required transformations as below
Name column in our example contains - first name + space + last name - example Thomas Eddy
Also another flavor of Name column could be that it contains text like this
Thomas
Eddy
Above is single row of excel.
using tJavaRow code as below we can achieve the needful
(assuming Name is the column in schema of tjavarow and tfileinputexcel)
//replace linefeed
if (input_row.Name != null) {
input_row.Name = input_row.Name.replaceAll("\n"," ");
}
//split name to first name and last name
if (input_row.Name != null) {
input_name = input_row.Name.trim();
int start = input_name.indexOf(" ");
int end = input_name.lastIndexOf(" ");
if (start >= 0) {
output_row.First_Name = input_name.substring(0, start);
output_row.Last_Name = input_name.substring(end + 1, input_name.length());
}
if (start < 0) {
output_row.First_Name = input_name;
}
}
//doing initcap of first_name
if (output_row.First_Name != null)
output_row.First_Name = output_row.First_Name.substring(0,1).toUpperCase()+
output_row.First_Name.substring(1).toLowerCase();
//doing initcap of last_name
if (output_row.Last_Name != null)
output_row.Last_Name = output_row.Last_Name.substring(0,1).toUpperCase()+
output_row.Last_Name.substring(1).toLowerCase();
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment