Convert MQTT-JSON to CSV

Hey Guys!

i'm having trouble converting MQTT Messages to CSV format.

my mqtt-in node delivers a parsed json object with this format:
{
"d":{
"ItemValue 1":100,
"ItemValue 2":200,
"ItemValue 3":300,
"ItemValue 4":400
},"ts":"2020-11-09T13:01:31.624030"
}

i need to somehow convert this to a CSV-File with the following Format/Header:
Timestamp;ItemValueNumber;ItemValue
09.11.2020 13:01:31;ItemValue 1;100
09.11.2020 13:01:31;ItemValue 2;200
09.11.2020 13:01:31;ItemValue 3;300
09.11.2020 13:01:31;ItemValue 4;400

i'm thankful for your tips/help :slight_smile:

You would need to iterate through values create an array and the use csv node.
e.g.

[{"id":"93a283f7.87a68","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{ \"d\":{ \"ItemValue 1\":100, \"ItemValue 2\":200, \"ItemValue 3\":300, \"ItemValue 4\":400 },\"ts\":\"2020-11-09T13:01:31.624030\" }","payloadType":"json","x":130,"y":1620,"wires":[["31d285a6.f18fca"]]},{"id":"31d285a6.f18fca","type":"function","z":"8d22ae29.7df6d","name":"","func":"let key = Object.keys(msg.payload.d),\nvalue = Object.values(msg.payload.d),\ntimestamp = msg.payload.ts;\nmsg.payload = [];\n\nfor(let a=0; a <= value.length; a++){\n  msg.payload[a] = [];\n  msg.payload[a][0] = timestamp; \n  msg.payload[a][1] = key[a];\n  msg.payload[a][2] = value[a];\n}\nmsg.payload.unshift([\"Timestamp\",\"ItemValueNumber\",\"ItemValue\"]);\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":290,"y":1620,"wires":[["ef5dfb10.e5a408"]]},{"id":"ef5dfb10.e5a408","type":"csv","z":"8d22ae29.7df6d","name":"","sep":";","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":430,"y":1620,"wires":[["9d874d5b.b4324"]]},{"id":"9d874d5b.b4324","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"msg","x":630,"y":1620,"wires":[]}]

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.