Export data to CSV

General
1

Hello together,

I have a little programming experience but in nodered I am a complete newbie :slight_smile:

I'm trying to export data to a CSV which works fine but I have problems with the formatting.

When I open my CSV in EXCEL it looks like this:

var now = new Date();
msg.payload = {
    "timestamp" : now.getTime(),
    "Temp_ThE_11" : msg.payload.Params.Tags[0].Value,
    "Temp_ThE_12" : msg.payload.Params.Tags[1].Value,
    "Temp_ThE_13" : msg.payload.Params.Tags[2].Value,
    "Temp_ThE_14" : msg.payload.Params.Tags[3].Value,
}
return msg;

11node
11node756×229 4.9 KB

But the final file should look like this:

node

can someone give me a hint how to format the data for this.

Thanks for your help
Rob

2 
const now = new Date().getTime();
msg.payload = [{
    Timestamp : now,
    Tag: "Temp_ThE_11",
    Value: msg.payload.Params.Tags[0].Value
  }, {
    Timestamp : now,
    Tag: "Temp_ThE_12",
    Value: msg.payload.Params.Tags[1].Value,
  }, {
    Timestamp : now,
    Tag: "Temp_ThE_13",
    Value: msg.payload.Params.Tags[2].Value,
  }, {
    Timestamp : now,
    Tag: "Temp_ThE_14",
    Value: msg.payload.Params.Tags[3].Value,
  }]
}
return msg;
3

Good Morning,

that was the solution thank you :slight_smile:

another question i have more than 50 variables of the same type do i have to enter them all by hand according to your suggestion or can i solve this in a more simple way...?

THX, Robert

4

There is likely a smarter solution but you will have to show me the contents of the data as it is before and as the function node.

Use the copy value button (appears when you hover over the payload in the debug side bar) then paste a small sample of the data in a reply.

5

i hope this sample is ok ....

{"ClientCookie":"NodeRedCookieForReadTags","Message":"NotifyReadTag","Params":{"Tags":[{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_58","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_57","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_56","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_55","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_54","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_53","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_52","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_51","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_48","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_47","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_46","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_45","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_44","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_43","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_42","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_41","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_38","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_37","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_22","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_21","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_18","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_17","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},{"ErrorCode":0,"ErrorDescription":"","Name":"UFO_gDB_Istwert_Temp_ThE_Schmelze_16","Quality":"Good","QualityCode":192,"TimeStamp":"2022-10-18 10:18:57.7070280","Value":"0.000000"},
6

Here is a demo showing 2 solutions. I prefer "to object" as you get easy access to any item without searching the array - but it all depends on what you are doing with the final output.

Demo...

image

Demo results...

image
image205×895 17 KB

Demo Flow...

Use CTRL-I to import

[{"id":"438935cfa49da585","type":"template","z":"ccdc4f1f78201a08","name":"sample data","field":"payload","fieldType":"msg","format":"json","syntax":"mustache","template":"{\n    \"ClientCookie\": \"NodeRedCookieForReadTags\",\n    \"Message\": \"NotifyReadTag\",\n    \"Params\": {\n        \"Tags\": [\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_58\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_57\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_56\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_55\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_54\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_53\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_52\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_51\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_48\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_47\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_46\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_45\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_44\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_43\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_42\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_41\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_38\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_37\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_22\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_21\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_18\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_17\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            },\n            {\n                \"ErrorCode\": 0,\n                \"ErrorDescription\": \"\",\n                \"Name\": \"UFO_gDB_Istwert_Temp_ThE_Schmelze_16\",\n                \"Quality\": \"Good\",\n                \"QualityCode\": 192,\n                \"TimeStamp\": \"2022-10-18 10:18:57.7070280\",\n                \"Value\": \"0.000000\"\n            }\n        ]\n    }\n}","output":"json","x":420,"y":120,"wires":[["e46918b0c4f8d49a","bc5da4e42a0e4d16"]]},{"id":"e46918b0c4f8d49a","type":"function","z":"ccdc4f1f78201a08","name":"to lookup","func":"const now = new Date().getTime();\nconst tags = msg.payload.Params.Tags\nconst results = {}\nfor (let index = 0; index < tags.length; index++) {\n    const tag = tags[index];\n    const name = tag.Name.replace('UFO_gDB_Istwert_', '').replace('_Schmelze', '')\n    const value = parseFloat(tag.Value)\n    results[name] = value\n}\n\nmsg.payload = results \n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":120,"wires":[["f260e4069561db31"]]},{"id":"4395b2e6be9c6e7b","type":"inject","z":"ccdc4f1f78201a08","name":"click me","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":260,"y":120,"wires":[["438935cfa49da585"]]},{"id":"f260e4069561db31","type":"debug","z":"ccdc4f1f78201a08","name":"debug 115","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":770,"y":120,"wires":[]},{"id":"bc5da4e42a0e4d16","type":"function","z":"ccdc4f1f78201a08","name":"to array","func":"const now = new Date().getTime();\nconst tags = msg.payload.Params.Tags\nconst results = []\nfor (let index = 0; index < tags.length; index++) {\n    const tag = tags[index];\n    const name = tag.Name.replace('UFO_gDB_Istwert_', '').replace('_Schmelze', '')\n    const value = parseFloat(tag.Value)\n    const entry = {\n        Timestamp: now,\n        Tag: name,\n        Value: value\n    }\n    results.push(entry)\n}\n\nmsg.payload = results \n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":160,"wires":[["aaf6d5e4f264de27"]]},{"id":"aaf6d5e4f264de27","type":"debug","z":"ccdc4f1f78201a08","name":"debug 116","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":770,"y":160,"wires":[]}]
7

Thanks again, your tips were very helpful :grinning:

Do you have any recommendations like books, tutorials that can help a newbie to get started with NodeRed?

Thx, Robert

8

You should read the documentation on this website and we recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.