CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Substitution/Matching regex issue (.txt to SQL)

 



Kupot
New User

Mar 17, 2009, 2:56 PM

Post #1 of 4 (327 views)
Substitution/Matching regex issue (.txt to SQL) Can't Post

Hi,

I have a SQL database that is updated from a Project DB on my work network. In the random event that the SQL's data is wiped, or only partially wiped I would like to have a way to update it with ease. Right now I have exported entries with information close to:

"#######","UserID","##","Description_Field_May_Contain_Many_Chars","Team",",#Date#

At the end there, those '#' marks are actual hash marks surrounding the Date.

I have looked around and have gathered enough information to create code that will be soon to follow, but I am having problems getting the data to come up in the correct spots.

The format I am trying to format this into is:

print "Update Tasks set totHrs=$3\n";
print "Where UID=$2 And Task=$1 And weekStart=$6";

What I have so far is...


Code
  

$data = "ProjInfo.txt";
$outdata = ">SQL_Stmts.txt";

open(READ_FILE, $data);
open(WRITE_FILE, $outdata);

@filearr = <READ_FILE>;

foreach $_ (@filearr)
{
if($_ =~ m/'"'(.*?)'","'(.*?)'","'(.*?)'","'(.*?)'","'(.*?)'",",#'(.*?)'#'/){
print "$1 $2 $3 $4 $5 $6\n";
}
}

print "Update Tasks set totHrs=$3\n";
print "Where UID=$2 And Task=$1 And weekStart=$6";

close(READ_FILE);
close(WRITE_FILE);



(Note: the READ, WRITE info I haven't touched yet, it worked with a prior program that I have, but can be ignored for the current issue ... I think... :) )

And, if you're trying this code, you'll find that it should print words, but have no values for any of the $(#)'s... I think I have done everything else correctly.. Though I should warn you... I am a beginner at this.

I've seen some great advice on this website, hopefully you can help me further too :) Thanks!


bichonfrise74
Novice

Mar 17, 2009, 3:25 PM

Post #2 of 4 (322 views)
Re: [Kupot] Substitution/Matching regex issue (.txt to SQL) [In reply to] Can't Post

Try this... Note that you may have to modify the array elements to suit your needs.


#!/usr/bin/perl

use strict;

while( <DATA> ) {
my @array = split( "," );
print "Update Tasks set totHrs=$array[3] " .
"where UID=$array[2] and Task=$array[1] and WeekStart=$array[6] \n";
}

__DATA__
"#######","UserID","##","Description_Field_May_Contain_Many_Chars","Team",",#Date#


--bichonfrise74


Kupot
New User

Mar 17, 2009, 4:17 PM

Post #3 of 4 (319 views)
Re: [bichonfrise74] Substitution/Matching regex issue (.txt to SQL) [In reply to] Can't Post

Thank you for the quick response!

The only problem I am having is trimming the last term of the #'s on either side... That's why I was trying the substitution/switch code because the delimiter doesnt work when it comes to the end...

Notice it's like this:

"Number,"UserID","Num","Description_Field_May_Contain_Many_Chars","Team",",#Date#

And I need each piece of the array (in your example) to trim to these results:

Number *

UserID *

Num *

Description_Field_May_Contain_Many_Chars

Team

Date *

I appreciate the help though, its been a while since I've coded in Perl and had forgotten about the array abilities Perl has.

(I've starred the items in the list that are the only really needed fields, if that helps with brainstorming, I appreciate it.)

I'll continue to see what I can do with your code there and see if I can get it to work.

Much Thanks


Kupot
New User

Mar 18, 2009, 11:12 AM

Post #4 of 4 (311 views)
Re: [Kupot] Substitution/Matching regex issue (.txt to SQL) [In reply to] Can't Post

Ok, after a little manipulating I was able to solve it, thanks for the help.. got me pointed in the right direction. :)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives