Column to row & separated by comma

General
1

Need help. need to convert column data to row separated by comma in msg.payload.
I get column data from a SQL table, which is in the form of array then I use a csv node
image
which converts it into string. But I need a Payload as "M01CN001, M01CN003, M01CN011 ..... so on".
image

2

You will need to provide a sample set of data direct from the SQL node as we dont know the original format.

Could you share a small sample (e.g. 3 elements of the data array).
Use the "copy value" button in the debug window to ensure you capture the the data in correct format.

3

Sample data

[{"MACHINE_CODE":"M01CN001"},{"MACHINE_CODE":"M01CN003"},{"MACHINE_CODE":"M01CN011"}]

4

Assuming msg.payload is the data array, in a function node...

msg.payload = msg.payload.map(e => e.MACHINE_CODE).join(", ")
return msg;

image

5

Thanks a lot :+1: it works.

image

6

NOTE: if you intend on building a proper CSV file & any of your columns have commas, you should probably use the CSV node...

image
image566×503 34.8 KB

Demo flow (import using CTRL+I)

[{"id":"30121ee119157788","type":"function","z":"85e1791bc0cd6285","name":"","func":"msg.payload = msg.payload.map(e => e.MACHINE_CODE).join(\", \")\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":2340,"y":120,"wires":[["9e3a8ea54f01c9c9"]]},{"id":"9e3a8ea54f01c9c9","type":"debug","z":"85e1791bc0cd6285","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":2350,"y":180,"wires":[]},{"id":"29254c7c855287d3","type":"function","z":"85e1791bc0cd6285","name":"","func":"msg.payload = msg.payload.map(e => e.MACHINE_CODE)\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":2360,"y":360,"wires":[["5dca6444d36b023e"]]},{"id":"0924c41f0799c94f","type":"debug","z":"85e1791bc0cd6285","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":2370,"y":480,"wires":[]},{"id":"5dca6444d36b023e","type":"csv","z":"85e1791bc0cd6285","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":2350,"y":420,"wires":[["0924c41f0799c94f"]]},{"id":"7888ca3f05237050","type":"inject","z":"85e1791bc0cd6285","name":"SQL Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"MACHINE_CODE\":\"M01CN,001\"},{\"MACHINE_CODE\":\"M01CN,003\"},{\"MACHINE_CODE\":\"M01CN,011\"}]","payloadType":"json","x":2340,"y":300,"wires":[["29254c7c855287d3"]]},{"id":"544ae393bdb9078a","type":"inject","z":"85e1791bc0cd6285","name":"SQL Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"MACHINE_CODE\":\"M01CN,001\"},{\"MACHINE_CODE\":\"M01CN,003\"},{\"MACHINE_CODE\":\"M01CN,011\"}]","payloadType":"json","x":2320,"y":60,"wires":[["30121ee119157788"]]}]
7

Thanks for the info :+1:. I'll keep this in mind.

But for now I only needed the payload string without quotes.

8

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