Using AWK on CSV file using function node

Hello! Need to extract a column from a CSV file into another CSV file. I was searching and found that using AWK could help but have no idea how to proceed.

Site link for reference
https://www.joeldare.com/wiki/using_awk_on_csv_files
Also, I was trying to create the flow given below

[{"id":"f23392c0.2a45e","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"2d799aab.019f36","type":"http in","z":"f23392c0.2a45e","name":"","url":"/temp","method":"get","upload":false,"swaggerDoc":"","x":180,"y":120,"wires":[["66427477.4008fc"]]},{"id":"c10a2f37.5190f","type":"http response","z":"f23392c0.2a45e","name":"","statusCode":"","headers":{},"x":640,"y":120,"wires":[]},{"id":"66427477.4008fc","type":"template","z":"f23392c0.2a45e","name":"","field":"payload","fieldType":"msg","format":"html","syntax":"mustache","template":"<!DOCTYPE html>\n<html>\n    <head>\n        <link rel=\"stylesheet\" href=\"https://cdn.jsdelivr.net/npm/bootswatch@4.5.2/dist/slate/bootstrap.min.css\" integrity=\"undefined\" crossorigin=\"anonymous\">\n    </head>\n    \n<body>\n\n<h1 style=\"font-size:60px;\">Heading 1</h1>\n<h2>Heading 2</h2>\n<h3>Heading 3</h3>\n<h4>Heading 4</h4>\n<h5>Heading 5</h5>\n<h6>Heading 6</h6>\n<p>This is a paragraph.</p>\n<p>This is another paragraph.</p>\n\n<h1>This is heading 1</h1>\n<p>This is some text.</p>\n<hr>\n<h2>This is heading 2</h2>\n<p>This is some other text.</p>\n<hr>\n\n<pre>\n  My Bonnie lies over the ocean.\n\n  My Bonnie lies over the sea.\n\n  My Bonnie lies over the ocean.\n\n  Oh, bring back my Bonnie to me.\n</pre>\n\n<p>I am normal</p>\n<p style=\"color:red;\">I am red</p>\n<p style=\"color:blue;\">I am blue</p>\n<p style=\"font-size:50px;\">I am big</p>\n\n<h1 style=\"font-family:verdana;\">This is a heading</h1>\n<p style=\"font-family:courier;\">This is a paragraph.</p>\n\n<h1 style=\"font-size:300%;\">This is a heading</h1>\n<p style=\"font-size:160%;\">This is a paragraph.</p>\n\n</body>\n</html>","output":"str","x":400,"y":120,"wires":[["c10a2f37.5190f"]]},{"id":"ec51fb28.ba1e38","type":"function","z":"f23392c0.2a45e","name":"transform data","func":"//as per info panel, the data needs to be formatted\n//as [ \"Choice 1\", \"Choice 2\", {\"Choice 3\":\"3\"} ]\n//and sent in msg.options.\nmsg.options = [];//create empty array\nfor(let i = 0; i < msg.payload.length; i++)\n    {\n    let row = msg.payload[i]; //get the row\n    let opt = {};//make new opt object\n    opt[row.Work_Center] = row.Work_Center; \n    msg.options.push(opt);//add the opt to array    \n    }\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":740,"y":360,"wires":[["849e4a87.a0a4e8"]]},{"id":"4cd838bf.b81ef8","type":"csv","z":"f23392c0.2a45e","name":"","sep":",","hdrin":true,"hdrout":"all","multi":"mult","ret":"\\r\\n","temp":"","skip":"0","strings":true,"include_empty_strings":false,"include_null_values":false,"x":550,"y":360,"wires":[["ec51fb28.ba1e38"]]},{"id":"378e748.df7018c","type":"file in","z":"f23392c0.2a45e","name":"mt02order","filename":"C:\\Users\\Sahil\\Desktop\\testing\\mt02order.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":400,"y":360,"wires":[["4cd838bf.b81ef8"]]},{"id":"abbca1c8.8cf72","type":"inject","z":"f23392c0.2a45e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":240,"y":360,"wires":[["378e748.df7018c"]]},{"id":"44b692da.1bc42c","type":"file","z":"f23392c0.2a45e","name":"work center no.","filename":"C:\\Users\\Sahil\\Desktop\\testing\\workcenter.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":1080,"y":360,"wires":[[]]},{"id":"849e4a87.a0a4e8","type":"csv","z":"f23392c0.2a45e","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\r\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":910,"y":360,"wires":[["44b692da.1bc42c"]]}]

I'm stuck here can anyone help.

Here is a recent example of joining two csv files, can not help more as you supplied no examples of your csv files.

Need to export "Work_Center" column to another CSV file
mt02order.txt (4.4 KB)

I have simulated the reading of the cvs file. Then in the first cvs node i target the second column by adding a comma seperated list in the columns field, columns you require you give names (any name you wish) to skip a column just add a extra comma with no text.
Your txt example had ,,,,,,,,,,,,,,,, on the second line so I also skipped the first two lines.

Then I just send it back through another csv file, to convert it back to text csv string. From there you could save it to file.

[{"id":"e24c1fb8.250e3","type":"inject","z":"c74669a0.6a34f8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":2920,"wires":[["be07bef2.3f262"]]},{"id":"be07bef2.3f262","type":"template","z":"c74669a0.6a34f8","name":"simulate file","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Order_No,Work_Center,Material,Work_Center_Description,Cycle_TIme,Material_Description,Order_Qty,Confirm_Qty,Creation_date,Pending_Qty,Person_Responsible,Plant,Drg_No\n,,,,,,,,,,,,\n100070432,M02HD008,C-M-201-0696-01,HD008 HEADER,3,SHAFT SWING ARM UPTO COLD FORGING,750,750,08.06.2021,0,RAM SINGH,MT02,M-0696\n100070441,M02MBF01,C-M-201-3449-02,MBF1 MBF HARDENING & WASHING,1.67,BOLT SWING ARM PIVOT UPTO HT,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",PAWAN,MT02,M-3449\n100070296,M02MBF01,C-M-201-3449-02,MBF1 MBF HARDENING & WASHING,1.67,BOLT SWING ARM PIVOT UPTO HT,\"2,000.00\",\"2,000.00\",05.06.2021,0,PAWAN,MT02,M-3449\n100070441,M02ST003,C-M-201-3449-03,ST03 STRAIGHTENING,8,BOLT SWING ARM PIVOT UPTO STRAIGHTENING,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",AMIT SHARMA,MT02,M-3449\n100070296,M02ST003,C-M-201-3449-03,ST03 STRAIGHTENING,8,BOLT SWING ARM PIVOT UPTO STRAIGHTENING,\"2,000.00\",\"2,000.00\",05.06.2021,0,AMIT SHARMA,MT02,M-3449\n100070297,M02CL013,C-M-201-3449-04,CL13 CENTRE LESS GRINDER,7,BOLT SWING ARM PIVOT UPTO DIA HALF GRIND,\"2,000.00\",\"2,000.00\",05.06.2021,0,AMIT SHARMA,MT02,M-3449\n100070441,M02CL013,C-M-201-3449-04,CL13 CENTRE LESS GRINDER,7,BOLT SWING ARM PIVOT UPTO DIA HALF GRIND,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",AMIT SHARMA,MT02,M-3449\n100070297,M02CL013,C-M-201-3449-05,CL13 CENTRE LESS GRINDER,6,BOLT SWING ARM PIVOT UPTO DIA FINAL GRIN,\"2,000.00\",\"2,000.00\",05.06.2021,0,AMIT SHARMA,MT02,M-3449\n100070441,M02CL013,C-M-201-3449-05,CL13 CENTRE LESS GRINDER,6,BOLT SWING ARM PIVOT UPTO DIA FINAL GRIN,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",AMIT SHARMA,MT02,M-3449\n100070441,M02TR007,C-M-201-3449-06,TR07 THREAD ROLLING,7,BOLT SWING ARM PIVOT UPTO THREAD ROLLING,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",AMIT SHARMA,MT02,M-3449\n100070298,M02TR007,C-M-201-3449-06,TR07 THREAD ROLLING,7,BOLT SWING ARM PIVOT UPTO THREAD ROLLING,\"2,000.00\",\"2,000.00\",05.06.2021,0,AMIT SHARMA,MT02,M-3449\n100070356,M02SB001,C-M-219-0286-04,SB01 SHOT BLASTING,7.2,PINION KICK STARTER UPTO SHOT BLASTING,\"15,911.00\",\"4,420.00\",07.06.2021,\"11,491.00\",UDAY THAKUR,MT02,M-0286","output":"str","x":360,"y":2920,"wires":[["b17914eb.552ac8"]]},{"id":"b17914eb.552ac8","type":"csv","z":"c74669a0.6a34f8","name":"","sep":",","hdrin":false,"hdrout":"none","multi":"mult","ret":"\\n","temp":",Work_Center","skip":"2","strings":true,"include_empty_strings":"","include_null_values":"","x":510,"y":2920,"wires":[["cc09450.9b56938"]]},{"id":"cc09450.9b56938","type":"csv","z":"c74669a0.6a34f8","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":350,"y":3020,"wires":[["22b84a54.36560e"]]},{"id":"22b84a54.36560e","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":530,"y":3020,"wires":[]}]

Thankyou. Didn't thought it to be this simple :grimacing:

there is one more thing with this flow. I also need to remove the duplicate values. I was going through solutions given below. How can I make them use in this flow.

[{"id":"be07bef2.3f262","type":"template","z":"c74669a0.6a34f8","name":"simulate file","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Order_No,Work_Center,Material,Work_Center_Description,Cycle_TIme,Material_Description,Order_Qty,Confirm_Qty,Creation_date,Pending_Qty,Person_Responsible,Plant,Drg_No\n,,,,,,,,,,,,\n100070432,M02HD008,C-M-201-0696-01,HD008 HEADER,3,SHAFT SWING ARM UPTO COLD FORGING,750,750,08.06.2021,0,RAM SINGH,MT02,M-0696\n100070441,M02MBF01,C-M-201-3449-02,MBF1 MBF HARDENING & WASHING,1.67,BOLT SWING ARM PIVOT UPTO HT,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",PAWAN,MT02,M-3449\n100070296,M02MBF01,C-M-201-3449-02,MBF1 MBF HARDENING & WASHING,1.67,BOLT SWING ARM PIVOT UPTO HT,\"2,000.00\",\"2,000.00\",05.06.2021,0,PAWAN,MT02,M-3449\n100070441,M02ST003,C-M-201-3449-03,ST03 STRAIGHTENING,8,BOLT SWING ARM PIVOT UPTO STRAIGHTENING,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",AMIT SHARMA,MT02,M-3449\n100070296,M02ST003,C-M-201-3449-03,ST03 STRAIGHTENING,8,BOLT SWING ARM PIVOT UPTO STRAIGHTENING,\"2,000.00\",\"2,000.00\",05.06.2021,0,AMIT SHARMA,MT02,M-3449\n100070297,M02CL013,C-M-201-3449-04,CL13 CENTRE LESS GRINDER,7,BOLT SWING ARM PIVOT UPTO DIA HALF GRIND,\"2,000.00\",\"2,000.00\",05.06.2021,0,AMIT SHARMA,MT02,M-3449\n100070441,M02CL013,C-M-201-3449-04,CL13 CENTRE LESS GRINDER,7,BOLT SWING ARM PIVOT UPTO DIA HALF GRIND,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",AMIT SHARMA,MT02,M-3449\n100070297,M02CL013,C-M-201-3449-05,CL13 CENTRE LESS GRINDER,6,BOLT SWING ARM PIVOT UPTO DIA FINAL GRIN,\"2,000.00\",\"2,000.00\",05.06.2021,0,AMIT SHARMA,MT02,M-3449\n100070441,M02CL013,C-M-201-3449-05,CL13 CENTRE LESS GRINDER,6,BOLT SWING ARM PIVOT UPTO DIA FINAL GRIN,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",AMIT SHARMA,MT02,M-3449\n100070441,M02TR007,C-M-201-3449-06,TR07 THREAD ROLLING,7,BOLT SWING ARM PIVOT UPTO THREAD ROLLING,\"3,000.00\",\"1,076.00\",08.06.2021,\"1,924.00\",AMIT SHARMA,MT02,M-3449\n100070298,M02TR007,C-M-201-3449-06,TR07 THREAD ROLLING,7,BOLT SWING ARM PIVOT UPTO THREAD ROLLING,\"2,000.00\",\"2,000.00\",05.06.2021,0,AMIT SHARMA,MT02,M-3449\n100070356,M02SB001,C-M-219-0286-04,SB01 SHOT BLASTING,7.2,PINION KICK STARTER UPTO SHOT BLASTING,\"15,911.00\",\"4,420.00\",07.06.2021,\"11,491.00\",UDAY THAKUR,MT02,M-0286","output":"str","x":670,"y":2920,"wires":[["b17914eb.552ac8"]]},{"id":"b17914eb.552ac8","type":"csv","z":"c74669a0.6a34f8","name":"","sep":",","hdrin":false,"hdrout":"none","multi":"mult","ret":"\\n","temp":",Work_Center","skip":"2","strings":true,"include_empty_strings":"","include_null_values":"","x":820,"y":2920,"wires":[["696e622c.8f6c0c","cc09450.9b56938"]]},{"id":"e24c1fb8.250e3","type":"inject","z":"c74669a0.6a34f8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":490,"y":2920,"wires":[["be07bef2.3f262"]]},{"id":"696e622c.8f6c0c","type":"change","z":"c74669a0.6a34f8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$distinct(payload)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":730,"y":2960,"wires":[["a8bb66b4.c07428"]]},{"id":"cc09450.9b56938","type":"csv","z":"c74669a0.6a34f8","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":980,"y":2920,"wires":[["22b84a54.36560e"]]},{"id":"a8bb66b4.c07428","type":"csv","z":"c74669a0.6a34f8","name":"","sep":",","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":950,"y":2960,"wires":[["5ed4e9c2.5e046"]]},{"id":"22b84a54.36560e","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1140,"y":2920,"wires":[]},{"id":"5ed4e9c2.5e046","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1210,"y":2960,"wires":[]}]

In future It would be best to give a clear explaination of what you would like, e.g. a example of input and how you would like output to look , then the people helping would be able to give you an answer first time.

1 Like

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