CSV data extraction

Warrenw

New Member
Hi All,
Has anyone had any experience of having a 'link' written into the web page to be able to download data in CSV format from memory locations.

The reason - we want to develop a datalogger application using the Net001 units and perhaps a 28 x2 picaxe. From a web interface we want to provide a means to extract the logged data in CSV or similar.

Looking forward to your responses.

Cheers
Warren.
 

slimplynth

Senior Member
Hi Warren

I had a bash at doing something similar, didn't use csv though - not sure how you'd display this in a webpage.

I tried doing it with xml. The picaxe was continually outputting xml formatted serial data to a text file, using one of the many serial coms progs available to do such a thing (search for xml formatted data in the previous posts). This then required a rename of the file to *.xml - Boriz suggested using Auto Hot Key (AHK) to write scripts... one of which automatically renamed the txt file to *.xml

the html file included a java script to display the xml data.

Is this the kind of thing you're aiming for?

I'm sure someone on here will have a simpler/better answer - I promised earlier this year I'd learn VB.net... :eek:
 

Warrenw

New Member
What I really want to do is store data in memory and be able to retrieve it via a webpage link. Not dynamically update to the page, but just like downloading a file from the internet. I can retieve data from the memory locations and probably insert a delimiter character like a comma as required using the program in the picaxe. I would like to have a single click to retrieve the data as a download if possible.

Anyone out there up for a challenge?
 

slimplynth

Senior Member
From pg 1 of the NET001 datasheet...

• Use as an internet connection for a hardware project controlled by a PICAXE chip, so that the PICAXE control program can be remotely updated via FTP.
I really want one, didn't realise NET001 was the PNS product code 'til this dinner time. Was under the impression that the PNS was designed for exactly what you are trying to do :confused:
 

Warrenw

New Member
Well that's just it, you see I do NOT want to update the PROGRAM in the picaxe. I want to be able to extract stored data, perhaps I will need to buy one and start experimenting.

Cheers
Warren.
 

slimplynth

Senior Member
Sorry Warren, just realised from your first post that you don't have a NET001 but are thinking of buying one?

The manualis very well written, pg. 17 shows how simple it should be to save/retrieve data.
 

Warrenw

New Member
Cheers for that, I do not own one at the moment and want to assess the viability of it for a potential project I have. I will look at P17 in the manual and see what I can do to get data from it.

Thanks for the help.
Cheers
Warren.:D
 
It is easy to download data in CSV format, up to a point.
If you are talking numbers then no problem, create a file called something like "values.cgi" and put this in it: ?24,?25,?26,?27,?28

Whenever you load this file, the PNS will replace the ?24 by the value in location 24, ?25 by that in location 25 etc, leaving you with a comma separated set of data. If you want to pick up values from an attached EEPROM then you'll need some more sophisticated scripts, but its all documented and fairly straightforward.

If you want to return strings, then it gets harder. You need to surround the string in quotes and escape any quotes in the string by doubling them up, eg. 1,"a,""b""",2 is just three fields. But I'm guessing that's not what you want for a data logger.

The second problem is that the data downloaded is not given the type "CSV". Depending on whether you use Windows or something else this might mean you won't get your spreadsheet application started up to open the data - it will just be treated as text by the browser and displayed in the browser window. I would guess for a data logger the client application that is downloading the data probably won't mind. If your application is something you're writing in Javascript then you'll be using HttpXMLRequest to fetch the data and that works just fine. This is where the Javascript in a web page fetches the data from the PNS - easy - and puts it into the web page in any complicated way you wish - hard, but not impossible.

Have fun!
 

Warrenw

New Member
Simon THANK YOU.

That was just the answer I was looking for. I can now progress forward with the proposal to my client.
Cheers

:D:D:D
 
Hang on, after my last post I thought about your question a bit more and realised that CSV is not really the problem. The hard bit is getting data out of a data logger through a PNS reliably. To do this I wouldn't use CSV but would get one sample at a time.

I'll describe what I mean using a simple example - I hope it's the kind of thing you're wanting to do.

Lets have a PicAxe controlling a sensor, and every minute it grabs a reading from the sensor and places the result in a circular buffer in serial flash EEPROM that's hanging off the i2c bus. Then we have a web browser that is displaying a page which contains some Javascript. This script polls the data logger every 15 seconds and if a sample is available it downloads it and adds it to a table that the page displays. So over time you see the web page table grow with new data without having to click on anything. The PNS sits between the PicAxe and the browser and is responsible for storing the web page and controlling the data flow.

The problem is that the web page script has to get a sample just once and no sample must be lost (unless the circular buffer fills up because the web page script is not running). To fix this we need to do some handshaking between the script and the PicAxe, and this is the tricky bit. For this we use the PNS P0 output, connecting it to the PicAxe so the data logger can sense its value (either by polling or by interrupt).

The idea is that P0 acts as a "data ready" signal. The PicAxe waits for P0 to be low and the circular buffer to be non-empty. When it sees this condition it copies the first sample from the circular buffer into the PNS general purpose memory - if the samples are two bytes long we just put the sample into PNS locations 24 and 25. Then it sets the P0 signal by writing to the PNS output port register (location 16). When the PicAxe next polls it will see that P0 is set and so not write anymore data to the PNS.

The web browser script, meanwhile, is polling the PNS web server every 15 seconds. It is GETing a cgi page called data.cgi that contains "?64,?24,?25". The PNS replaces the command codes with the values of P0, location 24 and location 25 producing a CSV record like this "0,12,34" or this "1,12,34". The first integer is 0 if there is no data ready, in which case the second two numbers are old junk. The first integer is 1 if there is some data to be read, and the second two numbers are the values of the next sample. The script tests the first number and ignores the data if it is 0. However if the first number is 1, the script puts the second two into the table (this needs some advanced Javascript that manipulates the structure of the web page - lots of examples on the web or post again for more help). Having updated the table the script must acknowledge receipt of the data, allowing the PNS to serve up the next sample. To do this it must clear P0, which it does by issuing a GET with a parameter like this: "data.cgi?00=0". The data returned is ignored here, what's important is that 00=0 is a command to "switch output pin 0 off" - i.e. clear P0. Finally the script will loop to check if another sample is ready, perhaps after a brief pause, before lapsing back into its 15 second poll routine. The reason for not polling faster than this is just to avoid loading the PNS web server too much, as it will lock the PicAxe out of the i2c bus while it is serving the web requests.

Something else to consider is what happens when the circular buffer fills up - the PicAxe obviously has to throw something away, perhaps the oldest sample in the buffer. But it might be nice to set a flag to indicate that this has happened. One possibility is to have a counter of the number of samples discarded stored in a PNS memory location so the web page script can see whether data has been lost. The script can write to the location to clear it once it has noted the loss.

Finally, it is possible to write the client application in something other than Javascript, in which case you can easily construct a CSV file with the data gathered. Otherwise with more Javascript magic it is possible to write files to your local disk, though you have to setup security permissions carefully.
 

Warrenw

New Member
Well another thank you is in order, Simon you rock.

It'll take me a while to get my head around all of this, but we are in the concept stage of design at the moment so having this sort of info is invaluable. We can proceed with a proposal to our client and say we can do this sort of thing - then all I have to do is to get one, start playing and programming.

The concept will be to log events, say type 1, 2 or 3 and a date /time stamp of the event, these will be recalled later into the log page of the web server or made available as a download link via hopefully your indicated method. We will be monitoring a few inputs, some digital and one analogue and perhaps outputting the last event and the current analogue level to the LCD on the PNS.

At least I now know that I can do want we need for the client and that come the time when they say "make a sample" I will be confident that we can.

Thanks for taking the time to respond in a concise manner, have a great Xmas

Cheers from 'down under' (New Zealand).

Warren.
 
Top