Unable to write data by bulk update *csv method [ESP8266, Arduino framework]

7 views (last 30 days)
Hello,
I have a problem to write a bulk-update using csv format. I have some experience with thingspeak using JSON update method, but I need to change it because *csv format would be more RAM-saving method in case of bulk update (less characters = less memory needed).
I was trying to change my code written for JSON method to csv according to instructions: https://www.mathworks.com/help/thingspeak/bulkwritecsvdata.html but my example-string csv_feed is not accepted by Thingspeak server. I don't have ideas where is the problem, I tried to comment "//" some non-necessery lines, because in API's example, those lines doesn't appears in code.
Thingspeak response code is always the same: 401, meaning "authorization required". But I wrote in code "write_api_key" properly I think (I'm sure that API key and channel number is the same as it is in channel settings on Thingspeak page). The key is authentic, I don't have important data on this channel and I'll change the key after getting rid problems with that code. Without success, I've been changing csv_feed content with diffrent time format (I'd like to use EPOCH time), diffrent number of commas representing field values and I'm still stuck with that code. Could anybody show my mistake? Thank you in advance, Jakub
char csv_feed[] = "write_api_key%3DxxxxxxxxxxxxxxxxC%26time_format%3Dabsolute%26updates%3D1662883022%2C20%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C";
String csv_lenght = String(strlen(csv_feed)+1);
if(client.connect(server, 80)){
client.println("POST /channels/647137/bulk_update.csv HTTP/1.1");
client.println("Host: api.thingspeak.com");
//client.println("User-Agent: mw.doc.bulk-update (Arduino ESP8266)");
//client.println("Connection: close");
client.println("Content-Type: application/x-www-form-urlencoded");
client.println("time_format: absolute");
//client.println("write_api_key: xxxxxxxxxxxxxxxx");
client.println("Content-Length: "+csv_lenght);
client.println();
client.println(csv_feed);

Answers (2)

Christopher Stapels
Christopher Stapels on 12 Sep 2022
The arduino client should take care of url encoding your data. I would use the raw text in the value of char csv_feed
Perhaps try to use the example from the doc directly: (with your API_Key of course)
char csv_feed="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"
You should leave these two lines in
//client.println("User-Agent: mw.doc.bulk-update (Arduino ESP8266)");
//client.println("Connection: close");
but leave this one out, since you have already incuded the api key in the csv_feed string.
//client.println("write_api_key: xxxxxxxxxxxxxxxx");
For troubleshooting, I would reccomend using POSTMAN to get the syntax correct.
I edited out your api key before I saw your explanation. Its still best not to share these.
  2 Comments
Christopher Stapels
Christopher Stapels on 13 Sep 2022
Its definitely not the port.
I might have some old code for an ESP8266, I do remember having to be careful with the url encoding, i forget the details. You might have to encode the commas but not the equals signs.

Sign in to comment.


Christopher Stapels
Christopher Stapels on 13 Sep 2022
I havent tested this recently, but I found it in an old folder and I expect that it works. I had to escape some values. Please let me know if it works for you.
//Prepare the data
for (int loop_value=0;loop_value<DATA_POINTS;loop_value++){
DataBuffer[loop_value]=random(300);
}
PostData = "{\"write_api_key\":\"";
PostData+=String(WriteAPIKey);
PostData+="time_format=relative";
PostData+="\",\"updates\":[";
for (int index=0;index<DATA_POINTS;index++)
{
PostData+="{\"delta_t\":\"" + String(index*DATA_POINTS+value);
if (index<(DATA_POINTS-1)) //add comma except last time
{
PostData+=",";
}
}
PostData+="]}";
Serial.println("POST "+ String(link_address) +" HTTP/1.1");
Serial.println(PostData);
if (client.connected()){
Serial.println("Connected");
//build HTTP request
client.println("POST "+ String(link_address) +" HTTP/1.1");
client.println("Host: " + String(host));
client.println("Connection: close");
client.println( Content-Type: application/x-www-form-urlencoded);
client.println("Content-Length: " + String(PostData.length()));
client.println();
client.println(PostData);
}
else{
Serial.println("Failed to post");
}
  1 Comment
Jakub R
Jakub R on 18 Sep 2022
Thanks for support but unfortunely your code is json-format customized (for() loop is creating json string).
I tried even to send json table, but it's seems to not be supported:
//not working json table char json_feed[] ="{\"write_api_key\":XXxxXXxxXXxxXX\",\"updates\":[{\"created_at\":[1663441309,1663442309,1663443309],\"field1\":[1.0,2.0,4.0]}]}";
Yesterday evening I wanted to reply that any attemps were unsucessful, but today morning I get find solution :).
The real problem with sending csv was at this line in my code:
String csv_lenght = String(strlen(csv_feed)); //works
Previously I had String(strlen(csv_feed +1)) which was source of all problems, causing server response code 400.
I paste my working example for anyone who wants send data using csv format.
char csv_feed[] ="write_api_key=XXxxXXxxXXxxXX&time_format=absolute&updates=1663458516%2C3%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C|1663468516%2C4%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C|1663478516%2C7%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C|1663488516%2C9%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C";
String csv_lenght = String(strlen(csv_feed));
if(client.connect(server, 80)){
client.println("POST /channels/YOUR-CHANNEL-ID/bulk_update.csv HTTP/1.1"); // Replace YOUR-CHANNEL-ID with your ThingSpeak channel ID
client.println("Host: api.thingspeak.com");
client.println("User-Agent: mw.doc.bulk-update (Arduino ESP8266)");
client.println("Connection: close");
client.println("Content-Type: application/x-www-form-urlencoded");
client.println("Content-Length: "+csv_lenght);
client.println();
client.println(csv_feed);
}
else{
}// Serial.println("Failure: Failed to connect to ThingSpeak");
delay(250); //Wait to receive the response
client.parseFloat();
String resp = String(client.parseInt());
Serial.println("Response code:"+resp); // Print the response code. 202 indicates that the server has accepted the response
Serial.println(csv_feed);
Serial.println(data);
As you has said, = & signs in char[] table should not to be encoded.
Thanks again for support, greetings from Poland.
Jakub

Sign in to comment.

Communities

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!