Author Topic: PHP to read CSV  (Read 449 times)

0 Members and 1 Guest are viewing this topic.

Offline 92merc

  • BismarckWeather.net
  • Forecaster
  • *****
  • Posts: 749
  • BismarckWeather.net
    • BismarckWeather.net
PHP to read CSV
« on: January 15, 2019, 12:45:01 PM »
Looking to see if anyone can figure out how to read a CSV file and post only certain columns from the file.

Since WU almanac is now dead, and I have no faith in them producing a free replacement, I decided to make my own Excel spreadsheet compiling data from the NWS site for normal rain, normal temps, record hi/low.

What I would need done is first get today's month and day, then match it to a row in the CSV for today's date.  Then have it display the results of column's F through M.

I've done some research, but it's out of my league for PHP.  I can tweak some existing PHP, but this is too far for me.

I have my CSV file in my /csv/bwnet.csv file.  I'll attach it for anyone to look at.  Had to rename it to TXT file to attach.  But it should be renamed to CSV.

Personally, I like to see what normal rain is for the full month.  That way I can compare it to what my station is actually reporting.  But for YTD rain, I like to see that added for each day, so I can compare that to annual rain fall reported so far by my station.

Temps are fairly straightforward.  Like to see norms and records.
http://www.BismarckWeather.net
Davis VP2, Cumulus, Blitzortung, Bloomsky, Saratoga Scripts

Offline R_o_B

  • WxElement panel
  • Senior Member
  • *****
  • Posts: 64
    • Tzouhalem-Maple Bay Weather
Re: PHP to read CSV
« Reply #1 on: January 25, 2019, 08:02:27 AM »
How about something like this (Stack Overflow):
Code: [Select]
$csv = array();
$csv = array_map( 'str_getcsv', file( 'bwnet.csv' ) );
This will produce a multidimensional array, such as:
Code: [Select]
Array
(
    [0] => Array
        (
            [0] => Month
            [1] => Day
            [2] => Mo Norm Rain Total
            [3] => Daily Norm Rain
            [4] => MTD Rain
            [5] => Yr Norm Rain Total
            [6] => YTD Rain
            [7] => Rec High
            [8] => RH Date
            [9] => Rec Low
            [10] => RL Date
            [11] => Norm High
            [12] => Norm Low
        )

    [1] => Array
        (
            [0] => 01
            [1] => 01
            [2] => 0.43
            [3] => 0.02
            [4] => 0.02
            [5] => 0.43
            [6] => 0.02
            [7] => 56
            [8] => 1998
            [9] => -41
            [10] => 1887
            [11] => 23
            [12] => 02
        )

....

    [366] => Array
        (
            [0] => 12
            [1] => 31
            [2] => 0.49
            [3] => 0.02
            [4] => 0.49
            [5] => 17.87
            [6] => 17.87
            [7] => 52
            [8] => 1963
            [9] => -13
            [10] => 1973
            [11] => 23
            [12] => 03
        )

)
Then you would need to use a couple of 'for()' loop to extract the data, first for each month, then the data for each day of the month.
R_o_B
---
eMail: weather@tzweather.org

Offline DaleReid

  • Forecaster
  • *****
  • Posts: 1440
    • Weather at Eau Claire, WI
Re: PHP to read CSV
« Reply #2 on: January 25, 2019, 08:37:59 AM »
Thanks for bringing this issue up.  Your comment and the resulting code snips are going to be helpful for something I need to do when a few other things get done.

I respect this forum and the sharing by so many intelligent folks here.
Thanks.
ECWx.info
&
ECWx.info/t/index.php

Offline daneast

  • Member
  • *
  • Posts: 37
Re: PHP to read CSV
« Reply #3 on: January 26, 2019, 06:19:31 PM »
Code: [Select]
<?php

function getWeatherStuff($filename$timezone 'America/New_York') {
$fileContents file($filename);
if (empty($fileContents)) {
//Error loading file
return null;
}

$csv array_map('str_getcsv'$fileContents);
if (empty($csv)) {
//Error parsing CSV or empty file
return null;
}

date_default_timezone_set($timezone);
$month date('m');
$day date('j');

foreach ($csv as $row) {
if ($row[0] == $month && $row[1] == $day) {
return array_slice($row58); //Rows "F" through "M"
}
}
return null;
}

//Test it
print_r(getWeatherStuff(__DIR__ '/bwnet.csv'));

Offline 92merc

  • BismarckWeather.net
  • Forecaster
  • *****
  • Posts: 749
  • BismarckWeather.net
    • BismarckWeather.net
Re: PHP to read CSV
« Reply #4 on: January 26, 2019, 06:32:51 PM »
Where would I input the filename?  bwnet.csv?
http://www.BismarckWeather.net
Davis VP2, Cumulus, Blitzortung, Bloomsky, Saratoga Scripts

Offline daneast

  • Member
  • *
  • Posts: 37
Re: PHP to read CSV
« Reply #5 on: January 26, 2019, 06:53:39 PM »
Where would I input the filename?  bwnet.csv?

The line at the bottom calls the function and gets the data:
Code: [Select]
print_r(getWeatherStuff(__DIR__ . '/bwnet.csv'));
So you could do something like....
Code: [Select]
<?php
$data 
getWeatherStuff(__DIR__ '/bwnet.csv');
?>

HTML stuff here...
Year normal rain total: <?=$data[0]?><br>
Year to date rain: <?=$data[1]?><br>

Offline 92merc

  • BismarckWeather.net
  • Forecaster
  • *****
  • Posts: 749
  • BismarckWeather.net
    • BismarckWeather.net
Re: PHP to read CSV
« Reply #6 on: January 27, 2019, 04:21:40 PM »
Worked on it a bit today.  https://www.bismarckweather.net/bwalmanac.php

Looking at my file, I actually want to use the 3rd column, then 7 through 13 or end.  Would it be easier to shuffle those columns around in Excel so they are in a row?

Lastly is how to format them for the screen.  If you look at my site now, I have a "normal rain" section just below the UV amounts.  I'd like to use the values in column 3 and 7 for those values.  https://www.bismarckweather.net/

Then another section named Normal Highs/Lows instead of Normal Rain.  With a line below looking the same as Normal Rain with those records.  This would use the last two column values.

Then another section named Record Highs/Lows instead of Normal Rain.  With a line below looking the same as Normal Rain with those records.  Then this one would use the 8-11 values.  So in the end, there will be 3 total sections.

Would it be easier to use the column header names as the "values" instead of the
  • , [1], etc?  I guess I don't want the brackets printed on screen.  Just the values within.


Thanks for the help.  Getting the data out worked out easier than I had expected.  It's mainly a matter of getting it formatted now.
http://www.BismarckWeather.net
Davis VP2, Cumulus, Blitzortung, Bloomsky, Saratoga Scripts

Offline daneast

  • Member
  • *
  • Posts: 37
Re: PHP to read CSV
« Reply #7 on: January 27, 2019, 05:59:55 PM »
I can't help you with integration into your site - you're probably using some sort of templating system or the like and I don't have the time to get into that.  For the pure PHP side here is an updated routine.  It returns an array of all data in the row, and the indices are the values in the first row with spaces removed.  Here is the PHP:

Code: [Select]
<?php

function getWeatherStuff($filename$timezone 'America/New_York') {
$fileContents file($filename);
if (empty($fileContents)) {
//Error loading file
return null;
}

$csv array_map('str_getcsv'$fileContents);
if (empty($csv)) {
//Error parsing CSV or empty file
return null;
}

date_default_timezone_set($timezone);
$month date('m');
$day date('j');

$headers $csv[0];
foreach ($headers as $idx=>$value) {
$headers[$idx] = str_replace(' '''$value);
}

foreach ($csv as $row) {
if ($row[0] == $month && $row[1] == $day) {
$rc = [];
foreach ($row as $idx => $column) {
$rc[$headers[$idx]] = $column;
}
return $rc;
}
}
return null;
}

Then to pull out a value in your page, call the routine just once:
Code: [Select]
$todayData = getWeatherStuff (__DIR__ . '/bwnet.csv');
Then to access the data use the Row Names in the CSV, but with spaces removed (and it is case sensitive).  Example:
Code: [Select]
We are in our HTML now.
Normal Monthly Rain: <?=$todayData['MoNormRainTotal']?><br>
Year To Date Rain: <?=$todayData['YTDRain']?><br>
Record high: <?=$todayData['RecHigh']?> in <?=$todayData['RHDate']?><br>
Record low: <?=$todayData['RecLow']?> in <?=$todayData['RLDate']?><br>
Normal high: <?=$todayData['NormHigh']?><br>
Normal low: <?=$todayData['NormLow']?><br>

All those indexes like 'NormLow' come from the top row of the CSV.  So technically this routine will work on ANY csv as long as the top row contains column names, the first column is the month number, and the second column is the day of the month number.

Offline 92merc

  • BismarckWeather.net
  • Forecaster
  • *****
  • Posts: 749
  • BismarckWeather.net
    • BismarckWeather.net
Re: PHP to read CSV
« Reply #8 on: January 27, 2019, 07:17:17 PM »
OK, thanks.  I'll look into it more later in the week when I have more time.
http://www.BismarckWeather.net
Davis VP2, Cumulus, Blitzortung, Bloomsky, Saratoga Scripts