tag:blogger.com,1999:blog-1255024703457423340.post3305459963482280955..comments2024-03-13T13:54:48.044+05:30Comments on The UNIX School: awk - 10 examples to insert / remove / update fields of a CSV fileGuru Prasadhttp://www.blogger.com/profile/07898245472802947101noreply@blogger.comBlogger46125tag:blogger.com,1999:blog-1255024703457423340.post-258044982003309182019-07-08T19:18:30.489+05:302019-07-08T19:18:30.489+05:30is it possible to update or replace a specific cel...is it possible to update or replace a specific cell in a excel ?<br /><br />beautiful quotes...https://www.blogger.com/profile/03525872504570441817noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-37688297792243100932018-11-23T17:39:20.482+05:302018-11-23T17:39:20.482+05:30How can we insert two columns before first column
...How can we insert two columns before first column<br />Example: file.txt has<br />1<br />2<br />3<br />4<br />5<br /><br />Need to have<br />Value 100 1<br />Value 102 2<br />Value 103 3<br />Value 104 4<br />Value 105 5<br />vasanthhttps://www.blogger.com/profile/06777398538204900519noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-37976645952924841712018-09-03T22:26:51.230+05:302018-09-03T22:26:51.230+05:30Hi
I have a text file with text like
[YTS.AM]/...Hi <br /><br />I have a text file with text like <br /> [YTS.AM]/www.YTS.AM.jpg<br /><br />I would like to insert text to make a command like...<br />rm "[1080p] [YTS.AM]/www.YTS.AM.jpg"<br /><br />please helpAnonymoushttps://www.blogger.com/profile/04590525962924682156noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-72945801791413029882017-07-03T08:57:17.443+05:302017-07-03T08:57:17.443+05:30Hi Guru,
I'd to update the second column in a...Hi Guru,<br /><br />I'd to update the second column in a file based on the the hour and minute in the first column. The delimiter is ','.<br /><br />For example, the original file contains the following <br /> <br />2017-07-02 aa:bb:00,0<br />2017-07-02 cc:dd:00,0<br /><br />The updated file is<br /><br />echo 2017-07-02 aa:bb:00,`expr 1000 '*' aa '+' 20 '*' bb`<br />echo 2017-07-02 cc:dd:00,`expr 1000 '*' cc '+' 20 '*' dd`<br />Anonymoushttps://www.blogger.com/profile/00001549011460018973noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-78296388625466163202017-06-20T07:16:16.045+05:302017-06-20T07:16:16.045+05:30awk 'NR==1{$(NF+1)="Total"}NR!=1{$(N...awk 'NR==1{$(NF+1)="Total"}NR!=1{$(NF+1)=$2+$3;}1' FS=, OFS=, fileGuru Prasadhttps://www.blogger.com/profile/07898245472802947101noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-36412627935256764822017-05-21T15:48:44.697+05:302017-05-21T15:48:44.697+05:30awk -F"|" '$1==101{$2="b"}...awk -F"|" '$1==101{$2="b"}1' OFS="|"Guru Prasadhttps://www.blogger.com/profile/07898245472802947101noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-39925668027381654922017-04-21T17:19:11.548+05:302017-04-21T17:19:11.548+05:30Hi Guru,
I have a file with "|" separato...Hi Guru,<br />I have a file with "|" separator. we need to update a column value based on the 1st column and will print in output file note that file does not have fixed columns. <br />Eg: 101|a|b|c<br />101|q|a|f<br />102|w|r|p|f<br />102|e|s|d|g<br />103|l|d|v|h|b|x<br />103|z|x|c|b|n|m<br />need to update some column value based on the first column value.<br />Really appreciate your help and Thank YouDavidhttps://www.blogger.com/profile/11936634421656139697noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-36457183326349129612017-02-22T21:30:39.042+05:302017-02-22T21:30:39.042+05:30Hi Guru,
i have:
time,nooffiles1,nooffiles2,
HR0...Hi Guru,<br /><br />i have:<br /><br />time,nooffiles1,nooffiles2,<br />HR00:,1,2<br />HR01:,3,4<br /><br />i want to become like this<br /><br />time,nooffiles1,nooffiles2,total<br />HR00:,1,2,3<br />HR01:,3,4,7<br /><br />how to generate this?<br />Thank you very muchAnonymoushttps://www.blogger.com/profile/07481798092166367729noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-8558047077806332422016-08-24T04:08:23.298+05:302016-08-24T04:08:23.298+05:30Hello
can we add a colum or columns not a serie o...Hello<br /><br />can we add a colum or columns not a serie of numbers but containing characters or something else thank youabeslamhttps://www.blogger.com/profile/00040585601026055547noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-2857469364229049842016-01-18T15:40:13.883+05:302016-01-18T15:40:13.883+05:30Hi Guru,
I have a .dat file (dellimiter = space)wi...Hi Guru,<br />I have a .dat file (dellimiter = space)with thousand columns. I want to <br />1. duplicate column 1<br />2.insert 4 columns of 0 after 3rd column (after duplicating column 1) <br />Can you help me? I could not figure out from your examples :(<br />Thank you very much!Anonymoushttps://www.blogger.com/profile/07931415047379482670noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-36452037946323764992016-01-14T11:30:55.460+05:302016-01-14T11:30:55.460+05:30Thank you very much Guru Prasad. It's work now...Thank you very much Guru Prasad. It's work now. Again,<br />How about from file.csv<br />cat file.csv<br />server1;!!redhat!!;"192.168.1.10,192.168.1.11";up<br />server2;!!centos!!;"192.168.1.20,192.168.1.21";down<br /><br />in column 2 which is !!redhat!! and !!centos!! required remove !!?<br /><br />Example:<br />server1;redhat;"192.168.1.10,192.168.1.11";up<br />server2;centos;"192.168.1.20,192.168.1.21";down<br /><br />Really appreciate your help.Anonymoushttps://www.blogger.com/profile/17180434354463137601noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-65675820896077834092016-01-06T22:46:14.518+05:302016-01-06T22:46:14.518+05:30awk -F";" '{$2=e$2e;$3=q$3q;}1' ...awk -F";" '{$2=e$2e;$3=q$3q;}1' q='"' e='!!' OFS=";" file.csvGuru Prasadhttps://www.blogger.com/profile/07898245472802947101noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-20964588897697324612016-01-06T10:20:06.584+05:302016-01-06T10:20:06.584+05:30Thanks Guru Prasad, I forgot to inform you that co...Thanks Guru Prasad, I forgot to inform you that column 2 also need to add with symbol !!. For example !!redhat!! and !!centos!!.<br /><br />Result will be like this:<br />cat file.csv<br />server1;!!redhat!!;"192.168.1.10,192.168.1.11";up<br />server2;!!centos!!;"192.168.1.20,192.168.1.21";down<br /><br />Anonymoushttps://www.blogger.com/profile/17180434354463137601noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-17876864592115030122016-01-05T11:27:55.402+05:302016-01-05T11:27:55.402+05:30awk -F";" '{$3=q$3q;}1' q='&...awk -F";" '{$3=q$3q;}1' q='"' OFS=";" file.csvGuru Prasadhttps://www.blogger.com/profile/07898245472802947101noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-11793382084258597922016-01-02T04:20:52.120+05:302016-01-02T04:20:52.120+05:30Hi Guru,
I have a csv file and need to include in...Hi Guru,<br /><br />I have a csv file and need to include in column 3 with " " example "192.168.1.10,192.168.1.11"<br /><br />cat file.csv<br />server1;redhat;192.168.1.10,192.168.1.11;up<br />server2;centos;192.168.1.20,192.168.1.21;down<br /><br />and I want the output write in file like this:<br />server1;redhat;"192.168.1.10,192.168.1.11";up<br />server2;centos;"192.168.1.20,192.168.1.21";down<br /><br />Really appreciate your help.Anonymoushttps://www.blogger.com/profile/17180434354463137601noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-39538007565981526342015-11-28T07:35:49.255+05:302015-11-28T07:35:49.255+05:30HI Guru,
Thanks for the wonderful explanation. My...HI Guru,<br /><br />Thanks for the wonderful explanation. My situation is a bit tricky.. I have a CSV file in which one field has http responses. So it includes comma, double quotes and many other special chacters, it isalso is not limited to one line. The issue im facing is how do i escape the special characters are new line and print the exact same thing as CSV.<br /><br />Refer to following for detailed question : http://stackoverflow.com/questions/33965840/how-to-escape-comma-double-quotes-and-new-line-in-csv-using-shell-bash-script?noredirect=1#comment55689411_33965840Mannyhttps://www.blogger.com/profile/02433883106512166771noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-82721112104364188322015-09-29T23:02:46.975+05:302015-09-29T23:02:46.975+05:30You can do this using cut command itself:
cut -d,...You can do this using cut command itself:<br /><br />cut -d, -f23-31,42 fileGuru Prasadhttps://www.blogger.com/profile/07898245472802947101noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-71620108574617166022015-09-29T20:26:53.286+05:302015-09-29T20:26:53.286+05:30Hi...
I have a csv file with total 42 columns
an...Hi... <br />I have a csv file with total 42 columns <br />and from that file i need to make a new csv file that contains only column 23-31 and 42th (only 10 columns) for every lines i have...<br /><br />Can you help me how to generate it? thank...<br />jphttps://www.blogger.com/profile/14033891258238407120noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-60552346531710646272015-07-07T22:30:33.750+05:302015-07-07T22:30:33.750+05:30awk -F, '{$5=round(($2+$3+$4)/3);}1' OFS=,...awk -F, '{$5=round(($2+$3+$4)/3);}1' OFS=, fileGuru Prasadhttps://www.blogger.com/profile/07898245472802947101noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-22845445911493441332015-07-07T22:28:15.447+05:302015-07-07T22:28:15.447+05:30echo "1|2|3|4" | awk -F"|" ...echo "1|2|3|4" | awk -F"|" '{$1="a|b";}1' OFS="|"Guru Prasadhttps://www.blogger.com/profile/07898245472802947101noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-6348350883077731582015-07-07T18:29:14.344+05:302015-07-07T18:29:14.344+05:30I have a day file with below data 1|2|3|4 I need t...I have a day file with below data 1|2|3|4 I need to remove the first column and two new columns to it in the start like a|b|2|3|4Anonymoushttps://www.blogger.com/profile/01326770708638689231noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-18655278061733802102015-07-07T18:27:45.636+05:302015-07-07T18:27:45.636+05:30Hi can all these commands be applied to pipe separ...Hi can all these commands be applied to pipe separated dat files also?Anonymoushttps://www.blogger.com/profile/01326770708638689231noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-51864494600996399572015-06-24T11:53:19.932+05:302015-06-24T11:53:19.932+05:30I have a file named product_sale With Below format...I have a file named product_sale With Below format <br /><br />product_name,jan,feb,march<br />cakes,20,40,60<br />pizzas,10,4,6<br />cokes,10,20,40<br /><br />I want to add extra column which is average value of columns from jan to march. Like below format.<br /><br />product_name,jan,feb,march,Average_sale<br />cakes,20,40,60,40<br />pizzas,11,4,6,7<br />cokes,10,10,40,20<br /><br />How to generate this ?Anonymoushttps://www.blogger.com/profile/04997051214172720850noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-74759117402088934472015-04-10T10:03:26.604+05:302015-04-10T10:03:26.604+05:30Thanks for your comment. If the file were to have ...Thanks for your comment. If the file were to have 100 columns, you cant do this, meaning it is not a generic solution.Guru Prasadhttps://www.blogger.com/profile/07898245472802947101noreply@blogger.comtag:blogger.com,1999:blog-1255024703457423340.post-69843277772605891152015-04-10T07:43:22.390+05:302015-04-10T07:43:22.390+05:30Hi, here are some simpler solutions to your exampl...Hi, here are some simpler solutions to your examples<br /><br />9. Remove/Delete the 2nd column from the CSV file: <br />awk -F, '{print $1 "," $3}' file<br /><br />10. Join 3rd column with 2nd colmn using ':' and remove the 3rd column: <br />awk -F, '{print $1 "," $2 ":" $3}' file<br /><br />Have a nice day.<br />champosthttps://www.blogger.com/profile/12864467705556675373noreply@blogger.com