How to replace word in CSV
Hi,
Can someone please help? I am using Sun OS unix:
1. read a CSV file, port.csv
2. find out all lines which has word "Documentation"..as I only need to change on those lines which has word, "Documentation"
3. then for each found line, I have to read the 9th element, which always has the pattern of CCC-CCC-### e.g. HKH-CSA-123, HKH-CSA-456
4. then I have to get the last 3 digit and try to find the replace ID in another file config.txt e.g. the replace id for 123 is 6.
5. The pattern in config file is
123,6
456,7
6. If I can find the replace ID, then replace column 11 in the CSV file with the replace ID. Currently, it is lucky that column 11 always has the
value of 5
7. Apply the changes to a new file(port2.csv) but not orignal file port file (port.csv)
before in port.csv:
hello, world, my, name, is, aa, bb, cc, HKH-CSA-123, 10, 5, 2008
hello, mom, his, name, is, dd, ee, ff, HKH-CSA-999, 23, 5,2008
hello, dad, my, name, is, aa, bb, cc, HKH-CSA-456, 24, 5,2008
After changes, create a new file port2.csv with following content:
hello, world, my, name, is, aa, bb, cc, HKH-CSA-123, 10, 6, 2008
hello, mom, his, name, is, dd, ee, ff, HKH-CSA-999, 23, 5,2008
hello, dad, my, name, is, aa, bb, cc, HKH-CSA-456, 24, 7,2008
given there are only 2 entries in config file
123,6
456,7
Below is my trial version of shell script. I need help on completing this task:
#!/usr/bin/ksh
echo "*********** Program starts at `date +%Y%m%d\ %H:%M:%S` **************"
FILENAME=port.csv
CONFIG_TXT=cfg.txt
AGREEID=
RESULT=`grep "Documentation" $FILENAME`
for LINE in $RESULT
AGREEID=`cat $LINE | awk -F"," '{print $9}'`
# how get the last 3 char of agree id b?
echo "AGREEID: $AGREEID"
REPLACE_ID=`cat "$CONFIG_TXT" | grep "$AGREEID" | awk -F"," '{print $2}'` # try to retrieve the replace ID given the agree ID in config file
if [[ -z REPLACE_ID && REPLACE_ID=""]] # if agree ID not found in config file, so no replacement can be performed
then
:
else
#dun know how to replace 11th element with REPLACE ID
sed 's/"5"/"$REPLACE_ID"/g' $LINE
#how to apply the changed file to a new file?
fi
exit 0