Post a reply

Write your message and submit

Options

Click in the dark area of the image to send your post.

Go back

Topic review (newest first)

rbillerey
2007-04-17 17:26:33

You're right the purpose of [- | \/] is to match "-" or "/" and there is no need for "|". This expression matches "-", "/", and "|" but it's not what we want.


Code:

my ( $day, $month, $year ) = ( $datestring =~ m/(\d{1,2})[- \/](\d{1,2})[- \/](\d{2,4})/ );

matches "2/3/07" or "2-3-07"

and

Code:

my ( $day, $month, $year ) = ( $datestring =~ m/(\d{1,2})[- |\/](\d{1,2})[- |\/](\d{2,4})/ );

matches "2/3/07" or "2-3-07" or "2|3|07" which is a rather uncommon date format !

Nicolep
2007-04-17 16:49:48

thanks! It works very well!

I tried something close : "$datestring =~ m/(\d{2}|d{1})[- |\/](\d{1}|d{2})[- |\/](\d{2})/" but it didn't work so thanks for the answer!

Just a last little question about perl syntax : in the [- |\/] part, it seems that the "|" character is not required. That works without it but does it change something?

Lot of thanks

Nicolep

rbillerey
2007-04-17 16:01:07

Hi,

This function should solve your problem :

Code:

sub myFmtDate {
    my ($datestring) = @_ ;

    my ( $day, $month, $year ) = ( $datestring =~ m/(\d{1,2})[- \/](\d{1,2})[- |\/](\d{2,4})/ );

    return sprintf("%02s-%02s-%04s", $month, $day, 2000 + $year);
    
}

myFmtDate('2-3-07') -> 03-02-2007
myFmtDate('02-03-07') -> 03-02-2007

But don't use it with dates prior to year 2000 !!!

Best Regards

Nicolep
2007-04-17 15:35:27

Damned we can't attach doc files!
So I do it again with image files..

Nicolep
2007-04-17 15:21:41

Hi,
Thanks for the link but I already try this conversion function. It works very well for dates in which day or month number doesn't begin with "0" (11/12/2007 for example) but dates like 02/07/2007 can't be well converted because the first automatic perl conversion gives 7-2-07 and not 07-02-07. So the length of day and month is not always 2 and the function doesn't understand.. I hope you understand the problem.
I join a .doc file to explain you my process.

Thanks.

Nicolep

rbillerey
2007-04-17 14:57:07

Hi,

When the excel perl connector reads your worksheet, it uses a default format converter that does not correspond to the french format that is defined in excel.

A workaround is to use a tMap and user defined code routines in TOS to get the right date format. You have a sample here :

http://talendforge.org/forum/viewtopic.php?id=275

Hope it helps.

plegall
2007-04-17 14:32:11

Can you show us an extract of the content of the exported data in your MySQL database? (select * from table) and also I would like to see the table schema (desc tablename) Can you also put a tLogRow just before the tMysqlOutput and show us what the "Run job" view shows?

Nicolep
2007-04-17 09:53:01

Mhirt have you got an idea? Does Talend automatically convert date data??
Thanks

Nicolep
2007-04-16 14:08:44

Yes it's a perl project. Has anyboby met this problem? Do you think it comes from Excel or Talend?
Thanks

Nicolep

Nicolep
2007-04-16 09:51:58

Where can I see that? I never had to choose between perl or java. I think it's perl because I can write perl functions and add TperlRow..?

mhirt
2007-04-13 18:37:14

Are you in a Perl or in a Java project ?

Nicolep
2007-04-13 12:17:05

Hello,

I've got a problem with a TfileInputExcel job and a date field.
I have to extract data from an EXCEL file. A column of this file is a date type (french format : DD/MM/YYYY).
I connect the TfileInputExcel to a TDBOutput to test the export (MySQL DB). The export is ok except for the date column. An automatic conversion seems to be done..The date format in the destination DB is MM-DD-YY..So why is this conversion done? How can I avoid it?
This conversion gives other problem : when I ve got a source date like 02/07/2006, I have the date 7-2-06 in the destination DB..So the first 0 are deleted and it's not a good thing for MY further date conversion!!
Have you got an idea?

Thanks

Nicolep

Board footer

Powered by FluxBB