Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with adjusting perl script

Posted on 2004-10-15
18
Medium Priority
?
277 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project

721 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