text file into database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

text file into database

Post by nhan »

hello, i have here a text file whose data contains this kind of format, i hope someone could help me to import this kind of data into a mysql database, it is like a call format which contains the time of call, the trunk,the duration and other stuff. I need to seperate all those data and insert it in the database, the portion in the file which contains this kind of format (eac99e80-ec1f-11d9-9887-00c07b9aa0c4) is a unique one. and every call in this text file have 4 lines meaning 4 similar charracter like the one in parenthesis. hope someone could give me an idea, below is a single call format of the said test file.
start 06/20/2005 16:21:35 eac99e80-ec1f-11d9-9887-00c07b9aa0c4
X.X.X:X.X.X:1:1003:1:3:3:2 ?@(?,?,?):(00441253736755,00441253736755) --> X.Default:X.X.X --> X.X.X.Default:X.X.X

start 06/20/2005 16:21:35 eac99e80-ec1f-11d9-9887-00c07b9aa0c4
X.X.X:X.X.X:0:2049:1:6:8:21 ?@(?,?,?):(00441253736755,00441253736755) --> X.X.XDefault:X.X.X--> X.X.X.Default:X.X.X

stop 06/20/2005 16:24:26 eac99e80-ec1f-11d9-9887-00c07b9aa0c4
X.X.X:X.X.X:0:2049:1:6:8:21 171 seconds (2.85 minutes) normalDrop

stop 06/20/2005 16:24:26 eac99e80-ec1f-11d9-9887-00c07b9aa0c4
The x.x.x here are kind of gateway, ssome of them are like IP address.
thanks so much!

Any similar tutorial will do.. thanks!
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

Post by nhan »

and also the text file here has a size of 65000++ kb... hope someone could really help me... i have a code here that only splits the different portion of the line

Code: Select all

$opFile = "cdr_sample.txt";
$fp = fopen($opFile,"r") or die("Error Reading File");

$data = fread($fp, filesize($opFile));
fclose($fp);

$line = explode("\n", $data);
$line = explode(" ", $data);

$i=count($line);
for ($n=0 ; $n < $i-1 ; $n++ ) {
  $cdr = explode(",", $line[$n]);


  if (isset($cdr[0]))
   {

    echo "data= ".$cdr[0]."<br>";

    }

 }
any idea on this one??? thanks!
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

PHP will stuggle with something like 65MB of data to load.
The only way I can see to do it with PHP is to read the file in chunks or break the file apart into smaller ones (other than increasing the 8MB limit in php.ini) but either are gonna be a pain.

timvw recently mentioned this mysql feature however which sounds like ti will do what you need :)

http://dev.mysql.com/doc/mysql/en/load-data.html
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

Post by nhan »

thanks so much d11wtq, ill try to use what timvw had mentioned, actually the other day i was looking at his site, thought i might get an idea on how to do that kind of text file, hehehe.. anyways ill try to post any solution i can get...

hope that someone is still there to hand an idea too....

thanks tim! thanks d11wtq!
Post Reply