Skip to content
Advertisement

Bash: Parse CSV and edit cell values

I am new to bash scripting I have the following CSV

Input

ID Location Way Day DayTime NightTime StandNo
1  abc      Up  mon 6.00     18.00    6

Expected Output

ID Location Way Day DayTime NightTime StandNo
1  ABC      UP  Mon 6.00     18.00    6

I need to check Location and Way and convert them to UpperCase – ABC, UP Day needs to be mon – Mon I need to do this for entire CSV. I need to correct the value and write all the fields on to CSV or edit the current cell and save CSV My script is as follows

file = "Myfile.csv"
while IFS="," read line
do
output=`echo $line | cut -d "," -f2`
echo $output
for i in $output
do
if [ -z $(echo $I | sed -e "s/[a-z]//g") ]
then 
echo $I | tr "[:lower:]" "[:upper:]" >> ".tempfile.CSV"
fi
done
done <$file

`1. Currently this writes only the corrected value and not entire line along with corrected value. [Unsure how I can loop thru cell values in every row correct the ones which needs correction and then copy the entire row]

Any help would be useful.

Advertisement

Answer

See Why is using a shell loop to process text considered bad practice?

As question is tagged linux, assuming GNU sed is available. And also that the input is actually csv, not space/tab separated

$ cat ip.csv 
ID,Location,Way,Day,DayTime,NightTime,StandNo
1,abc,Up,mon,6.00,18.00,6
2,xyz,down,TUE,2.32,5.23,4

$ sed '2,$ {s/[^,]*/Lu&/4; s/[^,]*/U&/3; s/[^,]*/U&/2}' ip.csv 
ID,Location,Way,Day,DayTime,NightTime,StandNo
1,ABC,UP,Mon,6.00,18.00,6
2,XYZ,DOWN,Tue,2.32,5.23,4
  • 2,$ to process input from 2nd line to end of file
  • s/[^,]*/Lu&/4 capitalize only first letter of 4th field
  • s/[^,]*/U&/3 capitalize all letters in 3rd field
  • s/[^,]*/U&/2 capitalize all letters in 2nd field

If the fields themselves can contain , within double quotes and so on, use perl, python, etc which has csv modules

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement