CODEWITHSUNDEEP
bashawk
HattrickNZ
HattrickNZ

Reputation: 4653

controlling where to insert a column in a file using awk

this is my file: 

$ cat -v test1
"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Device_Model","Product_Description"
"2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I","$39 Plan"
"2015-10-06","592","620","0","Apple Inc","Apple iPhone 6 (A1586)","PREPAY  STD - TRIAL - #16"
"2015-10-06","007","290","0","Apple Inc","Apple iPhone 6 (A1586)","PREPAY PLUS - $0 -"
"2015-10-06","592","050","48836832","Apple Inc","Apple iPhone 5S (A1530)","Talk and Text Connect Flexi Plan"
"2015-10-06","409","720","113755347","Samsung Korea","Samsung SM-G360G","$29 CARRYOVER PLAN"
"2015-10-06","742","620","19840943","Apple Inc","Apple iPhone S (A1530)","PREPAY STD - $0 - #2"
"2015-10-06","387","180","0","HUAWEI Technologies Co Ltd","HUAWEI HUAWEI G526-L11","PREPAY STD - $1 - #4"
"2015-10-06","731","570","2258243","Samsung Korea","Samsung SM-N910U","Business Freedom"
"2015-10-06","556","910","13332272","Samsung Korea","Samsung GT-I9505","$49 Plan"

this command adds a column to the end
NR==1{$7=q"Data_Volume_MB"q} add a column name at the end on line1.
NR>1{$7=$4; gsub(/"/,"",$7); $7= q $7/(1024*1024)q}1 adds a column at the on line 2 and greater 

awk -F, -v OFS=, -v q='"' 'NR==1{$7=q"Data_Volume_MB"q} NR>1{$7=$4; gsub(/"/,"",$7); $7= q $7/(1024*1024)q}1' test1
"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Device_Model","Data_Volume_MB"
"2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I","0.131383"
"2015-10-06","592","620","0","Apple Inc","Apple iPhone 6 (A1586)","0"
"2015-10-06","007","290","0","Apple Inc","Apple iPhone 6 (A1586)","0"
"2015-10-06","592","050","48836832","Apple Inc","Apple iPhone 5S (A1530)","46.5744"
"2015-10-06","409","720","113755347","Samsung Korea","Samsung SM-G360G","108.486"
"2015-10-06","742","620","19840943","Apple Inc","Apple iPhone S (A1530)","18.9218"
"2015-10-06","387","180","0","HUAWEI Technologies Co Ltd","HUAWEI HUAWEI G526-L11","0"
"2015-10-06","731","570","2258243","Samsung Korea","Samsung SM-N910U","2.15363"
"2015-10-06","556","910","13332272","Samsung Korea","Samsung GT-I9505","12.7146"

How do I control where to insert the column? Lets say i want to insert it before the 2nd last column? NR==1{$(NF-1)=q"Data_Volume_MB"q FS $(NF-1);} this does it for line 1 but I cannot figure out how to do it for lines 2 and greater. Can anyone advise how I can do it for line2 and greater NR>1{$7=$4; gsub(/"/,"",$7); $7= q $7/(1024*1024)q}1

$ awk -F, -v OFS=, -v q='"' 'NR==1{$(NF-1)=q"Data_Volume_MB"q FS $(NF-1);} NR>1{$7=$4; gsub(/"/,"",$7); $7= q $7/(1024*1024)q}1' test1
"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Data_Volume_MB","Device_Model","Product_Description"
"2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I","0.131383"
"2015-10-06","592","620","0","Apple Inc","Apple iPhone 6 (A1586)","0"
"2015-10-06","007","290","0","Apple Inc","Apple iPhone 6 (A1586)","0"
"2015-10-06","592","050","48836832","Apple Inc","Apple iPhone 5S (A1530)","46.5744"
"2015-10-06","409","720","113755347","Samsung Korea","Samsung SM-G360G","108.486"
"2015-10-06","742","620","19840943","Apple Inc","Apple iPhone S (A1530)","18.9218"
"2015-10-06","387","180","0","HUAWEI Technologies Co Ltd","HUAWEI HUAWEI G526-L11","0"
"2015-10-06","731","570","2258243","Samsung Korea","Samsung SM-N910U","2.15363"
"2015-10-06","556","910","13332272","Samsung Korea","Samsung GT-I9505","12.7146"

for line 2 and greater I have tried many attempts, but it is always slightly off. e.g.:

NR>1{$(NF-1)=$4; gsub(/"/,"",$(NF-1)); $(NF-1)= q $(NF-1)/(1024*1024)q FS $(NF-1);}1

EDIT1

this was my way which I was getting wrong

$ awk -F, -v OFS=, -v q='"' 'NR==1{$(NF-1)=q"Data_Volume_MB"q FS $(NF-1);} NR>1{$(NF-1)=$4; gsub(/"/,"",$(NF-1)); $(NF-1)= q $(NF-1)/(1024*1024)q FS $(NF-1);}1' test1 | head -n1 | tr ',' '\n'| cat -n
     1  "Rec_Open_Date"
     2  "MSISDN"
     3  "IMEI"
     4  "Data_Volume_Bytes"
     5  "Device_Manufacturer"
     6  "Data_Volume_MB"
     7  "Device_Model"
     8  "Product_Description"
$ awk -F, -v OFS=, -v q='"' 'NR==1{$(NF-1)=q"Data_Volume_MB"q FS $(NF-1);} NR>1{$(NF-1)=$4; gsub(/"/,"",$(NF-1)); $(NF-1)= q $(NF-1)/(1024*1024)q FS $(NF-1);}1' test1 | head -n2 | tail -n1 | tr ',' '\n'| cat -n
     1  "2015-10-06"
     2  "427"
     3  "060"
     4  "137765"
     5  "Samsung Korea"
     6  "0.131383"
     7  137765
     8  "$39 Plan"

Answers below from @Etan

Answer 1 --

$ awk -F, -v OFS=, -v q='"' 'NR==1{$(NF-1)=q"Data_Volume_MB"q FS $(NF-1);} NR>1{n=$4; gsub(/"/,"",n); $(NF-1)= q n/(1024*1024)q FS $(NF-1);}1' test1 | head -n1 | tr ',' '\n' | cat -n
     1  "Rec_Open_Date"
     2  "MSISDN"
     3  "IMEI"
     4  "Data_Volume_Bytes"
     5  "Device_Manufacturer"
     6  "Data_Volume_MB"
     7  "Device_Model"
     8  "Product_Description"
$ awk -F, -v OFS=, -v q='"' 'NR==1{$(NF-1)=q"Data_Volume_MB"q FS $(NF-1);} NR>1{n=$4; gsub(/"/,"",n); $(NF-1)= q n/(1024*1024)q FS $(NF-1);}1' test1 | head -n2 | tail -n1  | tr ',' '\n' | cat -n
     1  "2015-10-06"
     2  "427"
     3  "060"
     4  "137765"
     5  "Samsung Korea"
     6  "0.131383"
     7  "Samsung SM-G900I"
     8  "$39 Plan"
$

Answer 2 --

to insert it after the second last column(working from the second last column)

$ awk -F, -v OFS=, -v q='"' 'NR==1{$(NF-1)=$(NF-1) FS q"Data_Volume_MB"q} NR>1{n=$4; gsub(/"/,"",n); $(NF-1)= $(NF-1) FS q n/(1024*1024)q}1' test1 | head -n1 | tr ',' '\n' | cat -n
     1  "Rec_Open_Date"
     2  "MSISDN"
     3  "IMEI"
     4  "Data_Volume_Bytes"
     5  "Device_Manufacturer"
     6  "Device_Model"
     7  "Data_Volume_MB"
     8  "Product_Description"
$ awk -F, -v OFS=, -v q='"' 'NR==1{$(NF-1)=$(NF-1) FS q"Data_Volume_MB"q} NR>1{n=$4; gsub(/"/,"",n); $(NF-1)= $(NF-1) FS q n/(1024*1024)q}1' test1 | head -n2 | tail -n1 | tr ',' '\n' | cat -n
     1  "2015-10-06"
     2  "427"
     3  "060"
     4  "137765"
     5  "Samsung Korea"
     6  "Samsung SM-G900I"
     7  "0.131383"
     8  "$39 Plan"

Answer 3 --

to insert it after the second last column(working from the last column)

$ awk -F, -v OFS=, -v q='"' 'NR==1{$NF=q"Data_Volume_MB"q FS $NF} NR>1{n=$4; gsub(/"/,"",n); $NF= q n/(1024*1024)q FS $NF;}1' test1 | head -n1 | tr ',' '\n' | cat -n
     1  "Rec_Open_Date"
     2  "MSISDN"
     3  "IMEI"
     4  "Data_Volume_Bytes"
     5  "Device_Manufacturer"
     6  "Device_Model"
     7  "Data_Volume_MB"
     8  "Product_Description"
$ awk -F, -v OFS=, -v q='"' 'NR==1{$NF=q"Data_Volume_MB"q FS $NF} NR>1{n=$4; gsub(/"/,"",n); $NF= q n/(1024*1024)q FS $NF;}1' test1 | head -n2 | tail -n1 | tr ',' '\n' | cat -n
     1  "2015-10-06"
     2  "427"
     3  "060"
     4  "137765"
     5  "Samsung Korea"
     6  "Samsung SM-G900I"
     7  "0.131383"
     8  "$39 Plan"

Upvotes: 1

Views: 35

Answers (1)

Etan Reisner
Etan Reisner

Reputation: 80931

You replace the value at $(NF-1) as the first thing you do in the action. Then you try to use $(NF-1) as if it still had the original value in it.

That isn't going to work. You need to save the value to another variable, operate on that, and then set $(NF-1) to the new value.

NR>1{n=$4; gsub(/"/,"",n); $(NF-1)= q n/(1024*1024)q FS $(NF-1)}1

Also to use the new value as the second to last column you need to place it after the value in the second to last column (or operate on the last column and place it before it).

NR>1{n=$4; gsub(/"/,"",n); $(NF-1)= $(NF-1) FS q n/(1024*1024)q}1

or

NR>1{n=$4; gsub(/"/,"",n); $NF= q n/(1024*1024)q FS $NF;}1

Upvotes: 1

Related Questions