Topic: PHP to read CSV

[92merc]

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.

[R_o_B]

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.

[DaleReid]

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.

[daneast]

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($row, 5, 8); //Rows "F" through "M"
}
}
return null;
}

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

[92merc]

Where would I input the filename?  bwnet.csv?

[daneast]

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>


[92merc]

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.

[daneast]

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.

[92merc]

OK, thanks.  I'll look into it more later in the week when I have more time.

[92merc]

I just thought I would put an update to this thread I created in case anyone was ever wanting to do something similar.

Awhile ago, I had figured how how to use PHP to get today's month number in a 2 digit format.  I then would "open" the contents of that file and print to the screen it's contents.  What I did was had the screen report normal rain for the month, and normal rain YTD.  Just on a personal level, I always like to know where I stand for rain for those 2 periods.

I also used to have the WU script for the almanac that reported back Record/Normal High/Low.  With the demise of that service, I started a trail of looking for a solution I can control a bit.

Using the local NWS data, I created a spreadsheet that had all the data.  I basically used the daily normal average rainfalls to tabulate the normal rain for month, and the YTD rain.  Then the Record/Normal High/Low as well.

Since I couldn't get my above PHP to open the CSV in question, AND post it in a meaningful format, I reverted back to using my previous PHP code to open 6 files that I create.  With the help of some Excel VBA experts, they created me 5 macro's to create 5 files for each day of the year.  The last file for each month, I manually create. 

So now I have it such that I can update my spreadsheet, export out to 1830 TXT files, and upload those, and have my data updated.  About the only downside is having to dig through the NWS data for Bismarck to update the record High/Low data if that should change.

If anyone is interested, I'd be willing to give anyone my Excel file and help with getting to work on their site.  If you go to https://www.bismarckweather.net and look above the forecast, you'll see the 6 fields I'm updating.  The bonus for me is now my YTD rain increases each day and I can compare it to my actual YTD rain.  Before, I had the YTD amount only jump on the 1st of the month.

[greggw]

What you have done is exactly what I am looking for. I am new to all of this, so struggling with programming. My website is https://valleweather.com using the Saratoga Template with Ambient Weather plugin hosted on GoDaddy. In the menu, I have a page for normals which is just a table - see Menu > Wx Statistics > Daily Normals. If you would be willing help, I will gladly accept any advice on how to implement your solution.

[92merc]

I think it was beteljuice that wrote a script to pull the normal and record temps from the NWS sites.  So on my middle section, only the monthly rain, and YTD rain are pulled from my data files.

I have 12 monthly files.  So when you open the page on the first of the month, it pulls the rain data for the month.  So that file only changes once a month.  For the YTD data, I created a file for every day of the year, including leap year/day.  The data in those files increases the rain amount I have collected from NWS site.  So it ticks up every day.  I like to compare the monthly rain to what I can expect for the month to what I have.  But YTD rain I like ticked up daily to compare to my yearly rain observed.

I created a folder I named history.  I have a php script that pulls the data based on what your computer is returning for the month.  Example file would be rain would be rain04mo.txt for this month of April.  Here is the code that pulls that.

<?php $mydate = date('m'); $filename = "./history/rain".$mydate."mo.txt"; include($filename);?>

$mydate is the variable.  The date('m') means I just want to pull the month number only.

For the YTD rain that ticks up daily, the file name example for today is 0407ytd.txt.  I pull that with this script.

<?php $mydate = date('md'); $filename = "./history/".$mydate."ytd.txt"; include($filename);?>

Same $mydate, but configured for date('md').  Both of those date variables since they are small characters, pull in two digit day and month numbers.

I had to get help from an Excel forum.  But I had created a spreadsheet that I input the rain amount for each day of the year.  The spreadsheet calculated the monthly rain, and the YTD rain amounts.  The Excel forum made a macro to export it out into the individual files.  But I'm guessing you may already have a CSV you'd be exporting out to.  But in Excel terms, each field got exported into a file. 

[greggw]

Thanks - much appreciated - very helpful. However, I think this may be above my pay grade. I will need to start small. The first thing I will do is try to edit the ajax-dashboard.php file to get the middle section inserted into the dashboard. That looks to be somewhere around line 1068, give or take. Then, I will focus on temperature statistics (daily normals and records) since that data is largely fixed and we have very few days with rainfall here in Santa Fe. You mentioned a script by Beteljuice, but I have not found any working links on this forum to download that script. Is that something you may have saved and could maybe post? If not, I will experiment with my csv file that has daily normals and records and try to read that data. Again, many thanks.

[92merc]

The noaarec.php file isn't too bad.  I'll attach the file.  You first edit this file with your city's three character code.  Bismarck was 'bis' for me.  The text file has a link to find yours.  Put that file out on your site.

Then edit your ajaxdashboard. 

1)  Near the top, after the $ADBversion, I added this line to include the loading of the new PHP file.
include_once("noaarec.php");

2) Like you said, scroll down to around line 1056.  You see some closing table statements after the '<?php } // end $haveSolar  ?>'  I added this table in.

<table width="630" border="0" cellpadding="0" cellspacing="0" style = "font-weight: normal";>
<th style="text-align:center" colspan="2">National Weather Service History</th>
<tr style="text-align:center">
<td>Normal High: &nbsp;<?php echo $thisDayMaxAvg; ?>&deg;F</td>
<td>Normal Low: &nbsp;<?php echo $thisDayMinAvg; ?>&deg;F</td>
</tr>
<tr style="text-align:center">
<td>Record High: &nbsp;<?php echo $thisDayMax; ?>&deg;F &nbsp;<?php echo $thisDayMaxYear; ?></td>
<td>Record Low: &nbsp;<?php echo $thisDayMin; ?>&deg;F &nbsp;<?php echo $thisDayMinYear; ?></td>
</tr>
</table>

That should then add in your record/normal temps for the city you selected.

[greggw]

Thank you so very much. This gives me lots to work with. I will try to get this working. If successful, I may come back to you for help with precipitation.

[92merc]

I compiled the rain history for the Santa Fe Seton, NM records off the NWS site.  If you upload this history folder, then change that above code to the below, it should pull your rain records as well.

Wasn't too difficult.  A few copy and paste commands, and it was done.

<table width="630" border="0" cellpadding="0" cellspacing="0" style = "font-weight: normal";>
<th style="text-align:center" colspan="2">National Weather Service History</th>
<tr style="text-align:center">
<td>Normal Rain Month: &nbsp;<?php $mydate = date('m'); $filename = "./history/rain".$mydate."mo.txt"; include($filename);?>&nbsp; in.</td>
<td>Normal Rain YTD: &nbsp;<?php $mydate = date('md'); $filename = "./history/".$mydate."ytd.txt"; include($filename);?>&nbsp; in.</td>
</tr>
<tr style="text-align:center">
<td>Normal High: &nbsp;<?php echo $thisDayMaxAvg; ?>&deg;F</td>
<td>Normal Low: &nbsp;<?php echo $thisDayMinAvg; ?>&deg;F</td>
</tr>
<tr style="text-align:center">
<td>Record High: &nbsp;<?php echo $thisDayMax; ?>&deg;F &nbsp;<?php echo $thisDayMaxYear; ?></td>
<td>Record Low: &nbsp;<?php echo $thisDayMin; ?>&deg;F &nbsp;<?php echo $thisDayMinYear; ?></td>
</tr>
</table>

[greggw]

Thank you so very much. I will work on this next week and see how it goes.

[Silversword]

Hi All,

Just saw this post and would like to try to include this between my dashboard and Forecast on my index.php page at https://www.dwayneyamato.com/wx/.

I think I have the necessary parts to do this, however, I have not been able to find the  sid for my area: $wfo = "hfo"; $sid = "hfo";

I am located on the Island of Maui and the nearest NWS station is probably the Kahului Airport.  I am located higher up on the mountain in a town called Makawao.

Any suggestions and recommendations on how to put this data on my index page would be very much appreciated

Aloha,

--Stan Y.
   Maui, Hawaii

[greggw]

I ran into the same problem trying to find the SID for Santa Fe, NM.

[92merc]

I got lucky and my city is the NWS office for the western half of my state.  Hoping maybe Beteljuice can pop in and help out since he wrote the script.

[92merc]

Looks like BetelJuice may not be around. He hasn't checked in since 2021, and his site shows a RIP page.  So he may be gone gone.  He was always a good help.

This is the original thread, but it didn't have anything really on finding your WFO/SID.  It had links to the WD forum, but that forum has changed, so I doubt you'll be able to find anything there.

https://www.wxforum.net/index.php?topic=37220.msg439198#msg439198

[92merc]

I was able to play around with my original Excel file that I used to gather the data.  The spreadsheet has rain data for each day of the year you'd have to copy/paste into the sheet.  Then same for normal high, normal low, record high, record low.

Then I have a series of Excel Macros that export that data out into text files.  Since PHP doesn't like degree symbols, I had to bust up the data for the record high/low into one file for temp, one file for year.

For every day of the year, you end up with 7 files:
1) normal high
2) normal low
3) record high temp
4) record high date
5) record low temp
6) record low date
7) rain year to date

Plus 12 monthly rain files.  That has normal rain for the current month.  Total of 2,574 text files.

I updated my Excel sheet with my real data.  If you go to this page, you can see the History section with my live data, pulled from the history folder.

https://www.bismarckweather.net/ajax-dashboard_records.php

The nice thing is that NWS doesn't update the rain data that often.  I've only seen mine updated twice in the past 6 or so years.  But probably once or twice a year, you might need to review the record high/low temps and update the spreadsheet.  Then re-export the files back out.

[Silversword]

I got lucky and my city is the NWS office for the western half of my state.  Hoping maybe Beteljuice can pop in and help out since he wrote the script.

If I put in what I posted my area as wfo = "hfo"; $sid = "hfo"; then, I would probably have the info for the Honolulu NWS office and I am over 100 miles away on another island, so probably wouldn't be good data for me.

--Stan Y.
   Maui, Hawaii

[92merc]

You'd have to home brew your own database through my Excel file.  If there is a location near you, you could populate the Excel file from data on this site:   Just change your location to one on the list. 

https://www.weather.gov/wrh/climate?wfo=hfo

If not, you'd have to see if your weather software has the data for you.

Once you have the data in the Excel file, then you can export all the daily data out and have it show on your page.

This is how you get daily average temps from that site.  See attached.

[greggw]

I finally have what I was looking to do on my website at valleweather.com. Thanks so much for all your help. I appreciate your expertise and advice.