Format a file in Unix/Linux ?

Question

I have a file containing country, catalog number, year, description and price

Kenya 563-45 1995 Heron Plover Thrush Gonolek Apalis $6.60
Surinam 632-96 1982 Butterfliers $7.50
Seychelles 831-34 2002 WWF Frogs set of 4 $1.40
Togo 1722-25 2010 Cheetah, Zebra, Antelope $5.70

File isn't delimited by a "tab" or ":" anything. There is only spaces between them. can you please tell me how can I format this file(using awk ?) and how can I find the total price from this.

Answer 1

With command line perl:

$ cat /your/file | perl -e '$sum=0; for(<STDIN>) { $sum += $1 if(/\$([\d\.]+)/); }; print "$sum\n"'
21.2

and awk (assumes you have dollars at the end of each line):

$ cat /your/file | awk '{s+=substr($NF,2)} END{ print s}'
21.2

Also, in response to the comment. If you want to reformat on the command line:

$ cat /your/file | perl -e 'for(<STDIN>){@a=split /\s+/; $p=pop @a; \
  $line=join "|", ($a[0],$a[1],$a[2], (join" ",@a[3..$#a]) ,$p); print "$line\n"}'

Kenya|563-45|1995|Heron Plover Thrush Gonolek Apalis|$6.60
Surinam|632-96|1982|Butterfliers|$7.50
Seychelles|831-34|2002|WWF Frogs set of 4|$1.40
Togo|1722-25|2010|Cheetah, Zebra, Antelope|$5.70

If you want to do this properly, I'd do it not on the cmd line, but write a proper program to parse it.

Answer 2

I thought first 3 and last column is fixed meaning but middle columns are not fixed. So middle columns are kept at last with space between and fixed columns are seperated by tab so that you can start to edit it with some spreadsheet program:

awk '{ printf("%s\t%s\t%s\t%s\t", $1, $2, $3, $NF); for(i=4; i<NF; i++){ printf("%s ", $i); } printf("\n") }' < yourlist.txt