Wednesday, October 27, 2021

[SOLVED] I need to convert all the values to numeric except header. Can I do this in shell script?

Issue

I am new to shell scripting. I need to convert the values in string to integer in order to calculate sum of the below mentioned individual columns from csv file.

Column looks like: 'likes','dislikes' "61","110" 120, 70

I have tried using awk -F',' '{sum+=$1} END {print sum}' But this does not give me the desired result, as some values are in string. How can i convert those values to integers prior to adding them?


Solution

if you are certain that quotes " are only used in conjunction with numbers, a simple

more file.csv | tr '"' ' ' > fixed.csv

should do the job

If the first line is header, you can do something like:

head -1 file.csv > fixed.csv
tail -n +2 file.csv | tr '"' ' ' >> fixed.csv

If it fulfills your needs, a scripted version is:

#!/bin/bash

if [[ $# != 2 ]]
then
    printf "Usage:\nscript.sh input_file.csv output_file.csv\n"
    exit -1
fi

set -o noclobber

head -1 "$1" > "$2"
tail -n +2 "$1" | tr '"' ' ' >> "$2"

set -o noclobber prevents accidental overwritings: NoClobber.



Answered By - Picaud Vincent
at