ssjrocks Posted December 24, 2009 Share Posted December 24, 2009 hey guys, i need some help with importing a logfile into sql im sure there are tons easier ways to do every part of the script so i was hoping for a nudge in the right direction 1st is an example of the file im importing 1841526 19:41:01 22-12-09 POCSAG-4 NUMERIC 512 TONE ONLY 1841527 19:41:01 22-12-09 POCSAG-4 NUMERIC 512 TONE ONLY 0370377 19:41:05 22-12-09 POCSAG-3 ALPHA 1200 19:40 P101686..ADA2 0547216 19:41:11 22-12-09 POCSAG-1 ALPHA 1200 19:40 P90393..MSG1.MON.F2 0411456 19:41:16 22-12-09 POCSAG-3 ALPHA 1200 J230059,4.2,:HH130B 117,62 COLUMBIA CR MODBURY_NORTH,PETE"?)'agi 1517188 19:41:16 22-12-09 POCSAG-1 NUMERIC 1200 2578 1601773 19:41:16 22-12-09 POCSAG-4 NUMERIC 1200 TONE ONLY 0866741 19:41:16 22-12-09 POCSAG-3 NUMERIC 1200 TONE ONLY 0404958 19:41:16 22-12-09 POCSAG-2 NUMERIC 1200 TONE ONLY 0465590 19:41:16 22-12-09 POCSAG-2 NUMERIC 1200 2709 2058471 19:41:17 22-12-09 POCSAG-4 ALPHA 1200 v?v???ff.&?"? &6O?/?"G?&?BN??j?Nv??/?7O?.??/?J???~nNv>? >?N?2?n?"G? 0511406 19:41:18 22-12-09 POCSAG-3 ALPHA 1200 2212-19:40 CONTACT STATE GOVT SECURITY 07-3224-6666 RE ALARM 0847399 19:41:18 22-12-09 POCSAG-3 ALPHA 1200 19:40 P253441.SVRS2 as you can see thers alot of special chars coming through that i have to deal with hence the screwing around with mysql_real_escape_string and what not. and here is my script <?php $rimp = "0"; $dup = "0"; if ($_FILES["file"]["error"] > 0) { echo "Return Code: " . $_FILES["file"]["error"] . "<br />"; } else { echo "Upload: " . $_FILES["file"]["name"] . "<br />"; echo "Type: " . $_FILES["file"]["type"] . "<br />"; echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />"; echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />"; $rndmna = rand(1, 256); if (file_exists("upload/" . $_FILES["file"]["name"])) { echo $_FILES["file"]["name"] . " already exists. <BR>"; move_uploaded_file($_FILES["file"]["tmp_name"], "upload/" . $rndmna . $_FILES["file"]["name"]); echo "Renamed and Stored in: " . "upload/" . $rndmna . $_FILES["file"]["name"]; } else { move_uploaded_file($_FILES["file"]["tmp_name"], "upload/" . $_FILES["file"]["name"]); echo "Stored in: " . "upload/" . $_FILES["file"]["name"]; } } echo "<BR><br>Processing."; flush(); $con = mysql_connect("localhost","root","012142"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("spl1nt", $con); $spc = " "; $tcnt = ""; $filename = "upload/" . $_FILES["file"]["name"]; $fp = @fopen($filename, 'r'); if ($fp) { $array = explode("\n", fread($fp, filesize($filename))); } foreach ($array as $k => $v) { $tcnt ++; $tccnt = count($array); $fields = preg_split('//', $array[$k]); $fcnt = count($fields); // wow @ below.... gotta be an easier way if(isset($fields[0])){$fieldx[0] = $fields[0] . $fields[1] . $fields[2] . $fields[3] . $fields[4] . $fields[5] . $fields[6] . $fields[7];} if(isset($fields[9])){$fieldx[1] = $fields[9] . $fields[10] . $fields[11] . $fields[12] . $fields[13] . $fields[14] . $fields[15] . $fields[16];} if(isset($fields[17])){$fieldx[2] = $fields[18] . $fields[19] . $fields[20] . $fields[21] . $fields[22] . $fields[23] . $fields[24] . $fields[25];} if(isset($fields[27])){$fieldx[3] = $fields[27] . $fields[28] . $fields[29] . $fields[30] . $fields[31] . $fields[32] . $fields[33] . $fields[34];} if(isset($fields[36])){$fieldx[4] = $fields[36] . $fields[37] . $fields[38] . $fields[39] . $fields[40] . $fields[41] . $fields[42] . $fields[43];} if(isset($fields[43])){$fieldx[5] = $fields[43] . $fields[44] . $fields[45] . $fields[46] . $fields[47];} if(isset($fields[49])){$fieldx[6] = $fields[49] ;} //$fields[17] if($fields[0] >= 0){ $h=0; $tst1 = ''; foreach ($fields as $d) { if ($h > '49'){ $tst1 = $tst1 . $d; } $h++; } // $qry11 = "SELECT * FROM data WHERE Address='$fieldx[0]' and Date ='$fieldx[2]' and Msg='$tst1'"; // echo "<BR>pp" . $fieldx[0] . "pp"; echo "<BR>"; //echo "SELECT * FROM data WHERE Address='$fieldx[0]' and Date ='$fieldx[2]' and Msg='$tst1'";// echo "pp" . $fieldx[2] . "pp"; echo $checkstr; echo "<BR>" . $check . "<BR>" . mysql_num_rows($check); $checkstr = "SELECT * FROM data WHERE Address='$fieldx[0]' and Date ='$fieldx[2]' and Msg='$tst1'"; $check = mysql_query($checkstr,$con); if($fieldx[0] != "" || $fieldx[2] != "" || $tst1 != ""){ if(mysql_num_rows($check) >= 1) { $dup ++; } else; { if(mysql_num_rows($check) == 0) { if($tst1 == "- Smash -" ){break;} $tst1 = mysql_real_escape_string($tst1); $sqlqu="INSERT INTO Data (Address, Time , Date, p1234, Type, Baud, Msg) VALUES ('$fieldx[0]', '$fieldx[1]', '$fieldx[2]', '$fieldx[3]', '$fieldx[4]', '$fieldx[5]', '$tst1')"; $rimp ++; if (!mysql_query($sqlqu,$con)) { die('Error: ' . mysql_error()); } } } echo"."; } } } echo "<BR><BR>"; echo $rimp . " records imported"; echo "<BR>" . $dup . " Duplicates Ignored"; mysql_close($con); ?> it looks ugly as but im fairly new to this i keep getting Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2\htdocs\doimp2.php on line 111 Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2\htdocs\doimp2.php on line 116 Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2\htdocs\doimp2.php on line 124 and when i got parts of it to work without errors it would execute both parts of my if loop to check for dups tally up the $rimp and $dup variables then insert it into the database anyway any help given would be much apreciated. Quote Link to comment Share on other sites More sharing options...
gerkintrigg Posted December 24, 2009 Share Posted December 24, 2009 have you looked into explode() or preg_replace()? Though you might be better off with some kind of preg_match function and then loop through the matches. Try a for each loop. Quote Link to comment Share on other sites More sharing options...
ssjrocks Posted December 24, 2009 Author Share Posted December 24, 2009 i had very limited sucess with preg split so for compatibility i decided to explode the file on new line and preg split on all then crazy foreach loops Quote Link to comment Share on other sites More sharing options...
ChemicalBliss Posted December 24, 2009 Share Posted December 24, 2009 Looks like a fixed-width file. If indeed each field has a specific amount of characters (with space as padding, as is in your example): This class can convert the fixed-width files into CSV format, which can be read easily by fgetcsv(); Fixed-Width Converter Class: (With Example); http://www.phpro.org/classes/Convert-Fixed-Width-File-To-CSV.html fgetcsv() (also with examples); http://php.net/fgetcsv --- Unless you want to write your own custom code, which may get buggy with different versions. if you do though, i would use something like substr, with a loop, instead of explode. -CB- Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.