I want to set up a monthly system performance report in Windows Excel.
Using puTTY, I can access the phpfina .dat and the phptimeseries .MYD files.
My questions are …
Using Linux/Python? - how do I convert these .dat/.MYD files to csv format and then save the csv’s somewhere?
How do I then read these csv files from Windows?
Thanks
John - Below is a PHP program to grab data from a phpfina file and send it to a csv file.
You’ll need to change these two lines to meet your needs:
$dir = "/home/pi/data/phpfina/";
$outputDir = "/home/pi/data/";
to run it, type in the command with the feed number:
$ php export_phpfina_feed_to_csv_v4.php nnn
FYI - a 2MB phpfina .dat file will become a 16MB csv file.
Download the csv file via putty and then open with MS Excel.
#!/usr/bin/php
<?php
$time_start = microtime(true);
// Directory of phpfina feeds, see: settings.php
$dir = "/home/pi/data/phpfina/"; // must end with '/'
$outputDir = "/home/pi/data/"; // must end with '/'
;
// Feed id to read:
if ($argc > 1) {
$feedid = $argv[1];
} else {
echo "no argument passed. Need feed number\n";
exit;
}
//----------------------------------------------------
// read meta data
$meta = new stdClass();
$metafile = fopen($dir.$feedid.".meta", 'rb');
fseek($metafile,8);
$tmp = unpack("I",fread($metafile,4));
$meta->interval = $tmp[1];
$tmp = unpack("I",fread($metafile,4));
$meta->start_time = $tmp[1];
fclose($metafile);
$fh = fopen($dir."$feedid.dat", 'rb');
$filesize = filesize($dir."$feedid.dat");
print "meta: " .$dir.$feedid.".meta\n";
print "feedDB: " .$dir.$feedid. ".dat\n\n";
$npoints = floor($filesize / 4.0);
$csv_file = $outputDir."feed_" .$feedid. ".csv";
$fp = fopen($csv_file, 'w');
for ($i=0; $i<$npoints; $i++)
{
$val = unpack("f",fread($fh,4));
$time = $meta->start_time + $i * $meta->interval;
$value = $val[1];
$array = array($time, $value);
if (!is_nan($value))
{
fputcsv($fp, $array);
//print $time."\t".$value."\n";
}
//print $time."\t".$value."\n";
}
fclose($fh);
fclose($fp);
print "csv_file: " .$csv_file."\n";
print "filesize: " .$filesize."\n";
print "npoints: " .$npoints."\n\n";
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Ran in $time seconds\n";
?>
PS - I believe the main part of the above PHP script came from Trystan Lea (but I could be wrong). Sorry I can’t properly credit this!
John - Below is a PHP program to grab data from a phptimeseries file and send it to a csv file.
You’ll need to change these two lines to meet your needs:
$dir = "/home/pi/data/phptimeseries/";
$outputDir = "/home/pi/data/";
to run it, type in the command with the feed number:
$ php export_phptimeseries_feed_to_csv_v7.php nnn
FYI - a 2MB phptimeseries .myd file will become a 3MB .csv file.
Download the csv file via putty and then open with MS Excel.
#!/usr/bin/php
<?php
$time_start = microtime(true);
// Directory of phptimeseries feeds, see: settings.php
$dir = "/home/pi/data/phptimeseries/"; // must end with '/'
$outputDir = "/home/pi/data/"; // must end with '/'
// Feed id to read:
if ($argc > 1) {
$feedid = $argv[1];
} else { exit("\nNo argument - need feed number\n\n"); }
$feedDB = $dir. "feed_" .$feedid. ".MYD";
$csv_file = $outputDir. "feed_" .$feedid. " .csv";
if (!file_exists($feedDB)) { exit("\nThe file $feedDB does not exist\n\n"); }
//----------------------------------------------------
$fh = fopen($feedDB, 'rb');
$fp = fopen($csv_file, 'w');
$filesize = filesize($dir."feed_$feedid.MYD");
$npoints = floor($filesize / 9.0);
for ($i=0; $i<$npoints; $i++)
{
// Read next datapoint
$d = fread($fh,9);
// Itime = unsigned integer (I) assign to 'time'
// fvalue = float (f) assign to 'value'
$array = unpack("x/Itime/fvalue",$d);
fputcsv($fp, $array);
//$time = $array['time'];
//$value = $array['value'];
//print $i."\t".bin2hex($d)."\t".$time."\t".$value."\n";
}
fclose($fh);
fclose($fp);
print "feedDB: " .$feedDB."\n";
print "csv_file: " .$csv_file."\n";
print "filesize: " .$filesize."\n";
print "npoints: " .$npoints."\n\n";
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Ran in $time seconds\n";
?>
PS - I believe the main part of the above PHP script came from Trystan Lea (but I could be wrong). Sorry I can’t properly credit this!
Jon …
Thank you very much.
I haven’t tried yet but will do so in the next few days.
Hint to Glyn & Trystan - I think this info should be included in the Learning/Resources section of the website with proper credits where due?
John - one other item. MS Excel has worksheet size limits. So if you have greater than 1,048,576 rows then Excel will throw an error.
Thanks Jon - works brilliantly. I can now plot my data in gnumeric and analyse it in python (gnumeric is a linux based spreadsheet with up to 16 million rows and great charting).
Tony
Jon …
I’m trying out a system for my son’s place (30 kW ground mount PV array). It has 3 emonTx’s (at the inverter, grid supply meter & pool ASHP) each serially direct connected to an RPi3 running emoncms. To keep each of the 3 emoncms instances ‘plain vanilla’, I have a 4th RPi3 running Raspbian on his network. I mount the feeds folder from each emoncms on this 4th RPi3 using SSHFS over SSH.
I want to run your php script on the RPi3 running Raspbian. After a lot of ‘struggoogling’, I’ve managed to run your timeseries script – had to install php5 and php5-cli.
However it throws multiple identical errors …
fputcsv() expects parameter 1 to be resource, boolean given ~~~ line 34 and then one final error …
fclose()expects parameter 1 to be resource, boolean given ~~~ line 42
In both cases, parameter 1 is defined on line 21 …
$fp = fopen($csv_file, ‘w’);
$csv_file is defined on line 15
phptimeseries_to_csv.zip (850 Bytes)
Do you have any suggestions?
Thanks
John,
Based on the error I believe the Pi with this program (the RPi3 running Raspbian) does not have permission to write at /home/pi/jb_csv_wip/.
Line 34 is fputcsv($fp, $array);. And parameter 1 is $fp. Looking at the PHP reference manual for fputcsv, parameter 1 is the handle or file pointer.
Look in line 21 at $fp = fopen($csv_file, 'w');. And I would guess no file pointer is being returned.
Add a piece of debug code to check. Insert at Line 22: print "fp: " .$fp."\n";
If there is no $fp then it cannot open to write to /home/pi/jb_csv_wip/.
Jon …
Many, many thx for your prompt insight.
I decided to gamble and take the easy route of just opening up permissions by doing …
sudo chmod 777 /home/pi/jb_csv_wip
That fixed things - it works 
Thx again
Now I need to tidy things up - think I was a bit sloppy using sudo mkdir, etc
My next challenge - how to calculate UTC and add it to the csv?
John
