Bash: Parse CSV and edit cell values

Question

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.

Learn to use shellcheck.net before you post your code here ;-) . When you use shellcheck, you need to include a proper "she-bang" line as the first line, usually #!/bin/bash. Good luck. — shellter
– shellter, Commented Jul 10, 2017 at 3:23

Sundeep · Accepted Answer · 2017-07-10 05:27:45Z

3

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/[^,]*/\L\u&/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/[^,]*/\L\u&/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

answered Jul 10, 2017 at 5:27

Sundeep

23.9k2 gold badges35 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Meghna Sathyanarayan Over a year ago

Hi Sundeep, It worked on Gedit :). Thanks. The issue was with MobaXterm editor. Can I knw how \L\u indicates capitalize first letter in a word?

Sundeep Over a year ago

\L will lowercase all letters.... \u will uppercase only one letter... if your input has only words like mon, tue etc, you can skip using \L

Meghna Sathyanarayan Over a year ago

Thanks, I got another doubt. If you want me to open another thread I ll do so. I got this case sometimes where in 100, West Bengal, Up, Thu, , , ,. 101, , , Fri, 6.00, 8.00, P3. I ve this expression s/,[[:blank:]]*,/,,/. This works fine. But when more than 1 blank space exists between delimiters it does not work

Meghna Sathyanarayan Over a year ago

For ex 100, West Bengal, , , , , yields 100, West Bengal,, , , - the space exists between last two delimiters. [Note space needs to be preserved between words like West Bengal]

Sundeep Over a year ago

not sure about your doubt.. are you trying to delete all blanks in empty fields? if so, you'll need something like echo '100, West Bengal, , , , ,' | sed ':a s/,[[:blank:]]\+,/,,/; ta'

David C. Rankin · Accepted Answer · 2017-07-10 05:40:21Z

1

Recall in bash, you have a parameter expansion that will convert all characters in a variable (e.g. $line) to upper-case. The form of the expansion is ${line^^} (note the '^^'). To read and convert all characters in lines after the first to upper-case, you can do:

declare -i c=0
while read -r line; do 
    if [ "$c" -gt '0' ]; then 
        echo "${line^^}"          ## output line converted to upper
    else
        echo "$line"
    fi
    ((c++))
done < file.txt

Example Output

$ declare -i c=0; while read -r line; do if [ "$c" -gt '0' ]; then \ 
echo "${line^^}"; else echo "$line"; fi; ((c++)); done < file.txt
ID Location Way Day DayTime NightTime StandNo
1  ABC      UP  MON 6.00     18.00    6

answered Jul 10, 2017 at 5:40

David C. Rankin

85.1k6 gold badges67 silver badges95 bronze badges

Comments

mauro · Accepted Answer · 2017-07-10 05:57:52Z

0

I am assuming you have a headerless CSV file in input:

$ cat file.csv 
1,abc,Up,mon,6.00,18.00,6

I'm also assuming you want a CSV file in output.

If all these assumptions are correct, then:

$ awk -F, -v OFS=, '{$2=toupper($2); $4=toupper(substr($4,1,1)) substr($4,2); print}' file.csv
1,ABC,Up,Mon,6.00,18.00,6

EDIT. If you want to preserve your header line...

awk -F, -v OFS=, '{if(NR>1){$2=toupper($2); $4=toupper(substr($4,1,1)) substr($4,2)} print}' file.csv
ID,Location,Way,Day,DayTime,NightTime,StandNo
1,ABC,Up,Mon,6.00,18.00,6

edited Jul 10, 2017 at 5:57

answered Jul 10, 2017 at 3:42

mauro

6,0262 gold badges31 silver badges27 bronze badges

3 Comments

Meghna Sathyanarayan Over a year ago

Header needs to be copied. Can I knw where exactly this line needs to be inserted. Currently it gives me an error saying "multi call binary' Usage:awk [options] [awk_program] [file]... -v var=val set variable

mauro Over a year ago

@MeghnaSathyanarayan: don't understand what you mean with inserted. The command line here above has to be executed (not inserted).

Meghna Sathyanarayan Over a year ago

Apologies Mauro, I reframe my question. I did execute the command and found the error. I thought probably it should be added somewhere in the existing script. I am quite new to bash and hence I may have jumbled up.

Collectives™ on Stack Overflow

Bash: Parse CSV and edit cell values

3 Answers 3

5 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related