I have a csv that has data looking something like this,
123,Jeff,NY
456,Ross,LA
789,John,OH
I have to write a script that manipulates only first column and change the numbers to desired characters like show below,
ABC,Jeff,NY
DEF,Ross,LA
GHI,John,OH
Any help is appreciated. Thank you.
If you want to change numbers from 1 to 9 by letters A to I, use tr command:
tr 1-9 A-I <file
Since only the first column of your input file contains numbers, only that column will be changed.
awk '{printf "%s,", $1 | c; close c; print $2, $3}' c="tr 1-9 A-K" OFS=, FS=, inputperl -F, -ane '$,=","; $F[0] =~ s/([0-9])/chr(ord("A") + $1)/eg; print @F' input
A simple bash solution
#!/bin/bash
F="$1"
[ -z "$F" ] && {
echo "Usage: $0 csv-file"
exit 1
}
chars=( {A..Z} )
while IFS= read -r line; do
echo "${chars[($(echo $line | cut -b1)-1)]}${chars[($(echo $line | cut -b2)-1)]}${chars[($(echo $line | cut -b3)-1)]},"$(echo $line | cut -f2- -d,)
done < "$F"
Explanation:
Get the file as first parameter, exit if none is given
F="$1"
[ -z "$F" ] && {
echo "Usage: $0 csv-file"
exit 1
}
Declare the alphabet array
chars=( {A..Z} )
Loop the file and for each line use the 3 digits from the csv to get the alphabet array index
while IFS= read -r line; do
echo "${chars[($(echo $line | cut -b1)-1)]}${chars[($(echo $line | cut -b2)-1)]}${chars[($(echo $line | cut -b3)-1)]},"$(echo $line | cut -f2- -d,)
done < "$F"
If there are numbers on other columns and you want to replace only on the first column, you can try to create a new transformed column (by extracting and replacing using tr and cut commands, and then joining with paste commands) :
paste -d',' <(cat abc.txt | cut -d"," -f1 | tr 1-9 A-I) abc.txt
ABC,123,Jeff,NY
,
DEF,456,Ross,LA
,
GHI,789,John,OH
Now you can the remove the blank lines that have a leading comma
paste -d',' <(cat abc.txt | cut -d"," -f1 | tr 1-9 A-I) abc.txt|grep -v "^,"
ABC,123,Jeff,NY
DEF,456,Ross,LA
GHI,789,John,OH
Finally, you can select only the columns you need using the cut command :
paste -d',' <(cat abc.txt | cut -d"," -f1 | tr 1-9 A-I) abc.txt|grep -v "^,"| cut -d"," -f2,3,4
123,Jeff,NY
456,Ross,LA
789,John,OH
