Hello Viewer
Would u pls someone tell me,
how could I import data from a PABX log file(txt file),
and insert the data to the database
for ur kind information I am giving the sample log file.
pls find the log file in the following address-
http://cp.bol-online.com/pabx/
its an assignment for me.
thats why this so urgent for me.
Pls don't mind for my poor english.
I will be so greatful if anyone give any regarding information.
Thanks.
Read out data from a PABX log file
Moderator: General Moderators
-
Khairul Alam
- Forum Newbie
- Posts: 12
- Joined: Tue Sep 09, 2003 6:27 am
You're going to have to create some custom parsing code. The below is what i've whipped up and it seems to work (for me anyway) you just need to tweak it to your particular set up etc..
Code: Select all
<?php
error_reporting(E_ALL);
/*
CREATE TABLE `pbax` (
`date` varchar(8) NOT NULL default '',
`time` varchar(7) NOT NULL default '',
`ext` char(3) NOT NULL default '',
`co` char(2) NOT NULL default '',
`dial` varchar(10) NOT NULL default '',
`ring` varchar(4) NOT NULL default '',
`duration` varchar(8) NOT NULL default '',
`acccode` varchar(10) NOT NULL default '',
`cd` char(2) NOT NULL default ''
)
*/
//Parse PABX log file
$logfile = 'pabx_log.txt'; //change this to the name of your log file
$content = file($logfile);
foreach($content as $line){
//find only pabx lines and not the lines with the titles
if(preg_match('/^[0-9]{2}\/[0-9]{2}\/[0-9]{2}\s/', $line)){
$validlines[] = $line;
}
}
if(isset($validlines)){
//change the below for your mysql user and password
$db = mysql_connect('localhost', 'user', 'password') or die(mysql_error());
//change the below to the name of your database
mysql_select_db('test') or die(mysql_error());
foreach($validlines as $valid){
$valid = preg_replace('/\s{2,}/', ' ', $valid);
$parts = explode(' ', $valid);
list($date, $time, $ext, $co, $dial) = array_slice($parts, 0, 5);
$ring = '';
$duration = '';
$acccode = '';
$cd = '';
foreach(array_slice($parts, 5) as $check){
switch(strlen($check)){
case 4:
$ring = $check;
break;
case 8:
$duration = $check;
break;
case 2:
$cd = $check;
break;
}
}
//build up the insert query
$sqladd = array(
"'".$date."'",
"'".$time."'",
"'".$ext."'",
"'".$co."'",
"'".$dial."'",
"'".str_replace("'", "''", $ring)."'",
"'".str_replace("'", "''", $duration)."'",
"'".$acccode."'",
"'".$cd."'");
//change the below if your table isn't named pabx
$sql = "INSERT INTO pbax VALUES (".join(',', $sqladd).")";
echo $sql.'<br />';
mysql_query($sql) or die(mysql_error());
}
} else {
//if it ever gets here, then something went wrong :o
echo 'Error: No valid lines found';
}
?>-
Khairul Alam
- Forum Newbie
- Posts: 12
- Joined: Tue Sep 09, 2003 6:27 am
Thanks lot markl999
I'v worked with ur given code. It works very nice.
If u dont mind I'v some more query to u-
Would u pls tell me any links or other info about this system by which I can learn more.
I want to generate some reports suppose-
call summery of particular date
call summery of how many Incomming and Outgoing call have occured by a perticular extension
etc.
I am eagerly waiting for ur valuable sugestions.
Thanks again
I'v worked with ur given code. It works very nice.
If u dont mind I'v some more query to u-
Would u pls tell me any links or other info about this system by which I can learn more.
I want to generate some reports suppose-
call summery of particular date
call summery of how many Incomming and Outgoing call have occured by a perticular extension
etc.
I am eagerly waiting for ur valuable sugestions.
Thanks again
To produce reports you just need to work out a bunch of mysql queries. If you look at http://php.net/mysql it will show you the commands you can use to run the queries. http://www.sqlcourse.com has some tutorials on building queries. A simple query might look like :
$sql = "SELECT * FROM pabx WHERE date = '06/02/04'";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
//echo the $row values here
}
$sql = "SELECT * FROM pabx WHERE date = '06/02/04'";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
//echo the $row values here
}
-
Khairul Alam
- Forum Newbie
- Posts: 12
- Joined: Tue Sep 09, 2003 6:27 am
Thanks markl999 for ur valuable info.
I got some confusing about regular expressions of ur given code.
But I retrieve the meaning of the expressions by myself.
Here are some new query to u-
Why dont u assign values to other variable's($ring,$cd etc.) by the following line ? -
If my query is confusing, pls let me know.
Thanks again
I got some confusing about regular expressions of ur given code.
But I retrieve the meaning of the expressions by myself.
Here are some new query to u-
Would u pls tell me what u do in this section.$ring = '';
$duration = '';
$acccode = '';
$cd = '';
foreach(array_slice($parts, 5) as $check){
switch(strlen($check)){
case 4:
$ring = $check;
break;
case 8:
$duration = $check;
break;
case 2:
$cd = $check;
break;
Why dont u assign values to other variable's($ring,$cd etc.) by the following line ? -
Code: Select all
list($date, $time, $ext, $co, $dial) = array_slice($parts, 0, 5);If my query is confusing, pls let me know.
Thanks again
-
Khairul Alam
- Forum Newbie
- Posts: 12
- Joined: Tue Sep 09, 2003 6:27 am
When looking at the log file you can see that the first 5 columns are always present (date, time, ext, co, dialnumber) so the list($date, $time, $ext, $co, $dial) = array_slice($parts, 0, 5); takes care of those. The remaining fields have to be treated differently as some are filled and some arn't, but luckily each of them are a unique length.Would u pls tell me what u do in this section.
ring is always 4 chars, durations is always 8 and cd is always 2, so the code you queried above basically initialises the values to '' first, then checks the length of each of the remaining columns, this way you know which columns are filled and which are not.
With regards to your second question :
$date = '06/02/04';
$sql = "SELECT * FROM pabx WHERE date = '$date'";