Read text file and insert into mysql

[siclines]

Hey everyone,

 

I am trying to read a text file from NOAA, http://www.ndbc.noaa.gov/data/5day2/44004_5day.txt, and insert it into mysql database.

 

I have read various examples, but have had no luck. Can anyone post an example of this process?

 

Thanks,

sic

[kenrbnsn]

Please post the code you're using. We're not here to write the code for you.

 

Ken

[PFMaBiSmAd]

Edit: Longer version of above ^^^

 

Someone is likely to just post the example(s) you have already tried.

 

Perhaps the examples you have tried are not working because your server is configured to prohibit the method you used. You need to find out why the code you tried does not work. Post code you have tried along with any errors or other symptoms you got. Are you attempting this on a system where error_reporting is set to E_ALL and display_errors is set to ON so that php will show you errors that occur in the script?

 

Edit2: I guess I'll ask, which part of this are you having a problem with? Reading the site, parsing the data, or inserting it into a database?

[Carth]

Edit: also a longer version of the above.

 

Which part are you having trouble with? Do you have any code already?

 

Will you be reading the text file from that URL? Do you know how to do that part?

 

Do you know how to parse the text file into the relevant fields ready to insert?

 

Can you do an insert query to the database?

 

I'm reluctant to just write the code for you.

[siclines]

I am able to read the data from the text file, and I can print the text on the screen, I just can't get it in the db.

 

I tried various examples, I appreaciate everyone's time and I do not expect anyone to write the code for me, just trying to understand what I am doing wrong.

 

I am not getting any errors, just a blank screen.

 

 

[siclines]

here is the code excluding my connection string:

 

<?php
$file = "http://www.ndbc.noaa.gov/data/5day2/44004_5day.txt";
$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);

$output = str_replace("\t|\t", "|", $data);

$output = explode("\n", $output);


mysql_query("INSERT INTO buoy44 (Year,Month,Day,Hour,Min,WindDir,WindSpeed,Gust,WaveHT,DPD,APD,MWD,Press,Atemp,Wtemp,Dewp,Vis,ptdy,Tide) VALUES('$Year','$Month','$Day','$Hour','$Min','$WindDir','$WindSpeed','$Gust','$WaveHT','$DPD','$APD','$MWD','$Press','$Atemp','$Wtemp','$Dewp','$Vis','$ptdy','$Tide')") or die("Insert failed: " . mysql_error()); 

}
?>

 

(please use

tags when posting code)

[Carth]

I don't know if you missed some of your code. Where are $Year, $Month etc. and everything you insert being set?

 

Perhaps you need to explode() again, this time by "|", for each line. You need a loop as well, to loop over the array $output. Or have you missed some of your code out?

 

Edit: Actually, looking at the data, there are no pipes ("|") or tabs ("\t") in it, so that won't even work. I think your example code was designed for different data. Your text file looks like it is delimitted only by spaces, which makes it harder.

[mandred]

Where are all those variables you're inserting coming from?

 

The explode function is exploding the contents of each line of the text file into an array. Do you want to insert each line into its own row in the database? Or does each line contain a uniform formatting that you can use a regular expression on in order to grab each variable from each line?

 

You should really read up on some of these functions at php.net to better understand them, otherwise you're pissing in the wind.

 

edit - I just saw the text file. You can easily use a regular expression to extract the information you want into their own variables.

[siclines]

thanks for your reply, I am trying to insert each line as a row in the database.

[mandred]

foreach ($output as $newrow)
  mysql_query("INSERT INTO table (field) VALUES ('$newrow')");

[siclines]

Thanks for all your help, I made some changes, but now it only insert a blank record. No data. I read the function explode on php.net, what am I missing?

 

$file = "http://www.ndbc.noaa.gov/data/5day2/44004_5day.txt";

$fp = fopen($file, "r");

$data = fread($fp, filesize($file));

fclose($fp);

 

$output = explode(" ", $output);

 

foreach($output as $var) {

$Year = $Year;

$Month = $Month;

$Day = $Day;

$Hour = $Hour;

$Min = $Min;

$WindDir = $WindDir;

$WindSpeed = $WindSpeed;

$Gust = $Gust;

$WaveHT = $WaveHT;

$DPD = $DPD;

$APD = $APD;

$MWD = $MWD;

$Press = $Press;

$Atemp = $Atemp;

$Wtemp = $Wtemp;

$Dewp = $Dewp;

$Vis = $Vis;

$ptdy = $ptdy;

$Tide = $Tide;

 

$tmp = explode(" ", $var);

 

foreach ($output as $newrow)

mysql_query("INSERT INTO buoy44 (Year,Month,Day,Hour,Min,WindDir,WindSpeed,Gust,WaveHT,DPD,APD,MWD,Press,Atemp,Wtemp,Dewp,Vis,ptdy,Tide) VALUES('$Year','$Month','$Day','$Hour','$Min','$WindDir','$WindSpeed','$Gust','$WaveHT','$DPD','$APD','$MWD','$Press','$Atemp','$Wtemp','$Dewp','$Vis','$ptdy','$Tide')") or die("Insert failed: " . mysql_error());

 

}

[kenrbnsn]

That's not going to work. You're not assigning any values to the variables. Something like this will probably work:

 

<?php
$wx = array_map('trim',file("http://www.ndbc.noaa.gov/data/5day2/44004_5day.txt"));
$newwx = array();
foreach($wx as $i => $line) {
   if ($i > 1) {
        $tmp = array_filter(explode(' ',$line));
        $q = "insert into bouy44 (Year,Month,Day,Hour,Min,WindDir,WindSpeed,Gust,WaveHT,DPD,APD,MWD,Press,Atemp,Wtemp,Dewp,Vis,ptdy,Tide) values ('" . implode("','",$tmp) . "')";
        $rw = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
   }
}
?>

 

Ken

[siclines]

That worked, thank you very much for your help. Much shorter then what I was trying.

 

What if I only wanted the first five rows?

 

 

[kenrbnsn]

I assume you mean the first 5 rows of data, since the first 2 rows are headers. Change:

<?php
if ($i > 1) {
?>

to

<?php
if ($i > 1 && $i < 7) {
?>

 

Ken