help with data extraction script

Hello all,
Iam newbie here and to unix programming. I have the following text file.

A:Woshington,B:London,C:Paris,D:Manchester,C:Lisbon,E:Cape town.

Now I would like extract this and store in database. here is the script I have tried but it did work.

CITY1:`echo "$text" | grep "A:" |awk '{print $0 }'`
CITY2 :`echo "$text" | grep "B:" |awk '{print $0 }'`
CITY3:`echo "$text" | grep "C:" |awk '{print $0 }'`
CITY4:`echo "$text" | grep "D:" |awk '{print $0 }'`
CITY5:`echo "$text" | grep "E:" |awk '{print $0 }'`
CITY6:`echo "$text" | grep "F:" |awk '{print $0 }'`

Thanks in advance for your help.
mam

How you need the output?Are you looking for the entire block seperated with colon or just the City name ?

DILEEP410,
Thanks for your reply,
For the output, I need only city names and then I will use them to store in database.
like CITY1=Washington,CITY2= ...,

Thank you again for your help
mam

Sorry I think the code should read like this

CITY1=`echo "$text" | grep "A:" |awk '{print $0 }'`
CITY2 =`echo "$text" | grep "B:" |awk '{print $0 }'`
CITY3=`echo "$text" | grep "C:" |awk '{print $0 }'`
CITY4=`echo "$text" | grep "D:" |awk '{print $0 }'`
CITY5=`echo "$text" | grep "E:" |awk '{print $0 }'`
CITY6=`echo "$text" | grep "F:" |awk '{print $0 }'`

Please, anyone with idea?.
I really need your support expert!
Thank you again

could some one tell me, how could i pass the wilcard notation given as input to variable $1 which acts as variable that matches files in the directory and the delete them.

GNU awk

awk 'BEGIN{FS=",*[a-zA-Z]:"}
{
  for ( i=2 ; i<=NF ; i++ ) {
    print "insert table values (\047" $i "\047)"
  }
}
' "file"

output:

# ./testnew.sh
insert table values ('Woshington')
insert table values ('London')
insert table values ('Paris')
insert table values ('Manchester')
insert table values ('Lisbon')
insert table values ('Cape town.')

text="A:Woshington,B:London,C:Paris,D:Manchester,C:Lisbon,E:Cape town"
CITY1=`echo "$text" |awk -F, '{ for(i=1;i<=NF;i++) if($i ~ "^A:") print substr($i,3);}' `
echo $CITY1

I think you shoud use arrays in this case
and I am also not sure if the file that contains the name of the cities contains only one line or more.

for example if it contains only onle line like

:~/st [164]> cat city_names
A:Delhi,B:Mumbai,C:Calcutta,D:Pune,E:Gurgaon

and you know the number of cities, you can do something like

let i=1
let flag=0

#while [ $flag -eq 0 ]
while [ $i -le 5 ]
do
let p=$i+1
CITY[$i]=`head -1 city_names | cut -d ':' -f$p | cut -d ',' -f1`
let i=$i+1
done>error 2>&1

let j=1
while [ $j -le 5 ]
do
echo ${CITY[$j]}
let j=$j+1
done

which will give you output like

:~/st [166]> ./scr
Delhi
Mumbai
Calcutta
Pune
Gurgaon

where
CITY[1] : Delhi
CITY[2] : Mumbai

etc

Hello,
ranjithpr,snowline and ghostdog74,
Thank you for your reply.I will try to implement soon.
Also,it looks like I was not clear ,here is the really scenario:

text file
 A:Washington,B:Paris,D:London,C:Cape Town,E:Copenhagen,F:Mumbai
 The problem is on this part here:

 CITY1:`echo "$text" | grep "A:" |awk '{print $0 }'`
 CITY2:`echo "$text" | grep "B:" |awk '{print $0 }'`
 CITY3:`echo "$text" | grep "C:" |awk '{print $0 }'`
 CITY4:`echo "$text" | grep "D:" |awk '{print $0 }'`
 CITY5:`echo "$text" | grep "E:" |awk '{print $0 }'`
 CITY6:`echo "$text" | grep "F:" |awk '{print $0 }'`
 
  if [ "$SQL_PASSWORD" != "" ]; then
     SQL_ARGS="-p$SQL_PASSWORD";
     else
     SQL_ARGS="";
     
   fi

  SQL_ARGS="-h $SQL_HOST -u $SQL_USER $SQL_ARGS -D $SQL_DATABASE -s -e"
  
  mysql $SQL_ARGS  " insert into $SQL_TABLE(city1,city2,city3,city4,city5,city6) values
(\"CITY1\",\"$CITY2\",\"$CITY3\",\"$CITY4\",\"$CITY5\",\"$CITY6");";

Thank you again.

Hi,

Try follow one.

cat file | tr -s "," "\n" | nawk 'BEGIN{OFS=FS=":"}
{
$1=sprintf("city%d",NR)
print
}'

I came across a data extraction software that works well on windows, i got it from mountonetech.com, see if they have similar data extraction software for unix environment.