S_Ananth S_Ananth - 2 months ago 7
Linux Question

Add/Sub/Mul/Div a constant to a column in a csv file in linux shell scripting

I am trying to modify the contents of a particular column in a csv file by dividing a constant.

For Ex: If the contents are

1000,abc,0,1
2000,cde,2,3 and so on..


I would like to change it to

1,abc,0,1
2,cde,2,3


I went through all the previous solutions in this blog, and i tried this

awk -F\; '{$1=($1/1000)}1' file.csv > tmp.csv && mv tmp.csv file.csv


The above command opens up file.csv , performs $1/1000 and save it to a temporary file and then overwrites to the original file.

The problem i see is, in the final file.csv, The contents displayed are as follows

1
2
3
4 and so on ..


It doesn't copy all the other columns except column 1.

How can i fix this ?

Answer

Because your file is comma-separated, you need to specify a comma as the field separator on both input and output:

$ awk -F, '{$1=($1/1000)}1' OFS=, file.csv
1,abc,0,1
2,cde,2,3 

-F, tells awk to use a comma as the field separator on input.

OFS=, tells awk to use a comma as the field separator on output.

Changing the file in-place

With a modern GNU awk:

awk -i inplace -F, '{$1=($1/1000)}1' OFS=, file.csv

With BSD/OSX or other non-GNU awk:

awk -F, '{$1=($1/1000)}1' OFS=, file.csv >tmp && mv tmp file.csv

Alternate style

Some stylists prefer `OFS to be set before the code:

awk -F, -v OFS=, '{$1=($1/1000)}1' file.csv
Comments