Help with adjusting perl script

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
irishmanjbAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ozoConnect With a Mentor Commented:
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
 
ozoCommented:
if( /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/ )
0
 
ozoCommented:
($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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
irishmanjbAuthor Commented:
Ozo

Where am I inserting these changes?
 
Thanks for your help.
0
 
ozoCommented:
#       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
 
irishmanjbAuthor Commented:
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
 
ozoCommented:
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
 
irishmanjbAuthor Commented:
I would assume that I need to remove all of the references to file hour and file minute correct?

Thanks
0
 
ozoCommented:
If you leave them in, they will both be 00 unless you get an old format line in your $logfile
0
 
irishmanjbAuthor Commented:
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
 
irishmanjbAuthor Commented:
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
 
ozoCommented:
#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
 
irishmanjbAuthor Commented:
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
 
irishmanjbAuthor Commented:
That worked great thanks!
0
 
ozoCommented:
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
 
irishmanjbAuthor Commented:

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
 
ozoCommented:
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
 
irishmanjbAuthor Commented:
Thanks very much for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.