good afternoon all, I need a little help with formatting the data in the attached pic to a readble EXCEL format. thanks
You should be able to pass it through a csv node to convert to a comma seperated string, if you save that as a .csv file excell should be able to open it.
Hi E1cid,
I took your advice and went down the CSV path and I still can't seem to get the data formated how I would like it.
This is what goes into the File node:
28-6-2021 13:24:06node: e0b5b982.8cd6c8msg : Object
object
_msgid: "29c89576.097ada"
year: 2021
query: "SELECT last("value")-first("value") FROM "Gas_Total" WHERE time >= '2021-03-01 00:00:00' and time < '2021-03-30 00:00:00'"
gas: array[12]
payload: array[12]
[0 … 9]
0: object
april: 137435
1: object
feb: 144229
2: object
may: 107494
3: object
june: 104681
4: object
mart: -94272
5: object
feb: 144229
6: object
june: 104686
7: object
may: 107494
8: object
april: 137435
9: object
[10 … 11]
10: object
may: 107494
11: object
mart: -94272
and this is what gets generated in the CSV file:
I want the data something like this:
june : 104686
april : 137435
[{"june":104686},{"april":137435},{"feb":144229},{"mart":-94272},{"june":104690},{"may":107494},{"april":137435},{"mart":-94272},{"feb":144229},{"april":137435},{"may":107494},{"june":104690}]
any advice? Thanks.
Now you have supplied some data, it looks like you need to transform the data first so the months are values not properties.
here is an example
[{"id":"75ab1d00.e97ed4","type":"inject","z":"a80913f6.edcdb8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"june\":104686},{\"april\":137435},{\"feb\":144229},{\"mart\":-94272},{\"june\":104690},{\"may\":107494},{\"april\":137435},{\"mart\":-94272},{\"feb\":144229},{\"april\":137435},{\"may\":107494},{\"june\":104690}]","payloadType":"json","x":210,"y":380,"wires":[["77239356.ece26c"]]},{"id":"77239356.ece26c","type":"change","z":"a80913f6.edcdb8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.{\"col1\":$keys($),\"col2\":$.*}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":360,"wires":[["2aa3d05.7a8a63"]]},{"id":"2aa3d05.7a8a63","type":"csv","z":"a80913f6.edcdb8","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":570,"y":360,"wires":[["734d800c.adeb58"]]},{"id":"734d800c.adeb58","type":"debug","z":"a80913f6.edcdb8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":420,"wires":[]}]
Thanks!! that worked, great stuff.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
