CODEWITHSUNDEEP
phpmysqlparsingload
Matej
Matej

Reputation: 43

how to parse a log file and load data into a database

How can I parse a text file to extract data, including a date, and load it into my database table data_table?

This is my text document log.txt:

127.0.0.1 1.255.255.255 - - [30/Sep/2014:23:58:33 +0200] GET http://www.google.com HTTP/1.1 200 u:123456789 ourl:http://google.com/image ac:text ssl:1
127.0.0.1 1.255.255.255 - - [30/Sep/2014:23:58:33 +0200] GET http://www.google.com HTTP/1.1 200 u:123456789 new_data ourl:http://google.com/image ac:text ssl:1
...

these two lines are pretty similar, but in second line is the new value new_data

In my database table are these columns: 

|IP_1|IP_2|date_time|URL|HTTP_version|port|USER_ID|new_data|OURL|ac|ssl|

SQL code:

Load data local infile 'D:/log.txt' into table `data_table` fields terminated by ' ' lines terminated by '\n'

How can I parse '-','-','+0200','GET' from the text file? How should I fill the column new_data if I don't have a value in row (the best solution should be fill up with NULL )?

I would be very thankful if you could give me some ideas. Could be PHP script as well.

Upvotes: 1

Views: 2246

Answers (2)

Pixsa
Pixsa

Reputation: 599

First, file_get_contents() on the file and save its contents in a variable, then explode() it on \r\n. Now you can loop those contents like so:

// ... inside the loop:
// $log = $allLines[$i] where $i is an iterator.
// Example log value: Client IP - - [31/Aug/2017:05:48:10 +0400] "GET / HTTP/1.1" 200 1020 "http://website.com/dir" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36"

$parser = explode('"', $log);
$ip = explode(" - - ", $parser[0]);
$ip = $ip[0];
$info = $parser[1];
$location = $parser[3];
$browser = $parser[5];

echo "<tr>
    <td>IP</td>
    <td>Info</td>
    <td>Location</td>
    <td>Browser</td>
  </tr>
  <tr>
    <td>$ip</td>
    <td>$info</td>
    <td>$location</td>
    <td>$browser</td>
  </tr>";

Upvotes: 0

David162795
David162795

Reputation: 1866

since you added PHP flag, here I propose a PHP solution:

$line = "127.0.0.1 1.255.255.255 - - [30/Sep/2014:23:58:33 +0200] GET http://www.google.com HTTP/1.1 200 u:123456789 new_data ourl:http://google.com/image ac:text ssl:1";

if( preg_match('/^([^\\s]+)\\s([^\\s]+)\\s\\-\\s\\-\\s\\[([^\\]]+)\\]\\s[A-Z]+\\s([^\\s]+)\\s([^\\s]+)\\s([^\\s]+)\\su:([^\\s]+)\\s([^\\s]+)\\sourl:([^\\s]+)\\sac:([^\\s]+)\\sssl:([^\\s]+)/',$line,$m) )
{  
$v= array();
$v['IP_1']=$m[1];
$v['IP_2']=$m[2];
$v['date_time']=$m[3];
$v['URL']=$m[4];
$v['HTTP_version']=$m[5];
$v['HTTPcode']=$m[6];
$v['USER_ID']=$m[7];
$v['new_data']=$m[8];
$v['OURL']=$m[9];
$v['ac']=$m[10];
$v['ssl']=$m[11];
print_r($v);
}

note: It is not port, but HTTP return code over there.

you can learn more about PCRE on http://php.net/manual/en/book.pcre.php

Upvotes: 1

Related Questions