asked on

read all files in dir and extract specific data and send them to mysql

Hi friends,
I'm new to perl and Mysql and i need the following.if someone can give me the instructions i will go on the ditails myself.
---------------------------------------------------------------------------------
i have some files of format .txt in a directory that each of these files contain many blocks .every block is look like following:
---------------------------------------------------------------------------------
UMTSGSMPLMNCallDataRecord
mSTerminating
callPosition 03'H
chargeableDuration 0 1 12'BCD
dateForStartOfCharge 040719'H
exchangeIdentity "RS7G1V0119A11E0"'S
interruptionTime 0 0 0'BCD
recordSequenceNumber 13469605'D
tariffClass 1'D
tariffSwitchInd 00'H
timeForStartOfCharge 8 50 54'BCD
timeForStopOfCharge 8 52 7'BCD
outputType 04'H
switchIdentity 0001'H
calledSubscriberIMSI 418400100053236F'TBCD
calledSubscriberIMEI 352966000334860F'TBCD
timeForTCSeizureCalled 8 50 47'BCD
rFPowerClassCalled 04'H
eosInfo 00'H
mobileStationRoamingNumber 11964758890254'TBCD
teleServiceCode 11'H
frequencyBandSupported 06'H
mSCIdentification 11964750001110'TBCD
outgoingRoute "ARBSC1O"'S
calledPartyNumber 119647504483236F'TBCD
networkCallReference A43F0C0001'H
callingPartyNumber 132261097F'TBCD
incomingRoute "ERBCENI"'S
presentationAndScreeningIndicator 10'H
redirectionCounter 00'H
relatedCallNumber 2E033A'H
callIdentificationNumber 3015484'D
typeOfCallingSubscriber 1'D
radioChannelProperty 03'H
tAC 01020E'H
subscriptionType 00'H
originForCharging 01'H
chargedParty 00'H
timeFromRegisterSeizureToStartOfCharging 0 0 11'BCD
internalCauseAndLoc 0003'H
firstRadioChannelUsed 00'H
firstAssignedSpeechCoderVersion 01'H
speechCoderPreferenceList 010003'H
firstCalledLocationInformation 14F80400650015'H
lastCalledLocationInformation 14F80400650015'H
outgoingAssignedRoute "ARBSC1O"'S
eMLPPPriorityLevel 02'H
disconnectingParty 00'H

-----------------------------------------------------------------------------
i need to write a program in perl to select some specific fields of every block and insert them into mysql data tables.the program should read files in the specified dir sequntialy , open them and extract the specific info into corresponding colomns.i dont know how to read all files one by one and how to for example pick up the amount of incomingRoute that is "ERBCENI"'S and insert it to appropriate colomn in mysql table.
any help will be appreciated.
Rovan

holli

This is the first part that gets the file contents into a data-structure (an array of hashes, the hashes contain fieldname-value pairs).
Im an not very familiar with DBI and mysql, but if you have problems on that part, just ask again.

#!/usr/bin/perl
use strict;
use Data::Dumper;

my @records;
my %record;

# Put the fields you want here
my @fields =
(
      "timeForStartOfCharge",
      "timeForStopOfCharge",
);

my @files = glob ("your/path/to/file.*");

foreach my $file (@files)
{
next unless -f $file;
open FILE, $file or die "Can not open $file $!\n";

while ( my $line = <FILE>)
{
# is it a new block ?
      if ( $line =~ /^UMTSGSMPLMNCallDataRecord/ )
      {
       # after first record
            push @records, {};
            next;
      }

      #try to match the line to a field

      for my $field ( @fields )
      {
            if ( $line =~ /^ +$field +(.+)$/ )
            {
                  $records[-1]->{$field} = $1;
                  next;
            }
      }
}
close FILE;

#last record
push @records, \%record if %record;
}

print Dumper (\@records);

ASKER CERTIFIED SOLUTION

holli

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

holli

how´s it going? need further help?