topic Re: How to replace a blank date with a "Null Value" in Talend Studio

How to replace a blank date with a "Null Value"

Anonymous — Sat, 16 Nov 2024 13:41:41 GMT

Hello,
I am testing TOS and I really like it
I've got a short question: I am joining two csv files containing a date field that I output into an excel file.
The inport format is HH:mm:ss and in my excel output file TOS automatically replaces that with ""dd-MM-yyyy HH:mm:ss" eg "06:12:17" gets "01.01.1970 06:12:17" even though the scheme is defined as "HH:mm:ss".
In order to get the right format in the excel file i tried to use TalendDate.formatdate but it gives me a null exception because some values are acually blank after the initial join.
Hence, I was looking for an expression to replace a blank date with something like:
test.date == null ? 0:test.date
but it doesn't work (and I don't think it is possible).
Any advices? Thanks in advance.
I am using TOS 3.2. on Windows with a Java project. I searched the forum but I couldn't find anything.
Cheers,
James

Re: How to replace a blank date with a "Null Value"

Anonymous — Tue, 10 Nov 2009 08:23:57 GMT

Hello

The inport format is HH:mm:ss and in my excel output file TOS automatically replaces that with ""dd-MM-yyyy HH:mm:ss" eg "06:12:17" gets "01.01.1970 06:12:17" even though the scheme is defined as "HH:mm:ss".

You can read it as String type and output it to excel file as String type. If you need to convert a Date to a string, using TalendDate.formatDate(String pattern, Date date) eg:
test.date==null?null: TalendDate.formatDate("HH:mm:ss", test.date) //test.date is Date type
Best regards
shong

Re: How to replace a blank date with a "Null Value"

Anonymous — Wed, 11 Nov 2009 13:44:46 GMT

Thanks fo the answer, shong. It works like proposed, exept for the fact that excel does not recognize the format, which it did when using 'date' format. I check in the output options to leave the format definition excel as set (HH:mm:ss). I have a sum function that sums up durations and unless I click into the cells and press enter it does not regpognize the format. Propably this some kind of excel bug.
However, I have one more question: Is it possible (without conversions) to sum up durations given in HH:mm:ss, or will get confused because there is always a date involved?
Cheers,
James

Re: How to replace a blank date with a "Null Value"

Anonymous — Thu, 12 Nov 2009 07:16:40 GMT

Hello

Is it possible (without conversions) to sum up durations given in HH:mm:ss, or will get confused because there is always a date involved?

It is always a date involved, it is a excel API limitation.
Best regards

shong