MATLAB Answers

Bulk CSV upload to ThingSpeak with Python

37 views (last 30 days)
Jenna R
Jenna R on 1 Apr 2020
Edited: Jenna R on 20 Apr 2020
Hello all,
For context to my issue, I have a Raspberry Pi successfully uploading pH, turbidity, and temperature sensor data to ThingSpeak using a basic program. I was asked to implement a way for the results to be uploaded even if there was no internet connectivity, so in Python, the results should save to a CSV file which will later be automatically uploaded when the program runs and has internet connectivity again. So, the logic is:
  • If the Pi has internet, upload the sensor readings to ThingSpeak using the POST method. That's all.
  • If the Pi does not have internet, save the readings to a CSV file.
  • When the program is ran again, if the internet is still disconnected, it will save the readings to the CSV again by amending the file (not overwriting it).
  • When the program is ran again, if the internet is connected, it will automatically upload the CSV file to ThingSpeak using the POST bulk-write CSV method.
I have been searching the web for example code on how to get the bulk CSV upload for ThingSpeak working in Python. Most of the people asking for help are using the JSON method or Arduino code, but the ones who are using POST and Python (like I will be) have fixed their own issue without really going into detail on how they fixed it. I have checked the API thoroughly, but it is still confusing to me, even after checking out the examples that were in the MATLAB (such as this one: )
Let me say that I am still quite new to Python/ThingSpeak. I've done my best to try to understand how the single write process works and apply it to the bulk write process, but I'm just drawing a blank. I guess my main question is that the URL in the API doesn't make sense to me:
I understand I'm supposed to put my channel ID into the URL, but how does my CSV file data apply to the "bulk_update.csv" part of the URL? Am I supposed to read in the results of the CSV and insert them into the URL? Am I thinking about this the wrong way? For reference, here's the Python code I use to upload single entries (I cut out the parts of code that don't apply). Any help is greatly appreciated.
import http.client
import urllib
import time
import serial
#Assign ThingSpeak read API key to a variable
apiKey = 'XXXXXXXXX'
def readSensors(): #Read sensor data
(Code omitted, but it basically just grabs sensor data from the Arduino)
return pH, turbidity, temperature
def uploadReadings(): #ThingSpeak upload
pH, turbidity, temperature = readSensors()
params = urllib.parse.urlencode({'field1': pH, 'field2': turbidity, 'field3': temperature, 'key':apiKey})
headers = {"Content-type": "application/x-www-form-urlencoded","Accept": "text/plain"}
conn = http.client.HTTPConnection("")
print("Attempting to contact ThingSpeak")
conn.request("POST", "/update", params, headers)
response = conn.getresponse()
print ("Upload status: ", response.status, response.reason)
print("Connection failed")
while True:

Accepted Answer

Christopher Stapels
Christopher Stapels on 6 Apr 2020
When I write
I get
"feeds": [{"created_at": "2020-04-03T06:26:23-04:00","entry_id": 1,"field1": "1.2","field2": "2.3","field3": "3",....etc
I read the top write statement to say the first event happened at 10:26 in gmt+1. In Eastern standatrd Time (US) thats 6:26 am.
When I change my time zone to utc+1, for the channel data I get:
"created_at": "2020-04-03T12:26:23+02:00","entry_id": 1,"field1": "1.2",...etc
Which is 10:26 in UTC.
Can you provide the exact syntax of your body for the POST? (except the api key). Then I can try it, becasue +0100 seems to work for me.
Christopher Stapels
Christopher Stapels on 8 Apr 2020
This is the final post in the thread! I was just reading through things sequentially and it was really hard to follow. Im just putting this here in case anyone is reading for the solution. Look at the dates on the posts.
@Jenna R- Im totally happy to help, and I appreciate you pointing out how complex these things get (and helping me work through my test strategy) . It will help us at design time when we get a chance to improve the feature.

Sign in to comment.

More Answers (2)

Christopher Stapels
Christopher Stapels on 1 Apr 2020
Edited: Christopher Stapels on 1 Apr 2020
The documentation page explains where to put the channel ID. The Hackster article you mentioned on the forum is the correct one "Solar Powered Cellular Weather Station".
Your HTTP request is misformatting the body. The data goes in the section where you are writing the params.
conn.request("POST", "/update", params, headers)
The format of the csv data is specified in the documentation page for bulk CSV.
Here is an example
What are you seeing when you run this code?
  1 Comment
Jenna R
Jenna R on 1 Apr 2020
Hi Christopher, thanks for responding so quickly!
The code I shared was just to show how I'm doing the single data uploads for ThingSpeak with Python, I have not yet modified it to do the CSV uploads because I'm confused with how the API is telling me to write it, since it seems to be much different than how I'm doing it now (and maybe it's not... beginner here, still trying to get my head around it).
The example you suggested is what I've been seeing to use, but I don't know where I'm supposed to put that information... I understand it goes along with the URL, so does it just go into params? For example:
params = "write_api_key=XXXXXXXXXXXXXXXX&time_format=absolute&updates=2018-06-14T12:12:22-0500,1,,3,0.4,1.5,1.6,,1.8,40.0,5.4,0,wet|2018-01-30T10:26:23-0500,1.2,2.3,3,4,5,6,7,8,42.0,0.5,50,falling"
Once I figure out how to format it, I'd be glad to try it out and let you know what happens when I run the code.
Also, this may sound silly, but I'm still not grasping the concept of how the CSV file gets imported into ThingSpeak, even after reading through the API. Everything is just showing how you can import bulk entries into ThingSpeak, but doesn't Python first have to read the CSV file values in, then upload those values into ThingSpeak? I hope this question makes sense.
Thanks again for your help!

Sign in to comment.

Christopher Stapels
Christopher Stapels on 2 Apr 2020
You put the values from the csv file into the params.
so if your csv file says
date, field1, field 2,
2020-01-01 12:00:00,10, 20,
2020-01-01 13:00:00,11,12
then you make params="write_api_key=XXXXXXXXXXXXXXXX&time_format=absolute&updates=2020-01-01 12:00:00,10,20,,,,,,,,,,|2020-01-01 13:00:00,11,12.,,,,,,,,,,"
not 100% sure you need the trailing commas if there is nothing in those fields- check the doc page to be sure.
  1 Comment
Jenna R
Jenna R on 5 Apr 2020
Hi there Christopher,
Thanks for your help and sorry for a delayed response! I have actually been working on this the past few days, I was having trouble, but finally got my head around it and it is working. I'm still working on the code so that I can post it here and help anyone else who might need some assistance. However, I am stuck on something.
When my CSV data gets uploaded into ThingSpeak, it's showing the wrong timezone on the data points when I look at the charts. The data charts say that they are GMT+1:00, which is my timezone (Dublin and we are on +1 now because of the summer time change, otherwise we are equal to GMT), but they are an hour ahead of what they should be. For example, if my data was recorded at 4:15:30, it will show as 5:15:30. Here's what I've done to try to fix the issue:
Made sure my profile is set to the correct timezone.
When writing to the CSV, I had my datetime going in as this format: 2020-01-01 12:00:00
So then I changed it to 2020-01-01 12:00:00+01:00 to see if that helped. It did not make any difference, so I thought it must be wrong, but I tried 2020-01-01 12:00:00-01:00 and that made the datapoint show up 2 hours ahead instead of 1 hour ahead. So the syntax is right, and I can do all the UTC offsets I want if I do a minus amount (-01:00, -02:00, and so on), but if I try to do one with a plus (+01:00, +02:00), it does not work. I've even tried "UTC+01:00" and "GMT+01:00" to no avail. I checked all of ThingSpeak's documentation on timezone formatting, but it looks like it should work... and it's not.
I have also taken a CSV file and uploaded it directly through the web browser with the 2020-01-01 12:00:00+01:00 format and it WORKS! So is it a bug with the bulk-write CSV API? Again, I'm able to do 2020-01-01 12:00:00-01:00 through the bulk-write CSV API, but I can't do 2020-01-01 12:00:00+01:00. It doesn't error out or anything, it just ignores it and acts like it's still 2020-01-01 12:00:00 without an offset.
Any advice on how to get it to recognize that I need a +01:00 offset on the timestamp?

Sign in to comment.


More Answers in the  ThingSpeak Community

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!