Description

This is a PHP script that records data from Internet of Things (IoT) devices in a CSV spreadsheet that can be opened from Excel. If you also need a web view for the log, check the CSV Viewer for Web Browsers.

Features

• Input is automatically decoded from the URLs by using the HTTP GET standard (see examples below).
• Records different variable sets in the same spreadsheet, each variable goes to the column it belongs.
• If a new variable is set, a column for it will be automatically created.
• No specific order required for the input data.
• Records the timestamp of the request and the IP address of the client.
• Output in the CSV format that can be directly opened in Excel.
• Output is UTF-8 compatible (at lease should be).
• If the log reaches the maximum size, older entries will be automatically purged.
• Return TRUE if the record was set and FALSE in case of problems.
• The parameter ?LogClear=TRUE clears the table data and columns.
• Web display for the log available in CSV Viewer for Web Browsers.
• If a password is set, the parameter ?LogPass=yourpassword will be required to allow data to be inserted in the table.

Requirements

• Webserver with PHP 5.1.x or higher installed.
• Permissions for the script to read, write and delete in the target CSV folder.

Installation

Just put the script in the folder and run it. There is a configuration section in the code for further adjustments.

Examples

• Request 1: http://www.mysite.com/iotcsvlog.php
• Request 2: http://www.mysite.com/iotcsvlog.php?Temperature=22
• Request 3: http://www.mysite.com/iotcsvlog.php?Temperature=22&Humidity=60
• Request 4: http://www.mysite.com/iotcsvlog.php?Humidity=80&Temperature=25
• Request 5: http://www.mysite.com/iotcsvlog.php?Pressure=1000
• Request 6: http://www.mysite.com/iotcsvlog.php?Temperature=30&Pressure=900&Humidit…
• Request 7 (with password set): http://www.mysite.com/iotcsvlog.php?Humidity=50&LogPass=correct_password
• Request 8 (with password set): http://www.mysite.com/iotcsvlog.php?Humidity=50&LogPass=wrong_password
  Wrong pass! No data logged.
• Request 9 (with password set): http://www.mysite.com/iotcsvlog.php?Humidity=50
  Wrong pass! No data logged.
• Request 10: http://www.mysite.com/iotcsvlog.php?LogClear=TRUE
  New empty table is created.
• Request 11 (with password set): http://www.mysite.com/iotcsvlog.php?LogClear=TRUE&LogPass=correct_passw…
  New empty table is created.

Known issues

The script was designed to output the CSV in the UTF-8 format so it could support a wide range of languages and character sets. Unfortunately not all systems support UTF-8 in the same way and some characters might still be displayed incorrectly. To solve this problem, configure the PHP script to output the result file in the .txt format and then open it in Excel using the Text Import function. This way you will be able to proper adjust the importation character encoding.

Source code

     <!--?php
      /*=============================================================
        /   * Internet of Things IoT CSV Logger
        	* Version 1.2 (04/07/2017)
        	*
        	* This application logs any HTTP GET requests in a CSV file that can be directly opened by Excel.
        	* The GET variable names will be used as column headers and the values will sorted in the rows.
        	* The application can log completely different GET requests in the same CSV file and will automatically add the timestamp and the IP address of the client.
        	* In case of success return TRUE, in case of errors return FALSE.
        	* 
        	* Developed by Daniel BP - [email protected]
        	* http://www.danbp.org
        	* Copyright 2017 - Daniel BP
        	*
        	* This software is distributed under the MIT License.
        	* Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
        	*
        	* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
        	*
        	* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
        	*
        	*
        /* =============================================================*/
     
        //------------------------------------------------
        //Configurations
        //
        $fileName = "iotcsvlog.csv";
        date_default_timezone_set('America/Sao_Paulo');
        $delimiter = ";";
        $enclosure = '"';
        $maxLogSize = 10000; //Most Excel versions can read only about 16000 lines
        $password = ''; //Optional to prevent abuse. If set to [your_password] will require the &amp;LogPass=[your_password] GET parameter to allow the data to be recorded.
        //------------------------------------------------
     
        //Variable initialization
        $logLines = array();
        $inputLine = array();
        $responseMessage = "FALSE";
     
        if($_GET["LogClear"] === "TRUE"){
        	//If this is set, the source file will not be processed and will be erased after the new log entry is created
        }
        else if(file_exists($fileName)){ // File exists
     
        	// Reads lines of file to array
        	$fileLines = file($fileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
     
        	//Not Empty file
        	if($fileLines !== array()){
     
        		//Extract the existing header from the file
        		$lineHeader = array_shift($fileLines);
        		$logOriginalHeader = array_map('trim', str_getcsv(substr($lineHeader,3), $delimiter, $enclosure));
     
        		//Process the file only if the system could find a valid header
        		if(count($logOriginalHeader) &gt; 0) {
        			//Define the new header
        			$logHeader = array_combine($logOriginalHeader, $logOriginalHeader);
     
        			//Get each line of the array
        			$linesToSkip = (count($fileLines)+1&gt;$maxLogSize) ? (count($fileLines)-$maxLogSize+1) : 0; //Trim the log size
        			foreach ($fileLines as $line) {
        				if(trim($line) !== ''){ //Remove blank lines
        					if($linesToSkip &lt;= 0) {
        						$arrayFields = array_map('trim', str_getcsv($line, $delimiter, $enclosure)); //Convert line to array
        						$logLines[] = array_combine($logOriginalHeader, $arrayFields); //Uses the existing headers as keys for the log lines
        					}
        					else $linesToSkip--;
        				}
        			}
        		}
        	}
        }
        //Set the mandatory log headers (if they were not there during the file load)
        $logHeader["Date and Time"] = "Date and Time";
        $logHeader["IP"] = "IP";
     
        //Now process the inputs
        foreach($_GET as $key =&gt; $value){
        	//Performs some cleaning and adjustments
        	if($key == "Date and Time") $key = "Date and Time_user"; //Prevents overwriting internal data
        	if($key == "IP") $key = "IP_user"; //Prevents overwriting internal data
        	$key = preg_replace('/[^\w\-\s\.]/', '', trim($key)); //Allow only alphanumeric characters, underscore and hyphens in the header
        	$value = str_replace($enclosure, '\'', trim($value)); //Escape the delimiter and enclosure
     
        	if(($key != "LogPass" || $password ==='') &amp;&amp; $key != "LogClear"){ //Ignore the password parameter if the password is set
        		$logHeader[$key] = $key; //Adds the new header cell if needed
        		$inputLine[$key] = $value; //Defines the input array
        	}
        }
     
        //Set defaults to the system internal fields
        $outputLine["Date and Time"] = date("Y-m-d H:i:s O"); //Set the timestamp
        $outputLine["IP"] = $_SERVER['REMOTE_ADDR']; //
        $outputLine = (count($inputLine)&gt;0) ? array_merge($outputLine, $inputLine) : $outputLine;
     
        //Put the new output into the log array
        array_push($logLines, $outputLine);
     
        //Open an output temporary file, if it does not exist create it
        $fileW = fopen($fileName.".tmp","w+");
     
        //If the system was able to open the file
        if($_GET["LogPass"] === $password || $password === '')
        if($fileW !== false){
     
        	//File headers
        	fputs($fileW, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) )); //BOM for Excel UTF-8
     
        	//Write the table header
        	foreach($logHeader as $headerKey =&gt; $headerValue)
        		$finalHeader .= $enclosure.$headerValue.$enclosure.$delimiter; //Create the line
        	$finalHeader = substr_replace($finalHeader, "\n", -1); //Replace the last delimiter by a new line
        	fputs($fileW,$finalHeader); //Write to the file
     
        	//Write the table lines
        	foreach($logLines as $logKey =&gt; $logValue){
        		$finalLine = "";
        		foreach($logHeader as $headerKey =&gt; $headerValue){
        			$fieldValue = array_key_exists($headerKey, $logValue) ? $logValue[$headerKey] : ''; //Place the content under the right header
        			$finalLine .= $enclosure.$fieldValue.$enclosure.$delimiter; //Create the line
        		}
        		$finalLine = substr_replace($finalLine, "\n", -1); //Replace the last delimiter by a new line
        		fputs($fileW, $finalLine); //Write to the file
        	}
     
        	if(fclose($fileW)){ //Close the temporary log file
        		if(file_exists($fileName)) unlink($fileName); //Delete the old log file since we already have the TMP file
        		if(!file_exists($fileName) &amp;&amp; rename($fileName.".tmp",$fileName))	//Rename the temporary log file to the original name
        			$responseMessage="TRUE"; //If everything went well return TRUE
        	}
        }
        //Output the HTTP response message
        header("Content-Type: text/plain"); //Text output for compatibility with devices that don't process HTML
        echo $responseMessage;