Talend community forum / Testing for 2 digit year and appending century.

psm2000 · 2008-04-14 07:20:52

Tags: [CSV file, date, delimited file, perl, tos2.3.1]

Hi All,

I've a simple Excel to CSV mapping which is reading all Excel rows including a date column (tFileInputExcel to tFileOutputDelimited). This date column may or may not be null. If it is not null, it will be a valid date in one of the following formats:
MM-DD-YY or
MM-DD-YYYY or
(These above 2 are most likely scenarios)
MM/DD/YY or
MM/DD/YYYY or
MM.DD.YY or
MM.DD.YYYY or

The last 2 are rare but legal. The source and target columns need to be the same (one to one and no extra columns).

I need to do the following:
If date is NULL, do nothing.
If date is NOT NULL,
If date has YYYY for year,
do nothing
else
append 20 to YY (so it is 20YY).

How can I do this in TOS 2.3.1 (with some Excel mods)? Do I add a tMAP or tPerlRow? I think it should be some place I can do a bunch of if then else statements on one column!

Any help is appreciated.

Thanks
Sean

rbillerey · 2008-04-14 12:48:17

Hi Sean,

In a tPerlRow :

Code:

# to copy input row
@output_row = @input_row;

# we replace the "colDate" input column
$output_row[colDate] = DateFormat($output_row[colDate]);


sub DateFormat {
    my ( $inputDate ) = @_;
    
    my $rDate;

   # we also get separators because split separator is /(...)/
    my ( $mm, $sep1, $dd, $sep2, $yy ) = split /([\/\.-])/, $inputDate ;    

    $yy = '20'.$yy if 2==length $yy ;
    
    $rDate = join('', $mm, $sep1, $dd, $sep2, $yy);
    
    $rDate;
}

Hope it helps.

Richard

psm2000 · 2008-04-14 19:50:37

Richard,

Thanks for the whole solution! The revelation for me is that I can replace a current column (I thought I can only add a column) and that I can use subs/functions. This pretty much opens up the tPerlRow for anything PERL can do!

I'll be trying this out shortly.

Thank you.

Regards,
Sean

Announcement

Unanswered posts

#1 2008-04-14 07:20:52

Testing for 2 digit year and appending century.

#2 2008-04-14 12:48:17

Re: Testing for 2 digit year and appending century.

Code:

#3 2008-04-14 19:50:37

Re: Testing for 2 digit year and appending century.

Board footer