Structure the datas and create a CSV file

General
1

Hello everybody,
I am not really an expert to Nodered and I would need help on a particular point.
I am retrieving an XML file and would like to structure the datas to create a CSV file.
I watched some tutorials and spent time on this problem but I can't find the solution.

For the example:

  1. I get my XML file which I convert to Object.
  2. I have 3 values ​​that I would like to upload in my CSV

image

The result that I want

image

How can I create this table?

I tried with the change node taking each path but could not find a solution.

[
{
"name":payload.ticket.layout[0].$.name,
"id":payload.ticket.layout[0].contents[0].job[0].$.id,
"items":payload.ticket.layout[0].contents[0].job[0].$.items
}
]

If someone have an solution, it’s with great pleasure.

Thank’s a lot.

Alexandre

2

Can you post the actual msg.payload as preformatted text (using the </> button) rather than an image?

It's much easier to answer your question when we have data to work with.

3

If your paths are correct you would need to map through the array returned from the xml node.
e.g

[{"id":"78cae634.9cda78","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = msg.payload.ticket.layout.map(e => {\n   return {\n       name: e.$.name,\n       id : e.contents[0].job[0].$.id,\n       items: e.contents[0].job[0].$.items\n   }\n});\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":660,"wires":[["ed96e604.4547c8"]]},{"id":"912d5201.23e24","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"ticket\":{\"layout\":[{\"$\":{\"name\":\"bill\"},\"contents\":[{\"job\":[{\"$\":{\"id\":\"1\",\"items\":\"12\"}}]}]},{\"$\":{\"name\":\"jill\"},\"contents\":[{\"job\":[{\"$\":{\"id\":\"2\",\"items\":\"10\"}}]}]}]}}","payloadType":"json","x":120,"y":660,"wires":[["78cae634.9cda78"]]},{"id":"ed96e604.4547c8","type":"csv","z":"bf9e1e33.030598","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":470,"y":660,"wires":[["cbdb2c1.1139b5"]]},{"id":"cbdb2c1.1139b5","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":680,"y":660,"wires":[]}]

Using a function node and Javascript.

msg.payload = msg.payload.ticket.layout.map(e => {
   return {
       name: e.$.name,
       id : e.contents[0].job[0].$.id,
       items: e.contents[0].job[0].$.items
   }
});
return msg;

You could do similar in a change node using JSONata. $ has special meaning in JSONata, in this case the scope of each element of payload.tickets.layout, so it needs to be enclosed in backticks or quotes.

[{"id":"912d5201.23e24","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"ticket\":{\"layout\":[{\"$\":{\"name\":\"bill\"},\"contents\":[{\"job\":[{\"$\":{\"id\":\"1\",\"items\":\"12\"}}]}]},{\"$\":{\"name\":\"jill\"},\"contents\":[{\"job\":[{\"$\":{\"id\":\"2\",\"items\":\"10\"}}]}]}]}}","payloadType":"json","x":110,"y":660,"wires":[["b3a6f895.6e9b2"]]},{"id":"b3a6f895.6e9b2","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.ticket.layout.{\t       \"name\": $.`$`.name,\t       \"id\" : $.contents[0].job[0].`$`.id,\t       \"items\": $.contents[0].job[0].`$`.items\t   }","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":660,"wires":[["ed96e604.4547c8"]]},{"id":"ed96e604.4547c8","type":"csv","z":"bf9e1e33.030598","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":530,"y":660,"wires":[["cbdb2c1.1139b5"]]},{"id":"cbdb2c1.1139b5","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":690,"y":660,"wires":[]}]

$$.payload.ticket.layout.{
       "name": $.`$`.name,
       "id" : $.contents[0].job[0].'$'.id,
       "items": $.contents[0].job[0]."$".items
   }

but you can do it in a low code manner to.
e.g.

[{"id":"912d5201.23e24","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"ticket\":{\"layout\":[{\"$\":{\"name\":\"bill\"},\"contents\":[{\"job\":[{\"$\":{\"id\":\"1\",\"items\":\"12\"}}]}]},{\"$\":{\"name\":\"jill\"},\"contents\":[{\"job\":[{\"$\":{\"id\":\"2\",\"items\":\"10\"}}]}]}]}}","payloadType":"json","x":110,"y":660,"wires":[["b3a6f895.6e9b2"]]},{"id":"b3a6f895.6e9b2","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"move","p":"payload.ticket.layout","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":620,"wires":[["26608e6b.f5ac7a"]]},{"id":"26608e6b.f5ac7a","type":"split","z":"bf9e1e33.030598","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":510,"y":620,"wires":[["385838e8.0935e"]]},{"id":"385838e8.0935e","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload.name","pt":"msg","to":"payload.$.name","tot":"msg"},{"t":"set","p":"payload.id","pt":"msg","to":"payload.contents[0].job[0].$.id","tot":"msg"},{"t":"set","p":"payload.items","pt":"msg","to":"payload.contents[0].job[0].$.items","tot":"msg"},{"t":"delete","p":"payload.$","pt":"msg"},{"t":"delete","p":"payload.contents","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":690,"y":620,"wires":[["f93b6776.d83fc8"]]},{"id":"f93b6776.d83fc8","type":"join","z":"bf9e1e33.030598","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":430,"y":680,"wires":[["ed96e604.4547c8"]]},{"id":"ed96e604.4547c8","type":"csv","z":"bf9e1e33.030598","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":590,"y":680,"wires":[["cbdb2c1.1139b5"]]},{"id":"cbdb2c1.1139b5","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":780,"wires":[]}]
4

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