# Parsing JSON to output to CSV file

**URL:** <https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198>\
**Category:** General\
**Created:** [22 October 2018 20:28 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198 "2018-10-22T20:28:56Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![ddubief](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@ddubief](https://discourse.nodered.org/u/ddubief)\
**Post date:** [22 October 2018 20:28 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/1 "2018-10-22T20:28:56Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/b/bad3a405c3331f324f048be8a16375000d5a4819.jpeg)

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [22 October 2018 20:46 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/2 "2018-10-22T20:46:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ddubief](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@ddubief](https://discourse.nodered.org/u/ddubief)\
**Post date:** [22 October 2018 21:03 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/3 "2018-10-22T21:03:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ddubief](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@ddubief](https://discourse.nodered.org/u/ddubief)\
**Post date:** [22 October 2018 21:22 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/4 "2018-10-22T21:22:14Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/f/f6d8b7515c37741edfdcb179c68815ab9929dedb.png)

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [22 October 2018 21:33 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/5 "2018-10-22T21:33:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ddubief](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@ddubief](https://discourse.nodered.org/u/ddubief)\
**Post date:** [22 October 2018 22:14 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/6 "2018-10-22T22:14:34Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e46a1427a3568b4ac0a49c0994bcbc035dd6a5a4.png)

That did the trick. Schweet! Thank you both.

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

---

<div class="post-metadata">

**Author:** ![ddubief](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@ddubief](https://discourse.nodered.org/u/ddubief)\
**Post date:** [22 October 2018 22:19 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/7 "2018-10-22T22:19:43Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/34a297ca658c746b4027d5922eeaa1c6967728be.png)

This is the new flow.

---

<div class="post-metadata">

**Author:** ![nodecentral](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodecentral/32/10432_2.png) [@nodecentral](https://discourse.nodered.org/u/nodecentral)\
**Post date:** [23 October 2018 10:18 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/8 "2018-10-23T10:18:59Z")

</div>

Hi @ddubief

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

---

<div class="post-metadata">

**Author:** ![ddubief](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@ddubief](https://discourse.nodered.org/u/ddubief)\
**Post date:** [23 October 2018 19:27 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/9 "2018-10-23T19:27:47Z")

</div>

[{"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](http://172.86.160.3/api%22,%22tls%22:%22%22,%22x%22:310,%22y%22:100,%22wires%22:%5B%5B%22320a1c.2958c5e4%22%5D%5D%7D,%7B%22id%22:%22320a1c.2958c5e4%22,%22type%22:%22function%22,%22z%22:%22b495386b.f971e8%22,%22name%22:%22Format) 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"]]}]

---

<div class="post-metadata">

**Author:** ![ddubief](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@ddubief](https://discourse.nodered.org/u/ddubief)\
**Post date:** [23 October 2018 19:29 UTC](https://discourse.nodered.org/t/parsing-json-to-output-to-csv-file/4198/10 "2018-10-23T19:29:13Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e867970da6d81c986c74f8a94957a56839cdb760.jpeg)