Solved

Help with adjusting perl script

Posted on 2004-10-15
18
263 Views
Last Modified: 2010-04-17
Originally Kavar wrote this script for me and it worked great up until the data format was changed slightly.
This script parses an ftplog and imports the applicable data into log.mbd.

This is the current data format
10/10/04-12:15:15 PM - C:\InetPub\FTPRoot\302812\302812TLOG10102004.zip 47037 Copied
When I launch the script the box opens and closes nothing happens this is because the filename does not include the time.
This is the old format 1359 represents the time.
4/15/04-2:49:00 PM - c:\Inetpub\FTPRoot\335009\335009TLOG041420041359.zip 78719 Copied


I need to do is adjust this script so I am not parsing the time from the filename and importing it.


################begin perl script
use Win32::OLE;

#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x5cFTPRoot\x5c';
$reLogdate='\d{1,2}\x2f\d{1,2}\x2f\d{2}';
$reLogtime='\d{1,2}:\d{1,2}:\d{2}\s[PA]M';
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';

my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;

open(INFO, $logfile);          # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$dbFilename;");
$adoCommand->{ActiveConnection} = $adoConnection;

my $databasestring="";
while ($lines=<INFO>)
{
     $_=$lines;
     if( /($reLogdate)-($reLogtime)\s-\s$reFtpdir($reServernumber)\x5c($reFilename)\s($reFilesize).+/ )
     {
          $Logdate=$1;
          $Logtime=$2;
          $Servernumber=$3;
          $Filename=$4;
          $Filesize=$5;
          $_=$Filename;
          if (/($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2})(\d{2})(\.zip)/)
          {
           $FileMonth=$3;
           $FileDay=$4;
           $FileYear=$5;
           $FileHour=$6;
           $FileMin=$7;
          }
          else
          {
               $FileDay=0;
               $FileMonth=0;
               $FileYear=0;
               $FileHour=0;
               $FileMin=0;
          }
          $databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#$Logdate#,#$Logtime#,\'$Servernumber\',\'$Filename\',$Filesize,$FileDay,$FileMonth,$FileYear,$FileHour,$FileMin)";
          print "$databasestring\n";
          $adoCommand->{CommandText}=$databasestring;
          $adoCommand->Execute();
          if (Win32::OLE->LastError())
          {
               print Win32::OLE->LastError();
          }
     }
}
 
###################End Perl script
0
Comment
Question by:irishmanjb
  • 9
  • 9
18 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 12324579
if( /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/ )
0
 
LVL 84

Expert Comment

by:ozo
ID: 12324658
($LogHour,$LogMin,$LogPM) =  $Logtime=~/(\d+):(\d+)(.*PM)?/;
$LogHour += 12 if $LogPM;
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,LogHour,LogMin) Values (#$Logdate#,#$Logtime#,\'$Servernumber\',\'$Filename\',$Filesize,$FileDay,$FileMonth,$FileYear,$LogHour,$LogMin)";
0
 

Author Comment

by:irishmanjb
ID: 12341085
Ozo

Where am I inserting these changes?
 
Thanks for your help.
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 84

Expert Comment

by:ozo
ID: 12342460
#       if (/($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2})(\d{2})(\.zip)/)
          if( /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/ )
          {
           $FileMonth=$3;
           $FileDay=$4;
           $FileYear=$5;
           $FileHour=$6||'00';
           $FileMin=$7||'00';
          }
          else
          {
               $FileDay=0;
               $FileMonth=0;
               $FileYear=0;
               $FileHour=0;
               $FileMin=0;
          }
0
 

Author Comment

by:irishmanjb
ID: 12346536
Ozo thanks
I am not having any luck this is what I have.


################begin perl script
use Win32::OLE;

#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x5cFTPRoot\x5c';
$reLogdate='\d{1,2}\x2f\d{1,2}\x2f\d{2}';
$reLogtime='\d{1,2}:\d{1,2}:\d{2}\s[PA]M';
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';

my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;

open(INFO, $logfile);          # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$dbFilename;");
$adoCommand->{ActiveConnection} = $adoConnection;

my $databasestring="";
while ($lines=<INFO>)
{
     $_=$lines;
     if( /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/ )
     {
          $Logdate=$1;
          $Logtime=$2;
          $Servernumber=$3;
          $Filename=$4;
          $Filesize=$5;
          $_=$Filename;
          if (/($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2})(\d{2})(\.zip)/)
          if( /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/ )
          {
           $FileMonth=$3;
           $FileDay=$4;
           $FileYear=$5;
           $FileHour=$6||'00';
           $FileMin=$7||'00';
          }
          else
          {
               $FileDay=0;
               $FileMonth=0;
               $FileYear=0;
               $FileHour=0;
               $FileMin=0;


          }
          $databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#$Logdate#,#$Logtime#,\'$Servernumber\',\'$Filename\',$Filesize,$FileDay,$FileMonth,$FileYear,$FileHour,$FileMin)";
          print "$databasestring\n";
          $adoCommand->{CommandText}=$databasestring;
          $adoCommand->Execute();
          if (Win32::OLE->LastError())
          {
               print Win32::OLE->LastError();
          }
     }
}
 
###################End Perl script
0
 
LVL 84

Expert Comment

by:ozo
ID: 12346626
remove or comment out the line
#         if (/($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2})(\d{2})(\.zip)/)
the next line was meant to replace it
0
 

Author Comment

by:irishmanjb
ID: 12346627
I would assume that I need to remove all of the references to file hour and file minute correct?

Thanks
0
 
LVL 84

Expert Comment

by:ozo
ID: 12346785
If you leave them in, they will both be 00 unless you get an old format line in your $logfile
0
 

Author Comment

by:irishmanjb
ID: 12346872
This is very strange I think I may be barking up the wrong tree

Old log example
8/1/04-2:41:38 PM - c:\Inetpub\FTPRoot\306406\306406TLOG080120041236.zip 88733 Copied
8/1/04-2:41:52 PM - c:\Inetpub\FTPRoot\306424\306424TLOG073120041358.zip 48261 Copied
Processes and imports into the database correctly

New Log example
10/17/04-12:10:08 PM - C:\InetPub\FTPRoot\302812\302812TLOG10162004128.zip 79057 Copied
10/17/04-1:10:08 PM - C:\InetPub\FTPRoot\302984\302984TLOG101720041257.zip 73890 Copied
does not process

The time thing is not an issue some files have the time and some don't that is how it was with the old log and this did not cause an issue in the past.  I don't see any difference but the top works and the bottom doesn't.

Any ideas?




0
 

Author Comment

by:irishmanjb
ID: 12346906
I tried combining 2 lines from the old log and 2 lines from the new log.
The script simply hops over the new log lines and processes the old lines.

INSERT INTO "DataTable" (LogDate,LogTime,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#8/1/04#,#2:41:38 PM#,'306406','306406TLOG080120041236.zip',88733,01,08,2004,12,36)
INSERT INTO "DataTable" (LogDate,LogTime,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#8/1/04#,#2:41:52 PM#,'306424','306424TLOG073120041358.zip',48261,31,07,2004,13,58)
0
 
LVL 84

Expert Comment

by:ozo
ID: 12347022
#what happened to
     if( /($reLogdate)-($reLogtime)\s-\s$reFtpdir($reServernumber)\x5c($reFilename)\s($reFilesize).+/ )
     {
          $Logdate=$1;
          $Logtime=$2;
          $Servernumber=$3;
          $Filename=$4;
          $Filesize=$5;
          $_=$Filename;
0
 

Author Comment

by:irishmanjb
ID: 12347215
Here is what I have still no luck.
That line was missing but did not make a difference.

################begin perl script
use Win32::OLE;


#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x5cFTPRoot\x5c';
$reLogdate='\d{1,2}\x2f\d{1,2}\x2f\d{2}';
$reLogtime='\d{1,2}:\d{1,2}:\d{2}\s[PA]M';
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';

my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;

open(INFO, $logfile);          # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$dbFilename;");
$adoCommand->{ActiveConnection} = $adoConnection;

my $databasestring="";
while ($lines=<INFO>)
{
     $_=$lines;
     if( /($reLogdate)-($reLogtime)\s-\s$reFtpdir($reServernumber)\x5c($reFilename)\s($reFilesize).+/ )
     {
          $Logdate=$1;
          $Logtime=$2;
          $Servernumber=$3;
          $Filename=$4;
          $Filesize=$5;
          $_=$Filename;
         
            if( /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/ )
        {
           $FileMonth=$3;
           $FileDay=$4;
           $FileYear=$5;
           $FileHour=$6||'00';
           $FileMin=$7||'00';

          }
          else
          {
               $FileDay=0;
               $FileMonth=0;
               $FileYear=0;
               $FileHour=0;
               $FileMin=0;
          }
          $databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#$Logdate#,#$Logtime#,\'$Servernumber\',\'$Filename\',$Filesize,$FileDay,$FileMonth,$FileYear,$FileHour,$FileMin)";
          print "$databasestring\n";
          $adoCommand->{CommandText}=$databasestring;
          $adoCommand->Execute();
          if (Win32::OLE->LastError())
          {
               print Win32::OLE->LastError();
          }
     }
}
 
###################End Perl script
0
 
LVL 84

Accepted Solution

by:
ozo earned 500 total points
ID: 12347532
Sorry,
    /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/
should have been
    /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2})?(\d{2})?(\.zip)/
also,
  $reFtpdir='c:\x5cInetpub\x5cFTPRoot\x5c';
is matching only lower case "c"
try
  /($reLogdate)-($reLogtime)\s-\s$reFtpdir($reServernumber)\x5c($reFilename)\s($reFilesize).+/i
0
 

Author Comment

by:irishmanjb
ID: 12347650
That worked great thanks!
0
 
LVL 84

Expert Comment

by:ozo
ID: 12347690
Actually,
/($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2})?(\d{2})?\d*(\.zip)/
should work better for
302812TLOG10162004128.zip
which is not like either
335009TLOG041420041359.zip
or
302812TLOG10102004.zip

(so how should 2004128 be treated?)
0
 

Author Comment

by:irishmanjb
ID: 12347978

Different program versions have developed three different filenames as you have noted.
I am only concerened with:
the first 6 numbers is the site identifier
then month, date and year after the tlog reference

I don't need time.  When the script was written is was incorporated but I never used it.

Thanks


0
 
LVL 84

Expert Comment

by:ozo
ID: 12348083
Ok, but you'll still need a \d* to match any possible digits between the year and the .zip, even if you don't use it.
0
 

Author Comment

by:irishmanjb
ID: 12349783
Thanks very much for your help!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now