CODEWITHSUNDEEP
bashcsvawk
user2525881
user2525881

Reputation: 131

Replacing a column in CSV file with another in bash

I have a csv file with a number of columns. I am trying to replace the second column with the second to last column from the same file. For example, if I have a file, sample.csv

1,2,3,4,5,6
a,b,c,d,e,f
g,h,i,j,k,l

I want to output:

1,5,3,4,5,6
a,e,c,d,e,f
g,k,i,j,k,l

Can anyone help me with this task? Also note that I will be discarding the last two columns afterwards with the cut function so I am open to separating the csv file to begin with so that I can replace the column in one csv file with another column from another csv file. Whichever is easier to implement. Thanks in advance for any help.

Upvotes: 3

Views: 7532

Answers (3)

anubhava
anubhava

Reputation: 785156

How about this simpler awk:

awk 'BEGIN{FS=OFS=","} {$2=$(NF-1)}'1 sample.csv

EDIT: Noticed that you also want to discard last 2 columns. Use this awk one-liner:

awk 'BEGIN{FS=OFS=","} {$2=$(NF-1); NF=NF-2}'1 sample.csv

Upvotes: 11

gniourf_gniourf
gniourf_gniourf

Reputation: 46823

Pure solution, using IFS in a funny way:

# Set globally the IFS, you'll see it's funny
IFS=,
while read -ra a; do
    a[1]=${a[@]: -2:1}
    echo "${a[*]}"
done < file.csv

Setting globally the IFS variable is used twice: once in the read statement so that each field is split according to a coma and in the line echo "${a[*]}" where "${a[*]}" will expand to the fields of the array a separated by IFS... which is a coma!

Another special thing: you mentionned the second to last field, and that's exactly what ${a[@]: -2:1} will expand to (mind the space between : and -2), so that you don't have to count your number of fields.

Caveat. csv files need a special csv parser that is difficult to implement. This answer (and I guess all the other answers that will not use a genuine csv parser) might break if a field contains a coma, e.g.,

    1,2,3,4,"a field, with a coma",5

If you want to discard the last two columns, don't use cut, but this instead:

IFS=,
while read -ra a; do
    ((${#a[@]}<2)) || continue # skip if array has less than two fields
    a[1]=${a[@]: -2:1}
    echo "${a[*]::${#a[@]}-2}"
done < file.csv

Upvotes: 0

iruvar
iruvar

Reputation: 23364

In bash

while IFS=, read -r -a arr; do
  arr[1]="${arr[4]}";  
  printf -v output "%s," "${arr[@]}"; 
  printf "%s\n" "${output%,}"; 
done < sample.csv

Upvotes: 0

Related Questions