Parsing JSON to output to CSV file

General
1

I will apologize first, and take any critique!

I am trying to pull API data from a mobile router, that gives me plenty of JSON formatted output on what I need. Of this data, I am parsing to capture only specific info pertaining to the devices SystemID, gps data, and 6 cell RF values. Output and saved to a csv file on my machine.

Below is node flow, and that works but nothing gets written to file. I've confirmed permissions in the directory and on the file as well.

image

systemid = msg.payload.data.config.system.system_id + ","
lat = msg.payload.data.status.gps.lastpos.latitude + ","
long = msg.payload.data.status.gps.lastpos.longitude + ","
carr1 = msg.payload.data.status.wan.devices["mdm-6073fa91"].diagnostics.CARRID + ","
rssi1 = msg.payload.data.status.wan.devices["mdm-6073fa91"].diagnostics.DBM + ","
carr2 = msg.payload.data.status.wan.devices["mdm-7bd7f9ac"].diagnostics.CARRID + ","
rssi2 = msg.payload.data.status.wan.devices["mdm-7bd7f9ac"].diagnostics.DBM + ","
carr3 = msg.payload.data.status.wan.devices["mdm-755dc037"].diagnostics.CARRID + ","
rssi3 = msg.payload.data.status.wan.devices["mdm-755dc037"].diagnostics.DBM + ","
return { systemid,lat,long,carr1,rssi1,carr2,rssi2,carr3,rssi3 };

Debug output of the above:
10/22/2018, 2:20:46 PM node: 867c1577.f24f7
msg : Object
object
systemid: "IBR1700-190,"
lat: "43.580831333333336,"
long: "-116.6106175,"
carr1: "Verizon,"
rssi1: "-73,"
carr2: "AT&T,"
rssi2: "-59,"
carr3: "Sprint,"
rssi3: "-67,"
_msgid: "cd5f9480.610958"

Any pointers I'm all ears, this is driving me nuts.

2

I guess you need to return an object containing a payload object which contains the data to be written. As you can see from the debug there is no payload object.
Also it doesn't look as if you are sending anything at all to the second output, where you have connected the file out node.

1 Like
3

So I updated the function node, and yes you were correct, I didn't realize you had to explicitly define data to be sent to the 2nd output. Changed back to 1:

systemid = msg.payload.data.config.system.system_id
lat = msg.payload.data.status.gps.lastpos.latitude
long = msg.payload.data.status.gps.lastpos.longitude
carr1 = msg.payload.data.status.wan.devices["mdm-6073fa91"].diagnostics.CARRID
rssi1 = msg.payload.data.status.wan.devices["mdm-6073fa91"].diagnostics.DBM
carr2 = msg.payload.data.status.wan.devices["mdm-7bd7f9ac"].diagnostics.CARRID
rssi2 = msg.payload.data.status.wan.devices["mdm-7bd7f9ac"].diagnostics.DBM
carr3 = msg.payload.data.status.wan.devices["mdm-755dc037"].diagnostics.CARRID
rssi3 = msg.payload.data.status.wan.devices["mdm-755dc037"].diagnostics.DBM
msg.payload = [systemid,lat,long,carr1,rssi1,carr2,rssi2,carr3,rssi3];
return msg;

Now I get this in the return debug:

payload: array[9]
0: "IBR1700-190"
1: 43.58084
2: -116.61059516666667
3: "Verizon"
4: "-65"
5: "AT&T"
6: "-59"
7: "Sprint"
8: "-67"

Feel like I'm a step closer.

Now I just need that to write out into a csv file where the above is saved as: 1,2,3,4,5,6,7,8 in a row.

1 Like
4

K, that helped ALOT. Last piece I need to figure out, is to correct the beg/end of the parsed data to not include the bracket+quote:

image

5

It looks like you're writing the Object to the file - so each column has the key:value, rather than just the values.

Have a look at the CSV node - it can take the object and generate the proper csv string of the values which you can write to the file.

Nick

6

image

That did the trick. Schweet! Thank you both.

Now I have one step of data, I have to now add some layers and colors.

7

image

This is the new flow.

8

Hi @ddubief

Looks good would you be able to export and share the flow ?

9

[{"id":"1ec6902d.5e6db","type":"inject","z":"b495386b.f971e8","name":"15 sec Loop","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":100,"wires":[["ba9d2224.8ffe"]]},{"id":"ba9d2224.8ffe","type":"http request","z":"b495386b.f971e8","name":"GET /status","method":"GET","ret":"obj","url":"http://172.86.160.3/api","tls":"","x":310,"y":100,"wires":[["320a1c.2958c5e4"]]},{"id":"320a1c.2958c5e4","type":"function","z":"b495386b.f971e8","name":"Format Data","func":"systemid = msg.payload.data.config.system.system_id\nlat = msg.payload.data.status.gps.lastpos.latitude\nlong = msg.payload.data.status.gps.lastpos.longitude\ncarr1 = msg.payload.data.status.wan.devices["mdm-6073fa91"].diagnostics.CARRID\nrssi1 = msg.payload.data.status.wan.devices["mdm-6073fa91"].diagnostics.DBM\ncarr2 = msg.payload.data.status.wan.devices["mdm-7bd7f9ac"].diagnostics.CARRID\nrssi2 = msg.payload.data.status.wan.devices["mdm-7bd7f9ac"].diagnostics.DBM\ncarr3 = msg.payload.data.status.wan.devices["mdm-755dc037"].diagnostics.CARRID\nrssi3 = msg.payload.data.status.wan.devices["mdm-755dc037"].diagnostics.DBM\nmsg.payload = ([ systemid,lat,long,carr1,rssi1,carr2,rssi2,carr3,rssi3 ]);\nreturn msg;","outputs":1,"noerr":0,"x":490,"y":100,"wires":[["fc60419a.34169","75d32a08.e87994"]]},{"id":"fc60419a.34169","type":"debug","z":"b495386b.f971e8","name":"Lookey ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":660,"y":80,"wires":[]},{"id":"856081b9.6dc2c","type":"file","z":"b495386b.f971e8","name":"Write to Log.csv","filename":"C:\RFTest\log.csv","appendNewline":true,"createDir":true,"overwriteFile":"false","x":840,"y":260,"wires":[[]]},{"id":"75d32a08.e87994","type":"csv","z":"b495386b.f971e8","name":"FormatFix","sep":",","hdrin":"","hdrout":"","multi":"one","ret":"\n","temp":"","skip":"0","x":670,"y":180,"wires":[["856081b9.6dc2c"]]}]

10

Outputting the data into CSV, made me able to export this into Kepler.gl and build a simple heatmap.

For example, just my morning commute this morning.

image

3 Likes