Some values in column defined as String are getting converted to timestamps
Hello,
I have an Excel file with a column named DATE_MADE. This is very messy data and most values in this column cannot cleanly be converted to a real date type, so in my schema, I have defined the data type for this column as string.
I have set up a very simple job with tFileInputExcel as input and tLogRow as output.
tLogRow schema has the DATE_MADE column defined as a string.
I expected all the data in the DATE_MADE column to be passed through without transformation, as a string.
With an input value of "ca. 1952", that's what I'm seeing in my tLogRow output.
However, with an input value of "1950/02/01" I'm seeing the following in my tLogRow output, which is unexpected and undesirable: "Wed Feb 01 00:00:00 EST 1950"
Since I don't see any quick fixes, here's part of my dateroutine to convert the excel date into a normal date. You can pretty easily convert this to a method and use it in a tJavaRow component.
/**
* convertExcelDate: Takes the input date (String) and expected
* date format and converts the date into that format.
*
*
* {talendTypes} String
*
* {Category} User Defined
*
* {param} string("inputDate") input: The string representation of the Excel date you want converted.
*
* {param} string("ouputDateFormat") input: The format that you want the date in.
*
* {example} stringToInt("THU MAR 08 00:00:00 EST 1990", "yyyyMMdd") # 19900308.
*/
public static String convertExcelDate(String inputDate, String outputDateFormat) {
if(inputDate != null && inputDate.replaceAll("\\W", "").length()>0){
try{
DateFormat inDateFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy");
DateFormat outDateFormat = new SimpleDateFormat(outputDateFormat);
Date inDate = inDateFormat.parse(inputDate);
String outDate = outDateFormat.format(inDate);
return(outDate);
}
catch(ParseException e){
return "" + e;
}
} else {
return "";
}
}
This is an issue with Excel, not Talend. Excel will set a date in the format "EEE MMM dd HH:mm:ss z yyyy" but when viewed in Excel it will look like a normal short date.
I hope someone can provide an easy work-around, what I did in my job is create a routine to convert Excel dates and called it if the date.length() > 10.
No thanks to Excel for being unhelpfully "helpful".
Easy workarounds happily accepted if anyone has them.
The client originally sent CSVs, where yep, I can see the value there is "01-FEB-50". But the client also has HTML and line breaks inside column values in the CSV, and I was having trouble getting those files to parse into rows correctly at all. Always something...
Since I don't see any quick fixes, here's part of my dateroutine to convert the excel date into a normal date. You can pretty easily convert this to a method and use it in a tJavaRow component.
/**
* convertExcelDate: Takes the input date (String) and expected
* date format and converts the date into that format.
*
*
* {talendTypes} String
*
* {Category} User Defined
*
* {param} string("inputDate") input: The string representation of the Excel date you want converted.
*
* {param} string("ouputDateFormat") input: The format that you want the date in.
*
* {example} stringToInt("THU MAR 08 00:00:00 EST 1990", "yyyyMMdd") # 19900308.
*/
public static String convertExcelDate(String inputDate, String outputDateFormat) {
if(inputDate != null && inputDate.replaceAll("\\W", "").length()>0){
try{
DateFormat inDateFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy");
DateFormat outDateFormat = new SimpleDateFormat(outputDateFormat);
Date inDate = inDateFormat.parse(inputDate);
String outDate = outDateFormat.format(inDate);
return(outDate);
}
catch(ParseException e){
return "" + e;
}
} else {
return "";
}
}
Thanks so much for sharing this. I'm just getting started with this tool, but this will be quite helpful in figuring out how to do custom functions. Thanks!
