Talend community forum / [resolved] Check if a string is a valid date pattern ?

FeelIT · 2008-06-25 17:06:03

Tags: [date, delimited file, java, tos2.3.3]

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

shong · 2008-06-26 08:24:25

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,

Code:

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

Best regards

shong

FeelIT · 2008-06-26 08:49:38

ok thx shong

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

sahanakrish · 2009-05-26 23:38:59

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

Morbo · 2009-05-27 02:17:17

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

c0utta · 2009-05-27 07:50:53

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

moprea · 2010-01-07 01:08:29

Hello, relatively new to Talend, trying to help out a friend.

Trying to convert SQL Server date time value [2008-09-29 01:56:26.393] into 8 digit int [20080929] 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 [ENTDATE] - 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

shong · 2010-01-07 03:59:03

Hello Mircea

Convert(VARCHAR(10), DomAtyVisitDateEnt, 112) AS [ENTDATE] - 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

moprea · 2010-01-07 05:47:00

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

shong · 2010-01-07 05:49:41

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

moprea · 2010-01-07 06:39:31

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.

shong · 2010-01-07 06:49:04

Hello

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

You need convert date to int, see my screenshot.

Code:

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

Best regards

shong

Uploaded Images

moprea · 2010-01-07 07:15:16

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.

shong · 2010-01-07 07:19:35

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

moprea · 2010-01-07 07:33:34

Right, I suppose I should start brushing up on my Java ASAP.

Thanks again for your help.

-Mircea

Announcement

Unanswered posts

#1 2008-06-25 17:06:03

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

#2 2008-06-26 08:24:25

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

Code:

#3 2008-06-26 08:49:38

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

#4 2009-05-26 23:38:59

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

#5 2009-05-27 02:17:17

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

#6 2009-05-27 07:50:53

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

#7 2010-01-07 01:08:29

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

#8 2010-01-07 03:59:03

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

#9 2010-01-07 05:47:00

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

#10 2010-01-07 05:49:41

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

#11 2010-01-07 06:39:31

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

#12 2010-01-07 06:49:04

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

Code:

#13 2010-01-07 07:15:16

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

#14 2010-01-07 07:19:35

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

#15 2010-01-07 07:33:34

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

Board footer