How to save a matrix in CSV format, using CSV Node ?

[Marcio Braga]

After getting data from a public API, I need to save it in CSV format, but I'm not being able to make it to work.

In the node just before CSV Node, I set payload with v1[], a matrix with 12 elements. These 12 elements are the ones I need in the CSV file. 

I think the key point I may be misunderstanding in the CSV Node is the COLUMNS field, but I do not know what to write there in my case.

Welcome some guidance.

Thank you

Marcio

[{"id":"90ab5e58.6f54a","type":"subflow","name":"Subflow 2","in":[{"x":112,"y":131,"wires":[{"id":"c1562a00.3ea9d8"}]}],"out":[{"x":111,"y":199,"wires":[]}]},{"id":"87600cca.789ff","type":"http request","name":"API request","method":"GET","ret":"txt","url":"https://sabesp-api.herokuapp.com/{{datastring}}","x":370,"y":186,"z":"90ab5e58.6f54a","wires":[["84abee58.7b541"]]},{"id":"84abee58.7b541","type":"function","name":"JSON parse","func":"msg.payload = JSON.parse(msg.payload);\nreturn msg;","outputs":1,"noerr":0,"x":418,"y":237,"z":"90ab5e58.6f54a","wires":[["69aa8075.96558"]]},{"id":"915be2cb.6ea42","type":"change","name":"Header off","rules":[{"t":"set","p":"headers","to":"null"}],"action":"","property":"","from":"","to":"","reg":false,"x":681,"y":479,"z":"90ab5e58.6f54a","wires":[["d7957e1a.286a8"]]},{"id":"5e0e2f7b.a1f1d","type":"function","name":"Parse vol e chuva","func":"v1 =[]; // v1 matrix\nfor (i=0;i<6;i++) {\n v1[2*i] = msg.payload[i].data[0].value;\n v1[2*i+1] = msg.payload[i].data[1].value;\n}\nmsg.payload = v1;\nconsole.log(v1);\nreturn msg;","outputs":1,"noerr":0,"x":558,"y":349,"z":"90ab5e58.6f54a","wires":[["4fed2a24.b012d4","d46fca0e.2b9038"]]},{"id":"c1562a00.3ea9d8","type":"function","name":"Monta string","func":"//console.log(msg);\na1 = (\"0\"+(msg.data1.getMonth()+1)).slice(-2);\na2 = (\"0\"+msg.data1.getDate()).slice(-2);\nmsg.datastring=msg.data1.getFullYear()+\"-\"+a1+\"-\"+a2;\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":131,"z":"90ab5e58.6f54a","wires":[["87600cca.789ff"]]},{"id":"d7957e1a.286a8","type":"function","name":"Next or End","func":"if ( (msg.i += 1) < msg.max ) return msg;","outputs":1,"noerr":0,"x":454,"y":558,"z":"90ab5e58.6f54a","wires":[["9536705b.6ac99"]]},{"id":"9536705b.6ac99","type":"function","name":"Date + 1","func":"msg.data1.setDate(msg.data1.getDate()+1);\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":73,"z":"90ab5e58.6f54a","wires":[["c1562a00.3ea9d8"]]},{"id":"69aa8075.96558","type":"function","name":"Reservatorios","func":"// Colocando o nome dos reservatorio uma vez\nif( msg.i === 0 ) {\n v1 =[]; // v1 matrix\n for (i=0;i<6;i++) { v1[i] = msg.payload[i].name; }\n console.log(v1);\n}\nreturn msg;","outputs":1,"noerr":0,"x":484,"y":292,"z":"90ab5e58.6f54a","wires":[["5e0e2f7b.a1f1d"]]},{"id":"4fed2a24.b012d4","type":"csv","name":"","sep":",","hdrin":"","hdrout":true,"multi":"one","ret":"\\r\\n","temp":"what","x":666,"y":408,"z":"90ab5e58.6f54a","wires":[["915be2cb.6ea42","290fd6e1.d6f02a"]]},{"id":"290fd6e1.d6f02a","type":"file","name":"","filename":"c:\\mama99.txt","appendNewline":true,"createDir":false,"overwriteFile":"false","x":871,"y":371,"z":"90ab5e58.6f54a","wires":[]},{"id":"d46fca0e.2b9038","type":"debug","name":"","active":true,"console":"false","complete":"payload","x":801,"y":306,"z":"90ab5e58.6f54a","wires":[]},{"id":"2560dd5b.da9f22","type":"inject","name":"Kick-off","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":103,"y":72,"z":"ac76642a.538998","wires":[["566038a3.a99fc8"]]},{"id":"566038a3.a99fc8","type":"function","name":"Dados","func":"msg.max=5;\nmsg.i=0;\nmsg.datastring=\"02/01/2015\";\nmsg.data1 = new Date(msg.datastring);\nreturn msg;","outputs":1,"noerr":0,"x":156,"y":134,"z":"ac76642a.538998","wires":[["eaed78ba.151288"]]},{"id":"eaed78ba.151288","type":"subflow:90ab5e58.6f54a","name":"Loop","x":219.5,"y":193.5,"z":"ac76642a.538998","wires":[[]]}]

[Dave C-J]

Hi Marcio

here is a simple example -  In your case - trying to output a CSV file - you need to send it an object - not an array... the column names are then used to specify which parts of the object to extract and the order in which to place them into the CSV.

(If you are happy just building an array you could of course just build a string instead and not use the CSV node at all :-)

[{"id":"70700dc4.8f8ff4","type":"inject","z":"d07b18c9.2f84e8","name":"","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":133,"y":1241,"wires":[["d2a88985.2d5778"]]},{"id":"d2a88985.2d5778","type":"function","z":"d07b18c9.2f84e8","name":"","func":"msg.payload = {};\nmsg.payload.foo = \"B\";\nmsg.payload.last = \"last\";\nmsg.payload.bar = \"A\";\nmsg.payload.third = \"3\";\nreturn msg;","outputs":1,"noerr":0,"x":271,"y":1238,"wires":[["133b0c97.ecc4f3"]]},{"id":"133b0c97.ecc4f3","type":"csv","z":"d07b18c9.2f84e8","name":"","sep":",","hdrin":"","hdrout":"","multi":"one","ret":"\\n","temp":"bar,foo,third,last","x":418,"y":1238,"wires":[["844921b3.7bb6e"]]},{"id":"844921b3.7bb6e","type":"debug","z":"d07b18c9.2f84e8","name":"","active":true,"console":"false","complete":"payload","x":579,"y":1236,"wires":[]}]

[Marcio Braga]

Thank you Dave,

As confirmed that CSV Node works only with object properties, your suggestion to build the full CSV string became a good idea, thank you :-)

Marcio

[Nicholas O'Leary]

I do think the CSV node ought to cope with an array of arrays payload and do the right thing - if your data already happens to be in that format it would be frustrating (and inefficient) to convert each row into an object of key/value pairs.

N

--
http://nodered.org
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Dave C-J]

Indeed - will look to do that ... of course it will have to assume that all the array rows are consistent and without missing items... and consistent order.  Or do we only take as many as are "labelled" in the column names... or ...  needs a bit of thought first

Suggestions welcome.

[Marcio Braga]

Dave,

The example below is an extension of your example here.   

The Function-node builds a smal M1 matrix of 4 elements, but it could be dozens.

Payload receives M2, as simple M1 reference.

The Column field of CSV-Node receives the single word M2.

This way this node can output dozens of elements in CSV format.

Right now, something is wrong and the CSV-Node outputs extras chars at begining and end.

currently: "["A,1","B 1","3 1","last,1"]"

should be: "A,1","B 1","3 1","last,1"

Marcio

[{"id":"74b83fff.8b47c","type":"inject","name":"","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":100,"y":64,"z":"c15cdcca.3ea32","wires":[["ac422c23.53bdd"]]},{"id":"ac422c23.53bdd","type":"function","name":"","func":"msg.payload = {};\nmsg.payload.foo = \"B 1\";\nmsg.payload.last = \"last,1\";\nmsg.payload.bar = \"A,1\";\nmsg.payload.third = \"3 1\";\n\nm1=[];\nm1[0]=\"A,1\";\nm1[1]=\"B 1\";\nm1[2]=\"3 1\";\nm1[3]=\"last,1\";\nmsg.payload.m2=m1;\n\nreturn msg;","outputs":1,"noerr":0,"x":149,"y":122,"z":"c15cdcca.3ea32","wires":[["3078ee75.cf8712","4f2233b7.b0ddcc"]]},{"id":"3078ee75.cf8712","type":"csv","name":"","sep":",","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"bar,foo,third,last","x":305,"y":94,"z":"c15cdcca.3ea32","wires":[["d2f4b6e2.2d0b48","3e156321.c1ea9c"]]},{"id":"d2f4b6e2.2d0b48","type":"debug","name":"","active":true,"console":"false","complete":"payload","x":463,"y":94,"z":"c15cdcca.3ea32","wires":[]},{"id":"4f2233b7.b0ddcc","type":"csv","name":"","sep":",","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"v2","x":305,"y":149,"z":"c15cdcca.3ea32","wires":[["711857ad.8ee7a8","8a2369fe.75dc98"]]},{"id":"711857ad.8ee7a8","type":"debug","name":"","active":true,"console":"false","complete":"payload","x":465,"y":149,"z":"c15cdcca.3ea32","wires":[]},{"id":"3e156321.c1ea9c","type":"file","name":"","filename":"c:\\teste1.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","x":473,"y":44,"z":"c15cdcca.3ea32","wires":[]},{"id":"8a2369fe.75dc98","type":"file","name":"","filename":"c:\\teste2.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","x":469,"y":201,"z":"c15cdcca.3ea32","wires":[]}]

[Dave C-J]

Hi,

I think in your example the column is m2 not v2...
Actually there are two problems :-) One is mine ...
The actual result should have been - "[""A,1"",""B 1"",""3 1"",""last,1""]" - which looks even worse...
BUT... this is because... in your function you are building an array called m1 (which ends up as msg.payload.m2) and then in the CSV node you are asking for a single object (m2) out of the msg.payload... so you are asking for a single column (m2) - that should be a CSV representation of an array...
Your array is ["A,1","B 1","3 1","last,1"] so the way CSV escapes quotes is to make them double quotes... and to escape the commas (as this is a single column remember...) then it is also then wrapped in quotes... This will then give a single column output - m2...

so I will fix the bug... but I know that is not what you intend ...

As discussed earlier (and by Nick) it would be easier to just concatenate the strings in your function to create the csv you need rather than try to force into an object and use the node.

[Marcio Braga]

I see Dave,

I think the point is around the concept of the "column" field.

Maybe it could behave differently when data is matrix or property.

When matrix the column field could output as many columns as the matrix columns and with no columns names.

My point about matrix is that as CSV is used in spreadsheets, the CSV-node could take that in consideration.

With that, one can handle hundreds of values, with a simple operation.

I understand it is possible to make a loop to assemble the CSV line, but .... that is for standard programming environments ;-)

Marcio

Obs: I know CSV is used in many other applications ( I use CSV a lot in Rational System Architect ) ... but on the other side, I wonder why a matrix should end like this: 

[Dave C-J]

Marcio,

it ends up like that as that is the expected behaviour :-)....  you have given it an object with a property called m2 (that happens to be an array) - and then asked for one column to be created from a property called m2 to be output... and you got exactly that...

I know what you mean is (as Nick previously mentioned) that if we just pass in an array (rather than an object) then we should just handle that... I am looking at a basic implementation of that soon. 

For the array above it would then output 4 columns  A, B, C and D

with contents   A,1      B 1       3 1         last,1

[Marcio Braga]

That is great Dave.

Yes, a 4 columns matrix should correspond to a 4 columns in CSV format like you show.

Thank you.

Marcio

[Dave C-J]

Marcio,

but ONLY if you pass it in as an array on msg.payload :-) - not an object or property in an object....

I will use the column names config to set the column names if the user asks for the first line to contain column names...

[Marcio Braga]

Correct ... as Nick said: It is by convention that nodes operate on msg.payload. That is a key part of ensuring nodes can 'just work' when wired together.

So, you are the ensurers (guardians) of that  ;-)

Thanks

Marcio.  

[Dave C-J]

Yes, other opinions are available, but they are wrong :-)

[Dave C-J]

Marcio,

I have pushed some updates to the CSV node to the master branch that now accepts a simple array as input. It also accepts an array of arrays which then become multiple lines. It still accepts objects (that can then be templated using the column (property) names.

Here is an example 

[{"id":"3bf69ce6.c40964","type":"inject","z":"9e538f88.61ac7","name":"array of arrays","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":110,"y":1162,"wires":[["266e40c9.d991c"]]},{"id":"266e40c9.d991c","type":"function","z":"9e538f88.61ac7","name":"","func":"\nm1=[1,2,3];\nm2=[4,5,6];\nm3=[7,8,9];\n\nm = [m1,m2,m3];\nmsg.payload = m;\n\nreturn msg;","outputs":1,"noerr":0,"x":244,"y":1190,"wires":[["ae9e7f4b.51618","6fb0ee58.904f1"]]},{"id":"ae9e7f4b.51618","type":"csv","z":"9e538f88.61ac7","name":"","sep":",","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"C,B,A","x":413,"y":1266,"wires":[["6e45a0fa.91ba6"]]},{"id":"6fb0ee58.904f1","type":"debug","z":"9e538f88.61ac7","name":"Input","active":true,"console":"false","complete":"payload","x":458,"y":1358,"wires":[]},{"id":"b72068f9.48df98","type":"function","z":"9e538f88.61ac7","name":"","func":"\nm1=[];\nm1[0]=\"A,1\";\nm1[1]=\"B 1\";\nm1[2]=\"3 1\";\nm1[3]=\"last,1\";\nm1[4]=\"things in \\\"quotes\\\" are here\"\n\nmsg.payload = m1;\n\nreturn msg;","outputs":1,"noerr":0,"x":260,"y":1344,"wires":[["ae9e7f4b.51618","6fb0ee58.904f1"]]},{"id":"6e45a0fa.91ba6","type":"debug","z":"9e538f88.61ac7","name":"Output","active":true,"console":"true","complete":"payload","x":559,"y":1227,"wires":[]},{"id":"e9daa7f9.162558","type":"function","z":"9e538f88.61ac7","name":"","func":"\nm1={A:1,B:2,C:3};\nm2={A:4,B:5,C:6};\nm3={A:7,B:8,C:9};\n\nm = [m1,m2,m3];\nmsg.payload = m;\n\nreturn msg;","outputs":1,"noerr":0,"x":240,"y":1238,"wires":[["ae9e7f4b.51618","6fb0ee58.904f1"]]},{"id":"41acdf88.be532","type":"function","z":"9e538f88.61ac7","name":"","func":"\nm1=[1,2,3];\n\nmsg.payload = m1;\n\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":1290,"wires":[["ae9e7f4b.51618","6fb0ee58.904f1"]]},{"id":"5a6194df.a59e6c","type":"function","z":"9e538f88.61ac7","name":"","func":"\nm1={A:1,B:2,C:3};\n\nmsg.payload = m1;\n\nreturn msg;","outputs":1,"noerr":0,"x":225,"y":1400,"wires":[["ae9e7f4b.51618","6fb0ee58.904f1"]]},{"id":"e485ac3f.1b7a5","type":"inject","z":"9e538f88.61ac7","name":"more complex array","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":115,"y":1312,"wires":[["b72068f9.48df98"]]},{"id":"e543c708.1abc38","type":"inject","z":"9e538f88.61ac7","name":"array of objects","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":101,"y":1210,"wires":[["e9daa7f9.162558"]]},{"id":"5a910c11.a56ef4","type":"inject","z":"9e538f88.61ac7","name":"simple array","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":99,"y":1265,"wires":[["41acdf88.be532"]]},{"id":"611887af.9ee778","type":"inject","z":"9e538f88.61ac7","name":"simple object","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":94,"y":1364,"wires":[["5a6194df.a59e6c"]]}]

[Marcio Braga]

Dave, does this update means I need to upgrade my 0.11.2 Node-RED installation ? ( npm update -g --unsafe-perm node-red ).

--
http://nodered.org
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/QLQUrEBrVDM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.

[Dave C-J]

Hi,

.... well... it means it's in the github master version - which means that either you have to re-install it like a developer... (see http://nodered.org/docs/getting-started/installation.html bottom of page).... or wait until we release the next version... (which we are working towards right now)... or I guess you could download the two CSV files from https://github.com/node-red/node-red/tree/master/nodes/core/parsers and manually overwrite the existing... 

[Marcio Braga]

It works Dave !

Just to double check, I made a small change in your flow to emphasize the point about the Column field be set or not.

The top 3 injections ( matrix-based data ) flow to a CSV-node where the Column field is blank, at it works fine.

The botton 2 injections ( object-based data ) flow to a former CSV-node (with Column set ).

[{"id":"98ba5af7.6745a8","type":"inject","name":"array of arrays","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":212,"y":84,"z":"3f01c6ba.c0fe3a","wires":[["8d41af69.72be5"]]},{"id":"8d41af69.72be5","type":"function","name":"","func":"\nm1=[1,2,3];\nm2=[4,5,6];\nm3=[7,8,9];\n\nm = [m1,m2,m3];\nmsg.payload = m;\n\nreturn msg;","outputs":1,"noerr":0,"x":399,"y":84,"z":"3f01c6ba.c0fe3a","wires":[["a1f23561.5e0dc8","e8d8d7bc.172728"]]},{"id":"a1f23561.5e0dc8","type":"csv","name":"CSV with Columns in blank","sep":",","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"","x":650,"y":84,"z":"3f01c6ba.c0fe3a","wires":[["3405faaa.cbfa06"]]},{"id":"e8d8d7bc.172728","type":"debug","name":"Input","active":true,"console":"false","complete":"payload","x":629,"y":214,"z":"3f01c6ba.c0fe3a","wires":[]},{"id":"495d5a38.b6a2a4","type":"function","name":"","func":"\nm1=[];\nm1[0]=\"A,1\";\nm1[1]=\"B 1\";\nm1[2]=\"3 1\";\nm1[3]=\"last,1\";\nm1[4]=\"things in \\\"quotes\\\" are here\"\n\nmsg.payload = m1;\n\nreturn msg;","outputs":1,"noerr":0,"x":400,"y":177,"z":"3f01c6ba.c0fe3a","wires":[["a1f23561.5e0dc8","e8d8d7bc.172728"]]},{"id":"3405faaa.cbfa06","type":"debug","name":"Output","active":true,"console":"true","complete":"payload","x":886,"y":84,"z":"3f01c6ba.c0fe3a","wires":[]},{"id":"cc55a29e.33aa6","type":"function","name":"","func":"\nm1={A:1,B:2,C:3};\nm2={A:4,B:5,C:6};\nm3={A:7,B:8,C:9};\n\nm = [m1,m2,m3];\nmsg.payload = m;\n\nreturn msg;","outputs":1,"noerr":0,"x":389,"y":299,"z":"3f01c6ba.c0fe3a","wires":[["e8d8d7bc.172728","518bfd59.ae7404"]]},{"id":"18d52034.e72ae","type":"function","name":"","func":"\nm1=[1,2,3];\n\nmsg.payload = m1;\n\nreturn msg;","outputs":1,"noerr":0,"x":399,"y":133,"z":"3f01c6ba.c0fe3a","wires":[["a1f23561.5e0dc8","e8d8d7bc.172728"]]},{"id":"21bdd110.de422e","type":"function","name":"","func":"\nm1={A:1,B:2,C:3};\n\nmsg.payload = m1;\n\nreturn msg;","outputs":1,"noerr":0,"x":387,"y":355,"z":"3f01c6ba.c0fe3a","wires":[["e8d8d7bc.172728","518bfd59.ae7404"]]},{"id":"340bd1ba.cbf42e","type":"inject","name":"more complex array","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":221,"y":177,"z":"3f01c6ba.c0fe3a","wires":[["495d5a38.b6a2a4"]]},{"id":"68021b38.97fde4","type":"inject","name":"array of objects","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":209,"y":300,"z":"3f01c6ba.c0fe3a","wires":[["cc55a29e.33aa6"]]},{"id":"4b2d2047.b4d2e","type":"inject","name":"simple array","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":204,"y":132,"z":"3f01c6ba.c0fe3a","wires":[["18d52034.e72ae"]]},{"id":"b7f33c7a.480cc","type":"inject","name":"simple object","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":206,"y":354,"z":"3f01c6ba.c0fe3a","wires":[["21bdd110.de422e"]]},{"id":"518bfd59.ae7404","type":"csv","name":"CSV with columns set","sep":",","hdrin":"","hdrout":"","multi":"one","ret":"\\n","temp":"A,B,C","x":612,"y":352,"z":"3f01c6ba.c0fe3a","wires":[["40a629ad.bf59d8"]]},{"id":"40a629ad.bf59d8","type":"debug","name":"","active":true,"console":"false","complete":"false","x":873,"y":351,"z":"3f01c6ba.c0fe3a","wires":[]}]

[Dave C-J]

Marcio

make the lower one like this

[{"id":"87b15b12.784ea8","type":"csv","z":"2532920.fdacd6e","name":"CSV with columns set","sep":",","hdrin":"","hdrout":"","multi":"one","ret":"\\n","temp":"C,B,A","x":595,"y":685,"wires":[["c2bad890.3d4528"]]}]

the point is that the columns can be used to order the csv in any order - but only if you pick it out of an object...  

with an array it makes no difference... so the above  now has columns  C,B,A -   so the csv is 3.2.1    not 1,2,3 

[Marcio Braga]

Dave,

Nice and interesting point the ability to define the sequence when the payload is an object.

By the way,

- when you send an object to CSV node and you specify a property that does not exist, the CSV node does not indicate error and send a blank value.

- but when you send a matrix and one of its elements is not set (or null), the CSV node indicates error. 

Any way, I re-made my flow adding the new CSV node. Now I do not need to construct "manually" the CSV line ( I do not need to handle the CSV format ... complexity ;-).

A just load a matrix with elements and send it to the CSV node.

Thank you Dave. 

[Dave C-J]

Hi Marcio

good feedback - thanks...

I think the first is correct... as it would allow you to specify a column - and then not have to supply it in the object and you would get a blank column.... as what else should you put in a CSV to indicate a missing value ?  I thinks this should be OK...

But yes the second is incorrect - it should also allow blank or null elements and output a blank column ,,  as per the missing object... I'll fix it in a few minutes... 

thanks again.

[Dave C-J]

Marcio

now handles [ 1,2,,4 ] and  [ 1,null,3 ]   etc

https://raw.githubusercontent.com/node-red/node-red/master/nodes/core/parsers/70-CSV.js

[Marcio Braga]

Dave, this is becoming TOO easy ... scary !!!

Thank you.

[Dave C-J]

:-)