[wplug] Convert 6/16/2003 0:00:00 to 2003-06-16?

Poyner, Brandon bpoyner at ccac.edu
Tue Jan 13 15:29:25 EST 2004


Yours is more correct.  One potential problem is that you should begin
your regexp with a ^ or you might modify more than you intended.  Nicely
done.

$ cat test
6/16/2003 0:00:00 here's some stuff in here entered on 6/16/2003 0:00:01
$ perl -p -e
's/(\d{1,2})\/(\d{1,2})\/(\d{4})\s(?:\d{1,2}:){2}\d{2}/$3-$1-$2/g;' test
2003-6-16 here's some stuff in here entered on 2003-6-16
$ perl -p -e
's/^(\d{1,2})\/(\d{1,2})\/(\d{4})\s(?:\d{1,2}:){2}\d{2}/$3-$1-$2/g;'
test
2003-6-16 here's some stuff in here entered on 6/16/2003 0:00:01


Brandon Poyner
Network Engineer II
CCAC - College Office
412-237-3086


-----Original Message-----
From: Embery, Nathan [mailto:Nathan.Embery at crowncastle.com] 
Sent: Tuesday, January 13, 2004 2:53 PM
To: 'wplug at wplug.org'
Subject: RE: [wplug] Convert 6/16/2003 0:00:00 to 2003-06-16?


You beat me to it, and I'm glad actually. Check out this monstronsity
I'd
constructed ;-)

# echo "6/16/2003 0:00:00 here's some stuff in here" > test
# perl -pi.tmp -e
's/(\d{1,2})\/(\d{1,2})\/(\d{4})\s(?:\d{1,2}:){2}\d{2}/$3-$1-$2/g;' test
# cat test
2003-6-16 here's some stuff in here

nate


-----Original Message-----
From: Poyner, Brandon [mailto:bpoyner at ccac.edu]
Sent: Tuesday, January 13, 2004 2:18 PM
To: wplug at wplug.org
Subject: RE: [wplug] Convert 6/16/2003 0:00:00 to 2003-06-16?


There are plenty of ways to do it.  Crude but effective...

$ cat test
6/16/2003 0:00:00 blah test1 blah
6/18/2003 0:00:00 blah test2 blah
6/20/2003 0:00:00 blah test3 blah
$ awk -F ' ' '{split($1,array,/\//); $1=array[3] "-" array[1] "-"
array[2]; print;}' test
2003-6-16 0:00:00 blah test1 blah
2003-6-18 0:00:00 blah test2 blah
2003-6-20 0:00:00 blah test3 blah
$ perl -e 'while(<>) { if (/^(\d+)\/(\d+)\/(\d+) (.*)$/) { print
"$3-$1-$2 $4\n"; } }' \ 	test
2003-6-16 0:00:00 blah test1 blah
2003-6-18 0:00:00 blah test2 blah
2003-6-20 0:00:00 blah test3 blah

Brandon Poyner
Network Engineer II
CCAC - College Office
412-237-3086


-----Original Message-----
From: Chris [mailto:vze2f6h6 at verizon.net] 
Sent: Tuesday, January 13, 2004 1:48 PM
To: wplug at wplug.org
Subject: RE: [wplug] Convert 6/16/2003 0:00:00 to 2003-06-16?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1




> -----Original Message-----
> From: wplug-admin at wplug.org [mailto:wplug-admin at wplug.org] On 
> Behalf Of Russ Schneider
> Sent: Tuesday, January 13, 2004 1:29 PM
> To: WPLUG
> Subject: [wplug] Convert 6/16/2003 0:00:00 to 2003-06-16?
> 
> 
> I have a CSV someone gave me from MS Access.
> 
> I need to import this into MySQL.
> 
> But the dates read like 6/16/2003 0:00:00 and I need to 
> convert them in 
> the text file to a what MySQL expects, which is 2003-06-16.
> 
> Anyone have any really good ideas?
> 

This is just an idea.

Create a copy of the structure of the MySQL database.  Change the
timestamp field to a varchar field.  Import the file into the new
database. Write a php script that will covert the date to the timestamp
format.  Export the data and import into the right database.  This
should
work.

$sql = "SELECT key, date FROM table";
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

    list($month, $day, $year) = explode("/", $row[date]);

    if (strlen($day) == "1") {
       $day = "0".$day;
     }
    if (strlen($month) == "1") {
       $month = "0".$month;
     }

    $newday = $year.$month.$day;
    $sql1 = "UPDATE table SET date='$newday' WHERE key='$row[key]'";
    print "Now is $year $month $day<br>$sql1<br><br>";
    $result1 = mysql_query($sql1);
 }

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>

iQA/AwUBQAQ9UgvHK4/UMrUIEQKeMwCg5hq1dASEYrlEN7pTO8qFo0iBxHIAn0jU
FaXcCxH/zUjVbd0P2XRFeYQ1
=0GCG
-----END PGP SIGNATURE-----

_______________________________________________
wplug mailing list
wplug at wplug.org
http://www.wplug.org/mailman/listinfo/wplug
_______________________________________________
wplug mailing list
wplug at wplug.org
http://www.wplug.org/mailman/listinfo/wplug

_______________________________________________
wplug mailing list
wplug at wplug.org
http://www.wplug.org/mailman/listinfo/wplug



More information about the wplug mailing list