topic Re: Some values in column defined as String are getting converted to timestamps in Talend Studio

Some values in column defined as String are getting converted to timestamps

kspurgin — Tue, 24 Mar 2020 22:11:39 GMT

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"

How do I get it to leave these values alone?

Thanks!

Re: Some values in column defined as String are getting converted to timestamps

Jj5 — Tue, 24 Mar 2020 22:16:37 GMT

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.

Re: Some values in column defined as String are getting converted to timestamps

kspurgin — Tue, 24 Mar 2020 22:33:44 GMT

Thanks to you for being quickly helpful!

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...

Re: Some values in column defined as String are getting converted to timestamps

manodwhb — Wed, 25 Mar 2020 00:53:24 GMT

You open the cab file in notepad++ and is what was the format you are having. You will see different format when you open in excel that can file

Re: Some values in column defined as String are getting converted to timestamps

Jj5 — Wed, 25 Mar 2020 20:11:06 GMT

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 "";
		}
	}

Re: Some values in column defined as String are getting converted to timestamps

kspurgin — Wed, 25 Mar 2020 22:36:49 GMT

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!

Re: Some values in column defined as String are getting converted to timestamps

Jj5 — Wed, 25 Mar 2020 22:46:57 GMT

Any time, getting used to it has some learning curve but it's great once you get the hang of it. Here's how to add a routine:
https://community.talend.com/t5/Design-and-Development/Create-a-user-routine-and-call-it-in-a-Job/ta-p/21665