# Using AWK on CSV file using function node **URL:** \ **Category:** General\ **Created:** [16 June 2021 14:57 UTC](https://discourse.nodered.org/t/using-awk-on-csv-file-using-function-node/47251 "2021-06-16T14:57:55Z")\ **Posts on this page:** 7\ **Page:** 1

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\ **Post date:** [16 June 2021 14:57 UTC](https://discourse.nodered.org/t/using-awk-on-csv-file-using-function-node/47251/1 "2021-06-16T14:57:56Z")

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](https://www.joeldare.com/wiki/using_awk_on_csv_files) Also, I was trying to create the flow given below ```auto [{"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":"\n\n \n \n \n \n\n\n

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

This is a paragraph.

This is another paragraph.

\n\n

This is heading 1

This is some text.

This is heading 2

This is some other text.

\n\n

\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

\n\n

I am normal

I am red

I am blue

I am big

\n\n

This is a heading

This is a paragraph.

\n\n

This is a heading

This is a paragraph.

\n\n\n","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. ---

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\ **Post date:** [16 June 2021 17:34 UTC](https://discourse.nodered.org/t/using-awk-on-csv-file-using-function-node/47251/2 "2021-06-16T17:34:59Z")

Here is a recent example of joining two csv files, can not help more as you supplied no examples of your csv files. > [@Merging 2 csv LEFT JOIN](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/11): > > If the csvs are not same items then search for matching keys e.g. $map(csv1, function($v,$i){ $merge([$v, $$.payload[Item = $v.Item]]) }) here is testing flow, as i have no files i created cvs's with template nodes. [{"id":"bdbf843d.9a2268","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":110,"y":4020,"wires":[["e4bc4684.bce58"]]},{"id":"e… ---

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\ **Post date:** [16 June 2021 18:21 UTC](https://discourse.nodered.org/t/using-awk-on-csv-file-using-function-node/47251/3 "2021-06-16T18:21:31Z")

Need to export "Work\_Center" column to another CSV file [mt02order.txt](https://discourse.nodered.org/uploads/short-url/n7LrasVGTaATziz5wklWFPyHB6o.txt) (4.4 KB) ---

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\ **Post date:** [16 June 2021 18:55 UTC](https://discourse.nodered.org/t/using-awk-on-csv-file-using-function-node/47251/4 "2021-06-16T18:55:50Z")

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. ```auto [{"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":[]}] ``` ---

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\ **Post date:** [17 June 2021 06:39 UTC](https://discourse.nodered.org/t/using-awk-on-csv-file-using-function-node/47251/5 "2021-06-17T06:39:57Z")

Thankyou. Didn't thought it to be this simple 😬 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. > [@Ui\_dropdown: set msg.options for multiple entries](https://discourse.nodered.org/t/ui-dropdown-set-msg-options-for-multiple-entries/37318/2): > > Your function is not returning the msg. so no option set. Then your change node overwrites msg.option with msg.payload. But to only pass on changed msg.options compare the array lengths. e.g. [{"id":"f454e332.520a5","type":"inject","z":"63af3844.ce5a8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"test1/Node1/2/Data","payload":"{\"ID\":\"13dfsl48\",\"TimeStamp\":0,\"SensorNamwe\":\"Sensor\_3\",\"SensorRaw\":-332,\"Sen… > [@Remove duplicates from an array of objects](https://discourse.nodered.org/t/remove-duplicates-from-an-array-of-objects/28013/2): > > I would take the lazy approach and use a function node. var prev = null; msg.payload = msg.payload.filter((item) =\> { var keep = (item.x !== prev); prev = item.x; return keep; }); return msg; This assumes you only want to dedupe on 'x', and they are all in sequence. You would have to modify it a bit if that's not the case. ---

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\ **Post date:** [17 June 2021 07:31 UTC](https://discourse.nodered.org/t/using-awk-on-csv-file-using-function-node/47251/6 "2021-06-17T07:31:44Z")

```auto [{"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. ---

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\ **Post date:** [1 July 2021 07:32 UTC](https://discourse.nodered.org/t/using-awk-on-csv-file-using-function-node/47251/7 "2021-07-01T07:32:15Z")

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