Updating a line in a large csv file, with sed/awk?

Shell Programming and Scripting
1

I have an extremely large csv file that I need to search the second field, and upon matches update the last field...

I can pull the line with awk.. but apparently you cant use awk to directly update the file? So im curious if I can use sed to do this... The good news is the field I want to update is the last field, so I can just do a right to left search for "," and update to the right.

anyways, the details.

...
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field14 <This is the field I need to change
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13 
field1,X.X.X.X,feild3,...,field13
...

awk line to pull the info I want...

awk -v ip=$ip 'NF > 13 {if ($2 == ip && $14 =="*") print $0}' file.csv

now, besides turning the above into 3-4 long bash variables... Is there any easy way to change field $14 in the above line? :confused:

2

If how to directly apply to the file is your only question, then the answer is -i switch of sed can do it for you.

3

I know I can do that; just wondering if there is an easier way without creating two long variables with the awk command to use with sed.

4

awk can emulate sed -i by making a temporary copy of original file. I believe sed is doing exactly the same with the -i switch.

$ cat file
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,Y.Y.Y.Y,feild3,,,,,,,,,,,* 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13 
field1,X.X.X.X,feild3,,,,,,,,,,field13

#!/bin/bash

# make temporary copy
cp file /tmp/file

# awk part
IP="Y.Y.Y.Y"
awk -v ip=$IP -F, -v OFS="," awk'$2~/^Y.Y.Y.Y$/ && $14~/*/ {$14="new value f14"} {print}' /tmp/file > file

# if necessary remove temporary file
rm /tmp/file
5

Thanks for all the help, I was hoping there was an easier way to write out all this out... I guess thats a limitation of a bash script doing this sort of job. I went with 2 variables and a sed line to make the change i need.

...
  old_f=`awk -v host=$1 -F, 'NF > 13 {if ($4 == host && $14 == "*") print $0}' $MYLIST`
  new_f=`awk -v host=$1 -F, 'NF > 13 {if ($4 == host && $14 == "*") print $1","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","UPDATED}' $MYLIST` 
  if ! sed -i s/${old_f}/${new_f}/ $MYLIST
  then
    echo "BLAH" >> ${DATE}.log
  fi
...
6

Simply try:

# make temporary copy
cp $MYLIST /tmp/file

# awk part
IP="^Y.Y.Y.Y$"
awk -v ip=$IP -F, -v OFS="," '$2~ip && $14~/*/  {$14="new value f14"} {print}' /tmp/file > $MYLIST