Trouble with regex for seperating out date 1

[galorin]

I am trying to find and splot out dates from a less effective MySQL Database.

I connect, grab the key and all the records, then want to split out those that match a pattern into day, month, and year, the put into MySQL properly. Below is the code I've written, minus my connection strings. We have no pre-y2k data here, which is why I can just slap 20 onto the front of the 2-digit year

open(OUT, ">test.sql" )	|| die "Can't open file, 'test.sql': $!\n";

$db_bdb= DBI->connect('dbi:mysql:database=contacts;host=localhost:3306', $user, $pw, { RaiseError => 1, AutoCommit => 1 });
$sel = "SELECT IssueID, Version from issueditems";
$rows = $db_bdb->selectall_arrayref($sel);

foreach $row(@$rows){
print "@$row[1]\n";
if (@$row[1]  =~ m/(\d(1,))\/(\d(1,))\/(\d(1,))/){
	print "I found one!\n";
	$date="20$3-$2-$1";
	$sql = "UPDATE issueditems SET Date = '$date' WHERE IssueID = @$row[0];";
	print OUT "$sql\n";
	}
}

Here is a selection of data that is going into my regex.

5/5/08
3rd 30/4/08 [COLOR=red] (Getting this one would be nice) [/color]
SAL02-404jm
3rd
26/5/08
6/5/08
6/5/08
jug07jec
8/5/08
3.23
2020554328 jan 08 [COLOR=red] Don't care about this one, info duplicated elsewhere as this is a controlled item [/color]
3rd
6/5/08
6/5/08

 

[KevinADC]

Bad syntax here:

@$row[1]

should be:

$row

same with @$row[0], should be $row.


------------------------------------------
- Kevin, perl coder unexceptional!

 

[galorin]

As I am pulling in columns and rows from MySQL, the @$row[0] refers to the IssueID for that row, and @$row[1] refers to the Version, which I am trying to parse for date values.

Making the suggested changes does not feed what I need into my non-working regular expression.

 

[stevexff]

I've just pulled out the regex part, which might help you a bit. I've used your data as input. They aren't all supposed to be parsed as dates, are they?

use strict;
use warnings;

while (<DATA>) {
  chomp;
  print $_, "\t";
  if (/(\d{1,2})\/(\d{1,2})\/(\d{1,2})/) {
    print sprintf('%02d/%02d/20%02d', $1, $2, $3), "\n";
  }
  else {
    print ": no match\n";
  }
}

__DATA__
5/5/08
3rd 30/4/08 (Getting this one would be nice)
SAL02-404jm
3rd
26/5/08
6/5/08
6/5/08
jug07jec
8/5/08
3.23
2020554328 jan 08 Don't care about this one, info duplicated elsewhere as this is a controlled item
3rd
6/5/08
6/5/08

Obviously the if-then-else can be slimmed down a bit when you don't have to print out the results of the match...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[galorin]

No, all the data in there are not dates. The database was poorly designed before I started here, and I get to try to clean up someone else's design shortcomings. No doubt I'll have my own faults someone else will have to clean up after. Instead of having a version and a date field, if an item had no version, the date an item was sent is put into the version field. Sometimes the version and the date were put in, but not very often.

I think I may have left out a critical bit of info that's been a stumbling block. My data is coming from a SQL query, and if I understand Perl correctly (Still a newbie at it so forgive my bad use of terminology), then the references for my dataset based on my SELECT statement would be:

@$row[0] = IssueID, my unique identifier
@$row[1] = Version, the troublesome data that I want to parse out.

So my modified little block of code looks like this now:

open(OUT, ">test.sql" )	|| die "Can't open file, 'test.sql': $!\n";

$db_bdb= DBI->connect('dbi:mysql:database=contacts;host=localhost:3306', $user, $pw, { RaiseError => 1, AutoCommit => 1 });
$sel = "SELECT IssueID, Version from issueditems";
$rows = $db_bdb->selectall_arrayref($sel);

foreach $row(@$rows){
  if (@$row[1] =~ /(\d{1,2})\/(\d{1,2})\/(\d{1,2})/) {
    $date = sprintf('20%02d-%02d-%02d', $3, $2, $1);
	$sql = "UPDATE issueditems SET Date = '$date' WHERE IssueID = @$row[0];"; 
        print OUT "sql\n";
  } 
}

and the text I get into test.sql is:

UPDATE issueditems SET Date = '2008-05-15' WHERE IssueID = 5714;
UPDATE issueditems SET Date = '2008-05-15' WHERE IssueID = 5715;
UPDATE issueditems SET Date = '2008-05-29' WHERE IssueID = 5717;
UPDATE issueditems SET Date = '2008-05-29' WHERE IssueID = 5718;
UPDATE issueditems SET Date = '2008-05-26' WHERE IssueID = 5719;
UPDATE issueditems SET Date = '2008-05-26' WHERE IssueID = 5720;
UPDATE issueditems SET Date = '2008-05-29' WHERE IssueID = 5723;
UPDATE issueditems SET Date = '2008-05-29' WHERE IssueID = 5724;
UPDATE issueditems SET Date = '2008-05-29' WHERE IssueID = 5728;
UPDATE issueditems SET Date = '2008-05-29' WHERE IssueID = 5729;
UPDATE issueditems SET Date = '2008-06-05' WHERE IssueID = 5731;
UPDATE issueditems SET Date = '2008-06-05' WHERE IssueID = 5732;
UPDATE issueditems SET Date = '2008-06-05' WHERE IssueID = 5733;
UPDATE issueditems SET Date = '2008-06-05' WHERE IssueID = 5734;

This is the desired result. I see what I did wrong in my regex, and as that is what I really need perl for. Thanks for the help steve.

 

[stevexff]

I must admit, for these kinds of fixes I always like to write my SQL to a file, as you have. Just so I can have a look at it before I run it

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[ishnid]

Just as an aside, if $row is a reference to an array, then the normal way of referring to element 1 would be:

$row->[1]

 

[galorin]

I'll try that ishnid, if that's the normal way to do it then. Might make my code easier to manage and for others to understand.

 

[KevinADC]

I had assumed $row was not a reference to an array, which is why I suggested using $row. Not sure why I assumed that, I should have asked.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[galorin]

I guess even Kevin, perl coder unexceptional has an off moment. At least you're not like me, I have off moments most of the time when writing Perl.