topic Re: [resolved] Check if a string is a valid date pattern ? in Talend Studio

[resolved] Check if a string is a valid date pattern ?

Anonymous — Sat, 16 Nov 2024 14:19:17 GMT

Hi there,
I have a CSV File as a source, one of the field is supposed to be a date
But sometimes, some rows deliver this field with an invalid string for a date (for example : "378-35-23", or "NaN-NaN-NaN", etc ...)
I would like to test the content of this field to:
1* Load the correct rows in the target database
2* Reject the bad rows in an output file or Replace the bad data by a default one (NULL or 01/01/1900 for example)
Should I use tFilterRow ?
What is the method to valid the string pattern ?
How should I declare the data type for this field in my source input file ?

thx
Phil

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 26 Jun 2008 07:24:25 GMT

Hello

Should I use tFilterRow ?
What is the method to valid the string pattern ?
How should I declare the data type for this field in my source input file ?

Yes, you can use the tFilterRow to filter the invalid date and output them in a file, declare the data type for this field as string. All the valid data in this field should have a unite format, for exmaple the length of them are the same. After filter all the valid data, you can convert a String to a Date. for example,

TalendDate.parseDate("MM/dd/yyyy",06/26/2008)

Best regards
shong

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 26 Jun 2008 07:49:38 GMT

ok thx shong

is there any method like isValidDate(<<myString as string>>, <<myDateFormat as string>>) return 0 or 1 for OK or Not OK ??

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Tue, 26 May 2009 22:38:59 GMT

is there any method like isValidDate(<<myString as string>>, <<myDateFormat as string>>) return 0 or 1 for OK or Not OK ??
Did you get the answer yet / please share if you do

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Wed, 27 May 2009 01:17:17 GMT

I don't know about TalendDate.parseDate, but SimpleDateFormat.parse throws exception u could catch and return false otherwhise true.

Re: [resolved] Check if a string is a valid date pattern ?

c0utta — Wed, 27 May 2009 06:50:53 GMT

sahanakrish, double posting and reviving old threads is frowned upon in most forums.

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 00:08:29 GMT

Hello, relatively new to Talend, trying to help out a friend.
Trying to convert SQL Server date time value into 8 digit int for fact table (DateKey) column that is an FK to a Date Dim. Not having much luck with Expression Builder.
So from:
"2008-09-29 01:56:26.393" to "20080929"
Convert(VARCHAR(10), DomAtyVisitDateEnt, 112) AS - gets me the desired results in T-SQL.
And if possible the same for the FK (TimeKey) Time Dim
"2008-09-29 01:56:26.393" to "015626"
Any ideas or references would be great. The job is in "Java" (not Perl) if that helps.
Thanks, Mircea

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 02:59:03 GMT

Hello Mircea

Convert(VARCHAR(10), DomAtyVisitDateEnt, 112) AS - gets me the desired results in T-SQL.

In Talend, you don't need use the convert function on sql, just define the date pattern on the schema of input/output component.
on the schema of input component, set the date pattern as:
"yyyy-MM-dd HH:mm:ss.sss"
on the schema of output component, set the date pattern as:
"yyyyMMdd"
Best regards
shong

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 04:47:00 GMT

Shong;
Thanks for your response. I am still just a little bit confused.
When you say input/output component (singular) are you talking about the tMap component? Or did you mean the input (data source) and the output (target) components? If the latter, am I editing the schema on the component itself?
Thanks, Mircea

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 04:49:41 GMT

Hello

If the latter, am I editing the schema on the component itself?

I mean the latter point, for example:
tMSSQLInput---tLogRow
on the schema of tMSSQLInput, set the date pattern as:
"yyyy-MM-dd HH:mm:ss.sss"
on the schema of tLogRow, set the date pattern as:
"yyyyMMdd"
Best regards
shong

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 05:39:31 GMT

Okay, that makes sense, thanks.
On the "tLogRow" I cannot set the date pattern because it is an "int". I am copying the date to a date column (know how to do that) and to an int column in the fact table. The copying to the int column is where I am having issues. I am unfamiliar with the what the correct expression should be.
I cited the SQL Convert example only to show what I am trying to do.
What I need help with is answering how I would convert a date (from the source) to an (8 digit) int (target) to be used as an FK to the Date dim.
Would this be handled at the expression level? Thanks for your patience.

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 05:49:04 GMT

Hello

On the "tLogRow" I cannot set the date pattern because it is an "int".

You need convert date to int, see my screenshot.

Integer.parseInt(TalendDate.formatDate("yyyyMMdd",row2.date))

Best regards
shong

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 06:15:16 GMT

That did it! Thank you very much.
Where could I have found the "Integer.parseInt()"?
It is not available in my expression builder. Is there a list with descriptions some place in Studio?
Thanks again.

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 06:19:35 GMT

Hello

Where could I have found the "Integer.parseInt()"?

It is Java code, not sql function. As you known, Talend product is developed in Java and you are working a Java project.
Best regards
shong

Re: [resolved] Check if a string is a valid date pattern ?

Anonymous — Thu, 07 Jan 2010 06:33:34 GMT

Right, I suppose I should start brushing up on my Java ASAP.
Thanks again for your help.
-Mircea