Solved

Help with adjusting perl script

Posted on 2004-10-15
18
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

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 …
A short article about a problem I had getting the GPS LocationListener working.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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 …

729 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