[SOLVED] Howto read data from file and put into MySQL?

[elmar]

Hi all,

 

I'm trying to solve this problem as easy as possible....

 

With easy I mean, I know (after an hardware error)  which data is missing in the database.

I can retrieve the data from my weatherstation manualy into a flat file, but I want to have it in my database.

 

What I need is a php script that reads data from a flat file.

The file looks like this:

20070205193000 2007-Feb-05 19:30:00 18.4 2.5 0.7 54 88 0.0 112.5 ESE -1.00 2.5 0.00 1012.800

20070205193500 2007-Feb-05 19:35:00 18.4 2.4 0.6 54 88 0.0 135.0 SE -1.00 2.4 0.00 1012.800

20070205194000 2007-Feb-05 19:40:00 18.4 2.2 0.4 53 88 0.0 90.0 E -1.00 2.2 0.00 1012.800

20070205194500 2007-Feb-05 19:45:00 18.4 2.1 0.3 53 88 0.0 112.5 ESE -1.00 2.1 0.00 1012.900

20070205195000 2007-Feb-05 19:50:00 18.4 2.0 0.2 53 88 0.0 135.0 SE -1.00 2.0 0.00 1012.900

20070205195500 2007-Feb-05 19:55:00 18.4 1.9 0.1 53 88 0.0 157.5 SSE -1.00 1.9 0.00 1012.900

20070205200000 2007-Feb-05 20:00:00 18.5 1.9 0.1 53 88 0.0 112.5 ESE -1.00 1.9 0.00 1012.900

20070205200500 2007-Feb-05 20:05:00 18.6 1.9 0.1 53 88 0.0 135.0 SE -1.00 1.9 0.00 1012.900

20070205201000 2007-Feb-05 20:10:00 18.6 1.9 0.1 53 88 0.0 112.5 ESE -1.00 1.9 0.00 1012.900

20070205201500 2007-Feb-05 20:15:00 18.6 1.8 0.0 53 88 0.0 135.0 SE -1.00 1.8 0.00 1012.700

20070205202000 2007-Feb-05 20:20:00 18.7 1.8 0.0 53 88 0.0 90.0 E -1.00 1.8 0.00 1012.700

20070205202500 2007-Feb-05 20:25:00 18.7 1.6 -0.2 53 88 0.0 112.5 ESE -1.00 1.6 0.00 1012.700

 

The first column in the file is a timestamp, the rest is (other) data.

 

As input of the script I need to give the first and last timestamp that needs to be inserted into a MySQL database table. Let's call it weatherinfo in the database weather ;-)

The rest of the data on the same line will be inserted into various columns of a table where the timestamp is the primary key.

 

Thanks in advance, Elmar

[richardw]

Do you have the ability to download the data with a delimiter between fields, such as a "," or ";"?

 

 

[richardw]

I just used notepad and replaced the space with the ";"  . Now you can upload this directly using phpmyadmin

 

20070205193000;2007-Feb-05;19:30:00;18.4;2.5;0.7;54;88;0.0;112.5;ESE;-1.00;2.5;0.00;1012.800

20070205193500;2007-Feb-05;19:35:00;18.4;2.4;0.6;54;88;0.0;135.0;SE;-1.00;2.4;0.00;1012.800

20070205194000;2007-Feb-05;19:40:00;18.4;2.2;0.4;53;88;0.0;90.0;E;-1.00;2.2;0.00;1012.800

20070205194500;2007-Feb-05;19:45:00;18.4;2.1;0.3;53;88;0.0;112.5;ESE;-1.00;2.1;0.00;1012.900

20070205195000;2007-Feb-05;19:50:00;18.4;2.0;0.2;53;88;0.0;135.0;SE;-1.00;2.0;0.00;1012.900

20070205195500;2007-Feb-05;19:55:00;18.4;1.9;0.1;53;88;0.0;157.5;SSE;-1.00;1.9;0.00;1012.900

20070205200000;2007-Feb-05;20:00:00;18.5;1.9;0.1;53;88;0.0;112.5;ESE;-1.00;1.9;0.00;1012.900

20070205200500;2007-Feb-05;20:05:00;18.6;1.9;0.1;53;88;0.0;135.0;SE;-1.00;1.9;0.00;1012.900

20070205201000;2007-Feb-05;20:10:00;18.6;1.9;0.1;53;88;0.0;112.5;ESE;-1.00;1.9;0.00;1012.900

20070205201500;2007-Feb-05;20:15:00;18.6;1.8;0.0;53;88;0.0;135.0;SE;-1.00;1.8;0.00;1012.700

20070205202000;2007-Feb-05;20:20:00;18.7;1.8;0.0;53;88;0.0;90.0;E;-1.00;1.8;0.00;1012.700

20070205202500;2007-Feb-05;20:25:00;18.7;1.6;-0.2;53;88;0.0;112.5;ESE;-1.00;1.6;0.00;1012.700

[elmar]

Hi Richardw,

 

I have no ability to change the way the flatfile is created. The delimiter between the fields is a space " ".

Secondly I only need to insert some of the fields and the table in the database has more field that will have to stay empty.

 

Elmar

[richardw]

hi! If you paste your output into notepad as I did, then select the " " (sapce) with the mouse, got to replace, and paste the space into the "Find What" area, although you will see nothing the cursor will move, next insert the delimeter into the  "replace with" and then select replace all. Now save your file as a "txt".

 

Keep note of your field order, and in the phpmyadmin "Insert textfiles into table" option, enter the respective field names of your database  to  match the .... "If you wish to load only some of a table's columns, specify a comma separated field list." 

 

Give it a try... it works and may sound more difficult than it is. 

[.josh]

...yeah.. the point of writing a script is so that you don't have to do it manually, richardw.

 

elmar: so you have this file that is being generated, and all you need is the first line and the last line, but you need to separate the info and put it into individual columns?

 

use fopen, fgets, and feof to get the first and last lines, if it is an extremely large file.  If it's not, you might want to look into something simpler, like just using file and referencing the first and last elements of the array.

 

Whatever method you choose, after you get the line in a variable, you can explode the string at the " " and use each element in the new array, as variables for your query.

[elmar]

The file is indeed being generated.

I need all the data between two 'timestamps' seperated and put it into different columns of a MySQL table.

My idea is to start a script from the command line like this:

php /path/to/the/script.php <start-timestamp> <end-timestamp>

 

Thanks

[elmar]

After some testing.... It's solved!

 

Any suggestions for making the script better are welcome!

 

<?php

/* Connect to the weather database */
$link = mysql_connect("localhost", "<user>", "<password>")
     or die("Error connecting to MySQL");
mysql_select_db("weather")
     or die("Error on selecting database");

$fp = fopen("/var/log/open3600/test.log", "r");
while (!feof($fp)) {
  $current_line = fgets($fp);
  $value = explode (" ",$current_line);
   if ( $value[0] > $argv[1] & $value[0] < $argv[2] ) {
     $windspeed=$value[8]*3.6;
     mysql_query("insert into weatherinfo (timestamp,rec_date,rec_time,temp_in,temp_out,dewpoint,rel_hum_in,rel_hum_out,
                  windspeed,wind_direction,wind_angle,wind_chill,rel_pressure,abs_pressure)
                   select $value[0]','2007-02-08','$value[2]',$value[3],$value[4],$value[5],$value[6],$value[7],$windspeed,'$value[10]',
                    $value[9],$value[12],$value[14],$value[14]")
     or die("Error in Query: " . mysql_error());
   }
}
fclose($fp);
mysql_close();
?>

[.josh]

good job. knew you could do it.