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

One Star

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

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

Accepted Solutions
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

ok thx shong

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

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

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
One Star

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

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

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

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

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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

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

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

It is Java code, not sql function.Smiley Wink As you known, Talend product is developed in Java and you are working a Java project.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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