www.webdeveloper.com
Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 55

Thread: Save an HTML table as CSV

  1. #31
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    Kirby - Your synopsis of the layout is correct. The way we are currently seeing the information is by using TeamViewer to log in to each laptop, then using the browser on each laptop to log in to the central device and see the stats, all on one page for all devices connected. As you can imagine, with 150+ locations, it is a full time job in it's own.

    Tcobb - The ultimate goal is to have the laptops pull the information and send it to a centralized MySQL database. This is why I'm trying to program a script to log in, and save the HTML information for export into the database.

  2. #32
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    865
    Alright, now that I feel I have a better handle on the structure you have, my next question would be if it's possible for you to set up a webpage that can be executed on each individual laptop (hourly or at any interval necessary)? The webpage would be a typical .html file; I ask because I'm not really familiar with having webpages open up and run automatically.

    Ultimately my idea for a potential solution is to have a webpage that runs an AJAX request to log in to the central device and then takes the data returned (a webpage with a table) and parses this data into an organized variable format (JSON). This very same script would then send one final AJAX request to your centralized server, where a PHP file would receive the JSON object and write it to a MySQL database. This should avoid all of the previous issues as these AJAX request will be running directly on the local machines, thus the internal IP will not be an issue.

    So if you are certain you can get the .html webpages to run automatically (VBS or perhaps scheduled task on the laptop?) then I can give you a script that will send login data to the central device, request the table, parse the data and then post to a PHP/MySQL script.
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  3. #33
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    I can set up a scheduled task to open a website in the background. Not an issue there. Just a reminder, the first row of the table we are trying to pull has a header row that will not be needed. Additionally, I will need the computer name, which I can get through VBS if needed. The computer name will need to be one of the items on each row.

  4. #34
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    865
    The header row I assume contains column names? They would be necessary in organizing the data but obviously wouldn't be written as bits of data to the database. And there is no way with javascript (or PHP for that matter) to reliably get the computer name, so this would need to be handled by you somehow. If at all possible I would say pass the computer name into the URL when launching the page (eg. somepage.html?c=COMPUTER_NAME).


    Also, for clarification on the login system, where does the login page post to? This is where AJAX will need to submit the username and password.
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  5. #35
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    I can pass the computer name in the URL. That is not a problem, just tell me how to format the URL.

    The login page is: http://192.168.69.154/login.cgi?uri=/stalist.cgi

    Here is the source code for that page:

    HTML Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/DTD/loose.dtd">
    <html>
    <head>
    <title>Login</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta http-equiv="Pragma" content="no-cache">
    <meta http-equiv="Expires" content="0">
    <meta http-equiv="Cache-Control" content="no-cache">
    <link rel="shortcut icon" href="/140123.1659/favicon.ico" >
    <link href="/140123.1659/login.css" rel="stylesheet" type="text/css">
    <link href="/140123.1659/style.css" rel="stylesheet" type="text/css">
    <link href="/140123.1659/help.css" rel="stylesheet" type="text/css">
    <script type="text/javascript" language="javascript" src="jsl10n.cgi"></script>
    <script type="text/javascript" src="/140123.1659/js/jquery.js"></script>
    <script type="text/javascript" src="/140123.1659/util.js"></script>
    <script type="text/javascript" src="/140123.1659/index.js"></script>
    <script type="text/javascript" src="/140123.1659/js/jquery.ui.js"></script>
    <script type="text/javascript" language="javascript">
    //<!--
    var globals = {
    	first_login : false,
    	postback : false,
    	fixed : false,
    	country : ""
    };
    
    function onLangChange() {
    	$("#lang_changed").val("yes");
    	$("#loginform").submit();
    }
    
    function validateForm() {
    	if ($("#lang_changed").val() == "yes")
    		return true;
    
    	if ($("#country").val() == "0") {
    		$("#errmsg").text("Please select your country.");
    		return false;
    	}
    
    	if (!$("#agreed").is(":checked")) {
    		$("#errmsg").html("To use this product, you must agree to<br/>terms of use.");
    		return false;
    	}
    
    	return true;
    }
    
    $(document).ready(function() {
    	$("#username").focus();
    	cache_images([
    		'main_top.png', 'main.png', 'link.png',
    		'net.png', '4dv.png', 'srv.png',
    		'system.png', 'border.gif', 'spectr.gif']);
    
    	if (globals.first_login) {
    		$("#ui_language").change(onLangChange);
    		$("#loginform").submit(validateForm);
    		if (!globals.postback && !globals.fixed)
    			$("#country").val(0);
    		else
    			$("#country").val(globals.country);
    	}
    });
    //-->
    </script>
    </head>
    
    <body>
    <table border="0" cellpadding="0" cellspacing="0" align="center" class="loginsubtable">
    <form enctype="multipart/form-data" id="loginform" method="post" action="/login.cgi">
    	<tr>
    		<td valign="top"><img src="/140123.1659/images/airos_logo.png"></td>
    		<td class="loginsep">
    				<input type="hidden" name="uri" id="uri" value="/stalist.cgi" />
    				<table border="0" cellpadding="0" cellspacing="0" class="logintable" align="center">
    					<tr>
    						<td colspan="2" align="center">
    							<div id="errmsg" class="error">
    								
    							</div>
    						</td>
    					</tr>
    					<tr>
    						<td colspan="2">&nbsp;</td>
    					</tr>
    					<tr>
    						<td><label for="username">Username:</label></td>
    						<td><input type="text" name="username" id="username" /></td>
    					</tr>
    					<tr>
    						<td><label for="password">Password:</label></td>
    						<td><input type="password" name="password" id="password" maxlength="8"/></td>
    					</tr>
    					
    					<tr>
    						<td colspan="2">&nbsp;</td>
    					</tr>
    				</table>
    		</td>
    	</tr>
    	
    	<tr>
    		<td colspan="2">
    		
    		</td>
            </tr>
            <tr>
    		<td colspan="2" class="submit" align="right">
    			<input type="submit" value="Login" />
    		</td>
    	</tr>
    </form>
    </table>
    </body>
    </html>
    You are correct, the header row is simply column names. As long as we know (and are able) to omit those from entry into the database.

  6. #36
    Join Date
    Mar 2009
    Posts
    492
    Just a minor suggestion--store the computer name in local storage. If one isn't there, have the page prompt the user to give it. That way you can send it along with the AJAX data as well without having to mess with the url.

  7. #37
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    865
    Tcobb, I think the ultimate idea is for this to run without any user input (unless I'm mistaken) so a prompt will negate that concept.

    Also, I wanted to avoid some 'spammy' post as this next bit will involve some back-and-forth so I can gather the information necessary to make this work with your system, but it seems you don't have PMs enabled, TW79.

    In any case I assembled a simple AJAX-based login but I have no clue what the return value will be. This script will place the return value from the AJAX login into a textarea. I simply need to know what this value is (minus any sensitive/private information).
    HTML Code:
    <!doctype html>
    <html>
    <head>
    	<title>Auto-Login | Table to JSON | JSON Upload</title>
    </head>
    <body>
    
    <input type="button" value="Click to Login" onclick="_Login()" />
    <textarea id="ajaxResult" style="width: 640px; height: 480px;"></textarea>
    
    <script>
    	var $u = "USERNAME";
    	var $p = "PASSWORD";
    	function _Login() {
    		$a = new _$AJAX("http://192.168.69.154/login.cgi", "POST", true, "username="+$u+"&password="+$p, function($a, $b) {
    			if($a != "") _$("ajaxResult").innerHTML = $a;
    		});
    	}
    	
    	// Misc functions for convenience
    	if(typeof(_$) != "function") function _$($e) { return ($e !== null && $e !== undefined) ? document.getElementById($e) : document; }
    	function _$AJAX($a, $b, $c, $d, $e) {
    		var $x = this;
    		$x.resultData = "";
    		$x.$oAjax = "";
    		if(window.XMLHttpRequest) { $x.$oAjax = new XMLHttpRequest();
    		} else { $x.$oAjax = new ActiveXObject("Microsoft.XMLHTTP"); }
    		if($x.$oAjax) {
    			$x.$oAjax.onreadystatechange = function() {
    				if($x.$oAjax.readyState == 4) {
    					$x.resultData = $x.$oAjax.responseText;
    					if($e !== null) $e($x.resultData, $x.$oAjax.status);
    				}
    			}
    			$x.$oAjax.open($b, $a, $c);
    			$x.$oAjax.setRequestHeader("Content-type","application/x-www-form-urlencoded");
    			$x.$oAjax.send($d);
    		}
    		this._cancel = function(){ this.$oAjax.abort(); };
    	}
    </script>
    
    </body>
    </html>
    I don't really know how the login system is designed to work. If it uses cookies then technically this return value won't matter. The server (central device) should be setting a cookie for the individual once the login is successful. And since this is really a client-side script, once the cookie has been set AJAX should be able to request table page via GET.
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  8. #38
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    I'm searching for a way to enable PM's, but it doesn't seem obvious to me. Where can I enable them?

  9. #39
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    Here is the page after login:

    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <title>Associated Stations</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta http-equiv="Pragma" content="no-cache">
    <link rel="shortcut icon" href="/140123.1659/favicon.ico" >
    <link href="/140123.1659/style.css" rel="stylesheet" type="text/css">
    </head>
    
    
    <body class="popup">
    <script type="text/javascript" src="/140123.1659/js/jquery.js"></script>
    <script type="text/javascript" src="/140123.1659/js/jquery.l10n.js"></script>
    <script type="text/javascript" src="/140123.1659/js/jquery.utils.js"></script>
    <script type="text/javascript" src="/140123.1659/js/jquery.dataTables.js"></script>
    <script type="text/javascript" src="/140123.1659/util.js"></script>
    <script type="text/javascript" language="javascript">
    
    
    var l10n_stalist = {
        'day' : "day",
        'days' : "days",
        'unknown' : "unknown",
        'AP-WDS' : "AP-WDS",
        'No Associated Stations' : "No Associated Stations",
        'Loading, please wait...' : "Loading, please wait...",
        '_' : '_'
    };
    
    
    var ab5BeamAngles = [
        '<img src="/140123.1659/images/ab5-p39.png" title="+39 degrees">',
        '<img src="/140123.1659/images/ab5-p26.png" title="+26 degrees">',
        '<img src="/140123.1659/images/ab5-p13.png" title="+13 degrees">',
        '<img src="/140123.1659/images/ab5-0.png" title="0 degrees">',
        '<img src="/140123.1659/images/ab5-m13.png" title="-13 degrees">',
        '<img src="/140123.1659/images/ab5-m26.png" title="-26 degrees">',
        '<img src="/140123.1659/images/ab5-m39.png" title="-39 degrees">',
        '<img src="/140123.1659/images/ab5-bcast.png" title="bcast">',
    ];
    
    
    var sl_global = {
        'wlan_iface' : 'ath0',
        'autoack' : ('enabled' == 'enabled'),
        'ack' : '24',
        'distance' : '0',
        'airmax_on' : false,
        'phased_array' : ('' == '1'),
        'beam_angles' : ab5BeamAngles, /* Currently we only have Airbeam-5, need selectivty in future */
        '_': '_'
    };
    
    
    </script>
    <script type="text/javascript" src="/140123.1659/stalist.js"></script>
    
    
    <br>
    <form action="/stalist.cgi" method="GET">
    <table cellspacing="0" cellpadding="0" align="center">
        <tr>
            <td>
                <table id="sta_list" class="listhead dataTables_head" cellspacing="0" cellpadding="0">
                    <thead>
                        <tr>
                            <th>Station MAC&nbsp;&nbsp;&nbsp;</th>
                            <th>Device Name&nbsp;&nbsp;&nbsp;</th>
                            <th>Signal / Noise, dBm&nbsp;&nbsp;&nbsp;</th>
                            <th class="initial_hide">Beam&nbsp;&nbsp;&nbsp;</th>
                            <th class="initial_hide">Distance&nbsp;&nbsp;&nbsp;</th>
                            <th>TX/RX, Mbps&nbsp;&nbsp;&nbsp;</th>
                            <th>CCQ, %&nbsp;&nbsp;&nbsp;</th>
                            <th>Connection Time&nbsp;&nbsp;&nbsp;</th>
                            <th>Last IP&nbsp;&nbsp;&nbsp;</th>
                            <th>Action&nbsp;</th>
                            <th>&nbsp;</th>
                            <th>&nbsp;</th>
                        </tr>
                    </thead>
                    <tbody>
                    </tbody>
                </table>
            </td>
        </tr>
        <tr>
            <td class="change">
                <input type="button" id="_refresh" value="Refresh">
            </td>
        </tr>
    </table>
    </form>
    </body>
    </html>

  10. #40
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    865
    Alright... there's a lot of parts to this one so bare with me as I don't expect it to work on its first try. Essentially when this page is loaded it will log in to the central device, grab the table data, convert it to a JSON object and then submit it to a PHP script that will write to your database.

    You will need to update the username, password and phpScript variables (located at the top of the <script> tag) in order to have this work (or at least attempt to work) properly.
    HTML Code:
    <!doctype html>
    <html>
    <head>
    	<title>Auto-Login | Table to JSON | JSON Upload</title>
    </head>
    <body>
    
    <script>
    	var $u = "USERNAME";
    	var $p = "PASSWORD";
    	var $phpScript = "http://yourserver.com/path/to/php/script.php";
    	function _Login() {
    		var $a = new _$AJAX("http://192.168.69.154/login.cgi", "POST", true, "username="+$u+"&password="+$p, function($a, $b) {
    			if($a != "") _TableToJSON($a);
    		});
    	}
    		
    	var $jsonData = [];
    	function _TableToJSON($a) {
    		var $colNames = [];
    		var $tmpTable = document.createElement("div");
    		$tmpTable.id = "tmpTable";
    		$tmpTable.innerHTML = $a;
    		document.body.appendChild($tmpTable);
    		
    		var $tableElement = document.getElementById("tmpTable");
    		$tableElement.style.display = "none";
    		//$tableElement = $tableElement.childNodes[0];
    		$tableElement = document.getElementById("sta_list");
    		var $tableNodes = [];
    
    		for(var $i = 0; $i < $tableElement.childNodes.length; $i++) {
    			if($tableElement.childNodes[$i].nodeType == 1) $tableNodes.push($tableElement.childNodes[$i]);
    		}
    		
    		var $headOffset = "";
    		for(var $j = 0; $j < $tableNodes.length; $j++) {
    			var $tmpRow = $tableNodes[$j].childNodes;
    			var $rowCount = 0;
    			for(var $k = 0; $k < $tmpRow.length; $k++) {
    				if($tmpRow[$k].nodeType == 1) {
    					if($headOffset == "") $headOffset = $k;
    					var $tmpCols = $tmpRow[$k].childNodes;
    					var $colCount = 0;
    					for(var $l = 0; $l < $tmpCols.length; $l++) {
    						if($tmpCols[$l].nodeType == 1) {
    							if($k == $headOffset && $j == 0) {
    								$colNames.push($tmpCols[$l].innerHTML.replace(/&nbsp;/g, ""));
    							} else {
    								if($jsonData[$rowCount] == null || $jsonData[$rowCount] == undefined) $jsonData[$rowCount] = {};
    								$jsonData[$rowCount][$colNames[$colCount]] = $tmpCols[$l].innerHTML;
    							}
    							$colCount++;
    						}
    					}
    					$rowCount++;
    				}
    			}
    		}
    		if($jsonData.length > 0) {
    			_SubmitData();
    		}
    	}
    	
    	function _SubmitData() {
    		var $a = new _$AJAX($phpScript, "POST", true, "data="+JSON.stringify($jsonData), function($a, $b) {
    			if($a != "") console.log("I guess all is well...");
    		});
    	}
    	
    	// Misc functions for convenience
    	if(typeof(_$) != "function") function _$($e) { return ($e !== null && $e !== undefined) ? document.getElementById($e) : document; }
    	function _$AJAX($a, $b, $c, $d, $e) {
    		var $x = this;
    		$x.resultData = "";
    		$x.$oAjax = "";
    		if(window.XMLHttpRequest) { $x.$oAjax = new XMLHttpRequest();
    		} else { $x.$oAjax = new ActiveXObject("Microsoft.XMLHTTP"); }
    		if($x.$oAjax) {
    			$x.$oAjax.onreadystatechange = function() {
    				if($x.$oAjax.readyState == 4) {
    					$x.resultData = $x.$oAjax.responseText;
    					if($e !== null) $e($x.resultData, $x.$oAjax.status);
    				}
    			}
    			$x.$oAjax.open($b, $a, $c);
    			$x.$oAjax.setRequestHeader("Content-type","application/x-www-form-urlencoded");
    			$x.$oAjax.send($d);
    		}
    		this._cancel = function(){ this.$oAjax.abort(); };
    	}
    	
    	(function(){ _Login(); })();
    </script>
    
    </body>
    </html>
    The final part is the PHP script which will write everything to the database. I don't know anything about your database (column names, etc.) so you will have to do more editing with this script in order to get things working. This script is designed to take the JSON object and loop through it (in the event that you have a table with multiple rows) and write these rows to a database. There are variables (indicated by ALL CAPS, eg. USERNAME) which need to be updated so it can connect to your database. You'll also want to update the column names in the query so it writes to your database properly. This needs to be uploaded to your central server where your MySQL database is located, and of course that address gets entered back into the HTML page above (for the $phpScript variable).
    PHP Code:
    <?php
        
    class dbObj {
        function 
    _query($u$p$db$q) {
          
    $dbh = new PDO('mysql:host=localhost;dbname='.$db$u$p);
          
    $dbh->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
          return 
    $dbh->prepare($q);
        }
      }
        
        
    $dbObj = new dbObj;
        
    $jsonData = (isset($_POST["data"])) ? json_decode($_POST["data"]) : array();
        
        for(
    $i 0$i count($jsonData); $i++) {
            
    $q $dbObj->_query("USERNAME""DB_PASSWORD""DB_NAME""INSERT INTO TABLE_NAME (station_mac, device_name, signal_noise, beam, distance, tx_rx, ccq, connection_time, last_ip, action) VALUES ('" $jsonData[$i]["Station MAC"] . "', '" $jsonData[$i]["Device Name"] . "', '" $jsonData[$i]["Signal / Noise, dBm"] . "', '" $jsonData[$i]["Beam"] . "', '" $jsonData[$i]["Distance"] . "', '" $jsonData[$i]["TX/RX, Mbps"] . "', '" $jsonData[$i]["CCQ, %"] . "', '" $jsonData[$i]["Connection Time"] . "', '" $jsonData[$i]["Last IP"] . "', '" $jsonData[$i]["Action"] . "')");
            
    $q->execute();
        }
        
        echo 
    "success, maybe?";
    ?>

    Lastly, I did not include the code that handles the computer name. That is one of the simpler things to do if you pass it into the URL so we'll worry about that after I can ensure this will work.
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  11. #41
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    Wow! Thank you for working so hard on this project.

    I changed the USERNAME and PASSWORD variables, and also changed the path to the PHP file.

    I changed the column names in the PHP so the data will go to the correct column (hopefully).

    When I run the script with the edits, I am getting the following error:

    Code:
    XMLHttpRequest cannot load http://192.168.69.154/login.cgi. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost' is therefore not allowed access.
    Thoughts?

  12. #42
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    I also get this error when running the HTML directly, without localhost:

    XMLHttpRequest cannot load http://192.168.69.154/login.cgi. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'null' is therefore not allowed access.

  13. #43
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    865
    Foiled by cross-domain scripting...
    AJAX is very particular about where it sends request. If the request is to another domain then it's not allowed. And technically this falls into that basket as the IPs (while on the same network) are different.


    jQuery supposedly supports cross-domain AJAX request, though I'm not all that familiar with it. I do know that it doesn't allow asynchronous request (meaning the browser sort of... 'pauses' while the request is in process (kind of like waiting on a page to load normally)), but I doubt that'll be an issue.

    Give me a bit to plug jQuery AJAX requests into my code instead and then you can give it another try. I'll be a bit busy today but I'll try to have that available within the next few hours.
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  14. #44
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    This error is on line 1, before anything else is really declared. Is there a simple one-liner type code that would remedy this? I've been searching online for a solution but AJAX is all Greek to me.

  15. #45
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    865
    The error might return line one but that's because the script is set to run on window load. So since the script initiates as soon as the page has loaded the error triggers immediately. Honestly, don't ask me why but errors can often do that. I never really delved into why.

    It's actually looking like jQuery can't force cross-domain request if the server doesn't allow it (hence the 'Access-Control-Allow-Origin' header). I don't suppose you have any access to that central device server to where you could have it set up to accept cross domain request? To be fair I'm not entirely sure how to do this (assuming you can't make the change) but I do recall investigating this stuff before and know it won't be too difficult to figure out.


    Unfortunately I can't think of a way to get around this issue. I honestly would expect that AJAX would be allowed to preform any request within the same local network, but I can't seem to find anything that indicates how (if it is possible). I do wish I had a remedy because at this point it's really the only thing left to solve for your issue. I'll dig some more to see if there's a way I can get around the cross-domain issues and post back if I figure it out, or come up with another method of getting your overall desired result.
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles