How to export sensor data to csv file or Excel?

[prasann16]

Hi, I want to export my door sensor data to a csv file or Excel? How would I do that! I know how to publish data into particle dashboard but how do I store that data into something like an Excel file. Thanks.

[Moors7]

The easiest method would probably be IFTTT and google docs. Try searching the forum for either of those terms, and you should be able to find some info

[prasann16]

Yes! I found a way to do it with Google Sheets. But I have to open it each time and run the script. Is there any other ways you might know that can do it automatically and I can just download the file at the end? Thanks.

[bko]

This is how I did it with IFTTT and Google Drive:

Calling HTTPS URL (google docs) from spark core... is it possible?

Hi @DRCO Here is a screenshot of the IFTTT trigger and action sections. In my case the published event name is “Temperature”. The action is on your Google Drive (not the spreadsheet program in Google) which might have been hard to figure out. [image]

It adds a new row for each measurement and automatically adds a new spreadsheet when it thinks the current one is "full" so I put the data into an "Events" folder.

[prasann16]

This is perfect! Is there anyway to write data from multiple variables into different columns?
This only allows me to write the status of the event.

Thanks.

[chipmonk]

Hi @DRCO,

I have done what you are needing. Use a PC or MAC as my machine which requests a JSON variables from the Particle Cloud. The collection is a python program ( very simple) which sends the REST get requests and parses the JSON. You create a JSON cloud variable in the Photon with your data.

The Python program uses a module ‘XLSXWriter’. Here’s a link http://xlsxwriter.readthedocs.io/
Xlsxwriter is very easy to work with and the documentation is very good. Use this is my day job to collect data from USB to custom 2 wire serial interface. In the XLSXwriter I can format cell, label columns and even create charts.

To read the JSON from the Particle I use two modules “requests” and “json”. What good about Python is that any networking hiccups can be easily handled so you don’t crash out an not collect data.

You can request as many JSON variable which can pack in many different variables in one request. I think you are limited to 64 character max. If you round your data then is quite a easy to handle 4 or 5 floats with two sig digs.
Need more vars just define another JSON variable in the Photon and parse it.

This approach you have the data in text or spreadsheet ( using XLSXwriter) on your machine. I find that Google is not limiting the “free” cycles you get with google spreadsheets.

This approach helped me prove that I needed to invest in more expensive ultra sonic sensors and the results were worth the 10x cost.
Hope this helps.

Chipmonk

[DRCO]

Hi @chipmonk, sorry for the late reply - your approach looks really interesing and I will give it a go when time allows - thanks for sharing.

[MrRichardB]

Hi,

I am still new at all this and not a programmer. With my students I would like to read Photon sensor data (cloud variables) from within Python to write it into a DB. Would you be able to share your Python script for me to have a look how to read the Photon data from Python?
Thanks,
Richard

[Moors7]

The best way to learn is to try. If you're going to be teaching this, it makes sense that you have knowledge of the subject.

@chipmonk

To read the JSON from the Particle I use two modules "requests" and "json".

If you look into this, you should be able to get some basics working.
Give it a shot, and let us know if you get stuck. That way we can help you get to where you need to be without handing you the solution outright.

[MrRichardB]

Thanks for your reply Moors7. I very much understand where you are coming from; however, I need to be efficient in learning if this is a viable option (Photon -> Python -> DB) in my curriculum. Unfortunately, I do not have the time to learn all there is possibly to know for a couple of months before I can make a decision to implement into the curriculum. I find it very efficient to learn by having a working example. I am not in the position to understand all the forum entries related to the topis as they do not use simple explanations but require a certain level of knowledge already. Thanks again for your reply.

[RWB]

Does the database have to reside in Python specifically?

Do you need live data or just a collection of collected data in an excel file you can download at any time for processing later?

[chipmonk]

Hi MrRichardB,

Yes I will be glad to share my python. Here it is:

_#!/usr/bin/python_
_# Time Stamped Output_
_# "last_heard": "2016-04-19T04:03:44.070Z",_
_#_
_import requests, json, time_
_import sys,getopt_
_import sys_
_def main(argv):_
_ sleep_time = 0 _
_ sleepArg = "50"_
_ try:_
_      opts, args = getopt.getopt(argv,"hs:", ["sleepArg="])_
_ except getopt.GetoptError:_
_      print 'cmd_serve.py -s <sampleRateSeconds> '_
_      sys.exit(2)_
_ for opt, arg in opts:_
_      if opt == '-h':_
_         print 'cmd_serv.py -s <SampleRateSeonds>'_
_         sys.exit()_
_      elif opt in ("-s", "--SleepArg"):_
_         sleep_time = float(arg)_
_         print "Sleep argment is " ,  sleep_time _
_ no_response = 0;_
_ #print "this is a Serv.py"_
_ print"Cloud Variable REST call every seconds", sleep_time, "Data Collection started @", time.strftime("%b:%d:%Y:%H:%M")_
_ print"Hrs:Min  Total  Hrs:Min  Raw"_

_ while(1):_
_  try:_
_   resp = requests.get('https://api.particle.io/v1/devices/36002b001747343337363432/HopperJSON?access_token=5fb651a6a7bba1ab3981291fe2462e9f2756b1ec')_
_  except:_
_   print'%s %c Lost Socket Rest Command Failed' % (time.strftime("%H:%M:%S"),'\t' )_
_  if resp.status_code != 200:_
_    # This means something went wrong._
_    no_response = no_response + 1;_
_    print "REST did not return 200", no_response _
_ _
_  rawData =resp.json() _
_  #print json.dumps(rawData['result'], indent=4, sort_keys=True)_
_  try:_
_    CoreString=rawData['coreInfo'] _
_    #print (CoreString)_
_    #print (CoreString['last_heard'])_
_    #parsedTime = json.loads(CoreString)_
_    #print (parsedTime['last_heard'])_
_  except:_
_   print"Failed parsing coreInfo"_
_  try:_
_    DataString =rawData['result']_
_    parsedData = json.loads(DataString)_
_    #print (parsedData['Total'])_
_    #print (parsedData['X'])_
_    no_response = 0;_
_  except:_
_    print"Failed to parse result", no_response _
_    no_response = no_response + 1;_

_   #print'%s %c %s %s' % (time.strftime("%H:%M:%S"),'\t', parsedData['Total'],'\t',parsedData['Raw'] )_
_  #print'%s %s %s %s' % (time.strftime("%H:%M"),'\t', parsedData['Total'],'\t',parsedData['X'] )_
_  print'%s\t %s\t %s \t %s' % (time.strftime("%H:%M"), parsedData['Total'], time.strftime("%H:%M"), parsedData['X'])_
_  time.sleep(sleep_time)_
_    _

_# print('{} {}'.format(item['result.Total'], item['result.Hopper']))_

_if __name__ =='__main__':_
_   main(sys.argv[1:])_

ChipMonk