473,241 Members | 1,615 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

about timestamp reformat

HI,

I'm new in perl..

I'm not sure if that is easy to use Perl to change the timestamp
format in the text file..
I got the txt file from sybase database, because the format is
different , I need to change it suitable to out DB2 database..

I heard that Perl is good at text processing... But I'm not very
familiar with it...

For example I want to change the format from following

a|b|c|Jul 14 2000 4:56:00:000PM|d|e|f|Jul 14 2000
4:56:00:000PM|h|I|j

to

a|b|c|2000-07-14-16:56:00:000000|d|e|f|2000-07-14-16:56:00:000000|h|I|j


"|" is the seperator in the text, and the timestamp could be different
values and could happen at any place..

Is there any way to process the file easily?
Your help is very appreciated..
Jul 19 '05 #1
6 4583
janet wrote:
I want to change the format from following

a|b|c|Jul 14 2000 4:56:00:000PM|d|e|f|Jul 14 2000
4:56:00:000PM|h|I|j

to

a|b|c|2000-07-14-16:56:00:000000|d|e|f|2000-07-14-16:56:00:000000|h|I|j

"|" is the seperator in the text, and the timestamp could be
different values and could happen at any place..

Is there any way to process the file easily?


There are a few ways. Assuming the whole file is in $text, this is one
approach:

my %months = (Jan => 1, Feb => 2, Mar => 3, Apr => 4,
May => 5, Jun => 6, Jul => 7, Aug => 8,
Sep => 9, Oct => 10, Nov => 11, Dec => 12);

$text =~ s{\|(\w{3})\s+(\d{1,2})\s+(\d{4})\s+
(\d{1,2})(:\d{2}:\d{2}:)(\d{3})(\w{2})\|}
{ '|' . (sprintf '%d-%02d-%02d-%02d%s%06d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '|' }egx;

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl

Jul 19 '05 #2
Gunnar Hjalmarsson wrote:

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%06d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '|' }egx;


Those lines are probably better replaced with:

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%03d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '000|' }egx;

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl

Jul 19 '05 #3
Gunnar Hjalmarsson <no*****@gunnar.cc> wrote in message news:<il********************@newsb.telia.net>...
Gunnar Hjalmarsson wrote:

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%06d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '|' }egx;


Those lines are probably better replaced with:

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%03d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '000|' }egx;


HI,

I met a new problem when convert the data

For exp:

12:35:40, if converted, it would change to 24:45:40. that is not
correct. I'm not very familiar with Perl, who could help me how to do
it?

Gunnar, do you have time to take a look ?
Jul 19 '05 #4
janet wrote:
Gunnar Hjalmarsson wrote:
Gunnar Hjalmarsson wrote:

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%06d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '|' }egx;
Those lines are probably better replaced with:

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%03d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '000|' }egx;


I met a new problem when convert the data

For exp:

12:35:40, if converted, it would change to 24:45:40. that is not
correct. I'm not very familiar with Perl,


I know, you told us so in your original post. (I'm not very familiar
with the AM/PM time format.)

Since you asked for help here, I thought you were about to learn Perl,
so I hoped you would figure out how the the code I posted works, and
learn something new by doing so. Now I understand that that was never
your intention. Apparently you just wanted somebody to do your job for
you.
who could help me how to do it?
I won't.
Gunnar, do you have time to take a look ?


I'm not in the mood.

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl

Jul 19 '05 #5
Try this:

if ($7 eq 'PM' && $4 == 12)
{
$hour = 0;
}
elsif ($7 eq 'PM')
{
$hour = 12+$4;
}
else
{
$hour = $4;
}

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%06d', $3, $months{$1},
$2, $hour, $5, $6) . '|' }egx;
-HS Phuah

Gunnar Hjalmarsson <no*****@gunnar.cc> wrote in message news:<xx********************@newsb.telia.net>...
janet wrote:
Gunnar Hjalmarsson wrote:
Gunnar Hjalmarsson wrote:

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%06d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '|' }egx;

Those lines are probably better replaced with:

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%03d', $3, $months{$1},
$2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '000|' }egx;


I met a new problem when convert the data

For exp:

12:35:40, if converted, it would change to 24:45:40. that is not
correct. I'm not very familiar with Perl,


I know, you told us so in your original post. (I'm not very familiar
with the AM/PM time format.)

Since you asked for help here, I thought you were about to learn Perl,
so I hoped you would figure out how the the code I posted works, and
learn something new by doing so. Now I understand that that was never
your intention. Apparently you just wanted somebody to do your job for
you.
who could help me how to do it?


I won't.
Gunnar, do you have time to take a look ?


I'm not in the mood.

Jul 19 '05 #6
hs*****@usa.com (Hon Seng Phuah) wrote in message news:<38*************************@posting.google.c om>...
Try this:

if ($7 eq 'PM' && $4 == 12)
{
$hour = 0;
}
elsif ($7 eq 'PM')
{
$hour = 12+$4;
}
else
{
$hour = $4;
}

{ '|' . (sprintf '%d-%02d-%02d-%02d%s%06d', $3, $months{$1},
$2, $hour, $5, $6) . '|' }egx;
-HS Phuah

Gunnar Hjalmarsson <no*****@gunnar.cc> wrote in message news:<xx********************@newsb.telia.net>...
janet wrote:
Gunnar Hjalmarsson wrote:
> Gunnar Hjalmarsson wrote:
>>
>> { '|' . (sprintf '%d-%02d-%02d-%02d%s%06d', $3, $months{$1},
>> $2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '|' }egx;
>
> Those lines are probably better replaced with:
>
> { '|' . (sprintf '%d-%02d-%02d-%02d%s%03d', $3, $months{$1},
> $2, $7 eq 'PM' ? 12+$4 : $4, $5, $6) . '000|' }egx;

I met a new problem when convert the data

For exp:

12:35:40, if converted, it would change to 24:45:40. that is not
correct. I'm not very familiar with Perl,


I know, you told us so in your original post. (I'm not very familiar
with the AM/PM time format.)

Since you asked for help here, I thought you were about to learn Perl,
so I hoped you would figure out how the the code I posted works, and
learn something new by doing so. Now I understand that that was never
your intention. Apparently you just wanted somebody to do your job for
you.
who could help me how to do it?


I won't.
Gunnar, do you have time to take a look ?


I'm not in the mood.


Hi, HS Phuah, Thanks very much...

Yes it is the problem I met in my work , ask for help on it.. I just
busy on sth.. had no time to learn in Perl in detail this couple
weeks...

I think here is another way another way to do it

$7 ='PM' && $4+12 < 24 : $4+12

Thanks everyone!
Jul 19 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: mike m | last post by:
all, was able to piece together the following php/html code. Basically I want to push an SQL query and format it, present it to an html document. The problem appears with the SQL query. ...
3
by: Bert Sierra | last post by:
Hello -- I have what appears to be a simple PHP+MySQL query, but Dreamweaver consistently generates PHP code which won't parse. I've stared at the PHP code for several hours now, and I can't...
2
by: JS | last post by:
I have a del file with timestamp format as: Jan 1 1995 12:00AM I am trying to load this as: modified by timestampformat "MMM/D/YYYY HH:MM TT" but DB2 will not accept it. I have tried various...
12
by: Mike | last post by:
I have an Access DB that I upsized to a SQL server DB. The tables that I upsized I can't seem to modify. I wanted to insert some data into the table and I am getting the following error: ...
2
by: klh | last post by:
I am working with my developer group to switch from the type 2 to the type 4 (Universal Driver). Our set up is: Host Data Base - DB2 z/os v7.1 (mainframe). Client - DB2 Admin Client V8 FP8...
2
by: Ted | last post by:
1) In several tables, in my MySQL version, I created columns using something like the following: `ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, This...
6
by: jim.clifford | last post by:
Hello, I am experimenting with indexes and hope people can shed light on some of my problems. I am using SLQ 2000 on Win 2000 Server. Using the following query for discussion;...
7
by: bdbeames | last post by:
I never used Perl before, so I need a little help formating a date. What I'm doing is querying a postgres database and then creating a .xml file for a RSS feed. I don't know how to correctly format...
4
by: Mark McIntyre | last post by:
Hallvard B Furuseth wrote: Setting aside that 4 is an entirely sensible tab width... This seems to be the showstopper. Unless you now entirely own the code yourself alone, don't reformat it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.