I am working on a large csv file with all coma separated entries. The entire document is composed of plain numbers, except for a few columns reflecting currencies that are formatted as text. For example, one such entry may look like "$12,345.67" and another one like "$1,234,567.89" (double quote symbols are also included as part of the entries). Here is an example of a full record (one single line) of data:


I would like to do some processing on this data file and therefore, I want to reformat such text fields to become plain numbers (i.e., getting rid of double quotes and comas). I think a regex pattern replacement with sed should do the job for me, but I am not sure what that regular expression looks like at all. Any idea would be much appreciated.

  • Can you give an example of an entire record?
    – bahamat
    Oct 10 '12 at 0:22
  • Just added one.
    – Ali
    Oct 10 '12 at 0:35

it is simple to express what should be done:

1 For each string that is enclosed between "$ and " delete all ,
2 delete the surrounding "$ and "

This are sed expressions. If you use it on the command linen you have to quote it appropriately. It is simpler to put it in a file, e.g. command.sed, and call it by

sed -f command.sed data.csv

This is the program that implements the these two steps:

t repeat

This document helped me to solve the problem.


Update: adapting the answer from Replace spaces only in between quotation marks to handle this problem.


echo '123,7,11,"$343,700.14","$34,928.63","$377,000.00","$15,421.92",19,2' |
awk -F\" '{OFS="\""; for (i = 2; i < NF; i += 2) gsub(/[$,]/,"",$i); gsub(/"/,""); print}'



The first gsub removes $s and ,s inside double quotes. The second remove the quotes themselves.

Leaving original answer below in case it helps.

If I understand you correctly a simple search and replace would do it

echo '"$1,234,567.89"' | sed 's/[$,"]//g'

Output 1234567.89.

What this does is that it substitute (s) characters matching ([]) $, , and " with nothing (empty between the last two /. The g flag makes it apply globally, replacing all instances (otherwise only the first instance in a line would be replaced).

How to apply this to the whole file depends on the format of the file. If there is nothing else in the file that has double quotes, dollar signs and comma, this should be good:

sed 's/[$,"]//g' /path/to/file
  • Thanks @phunehehe. This looks pretty close, except for the fact that it would also eliminate all other comas separating entries in the file. Am I right?
    – Ali
    Oct 10 '12 at 0:29
  • Right. My little research suggests that you need awk, but my awk is not good :) Here is a hint in the right direction stackoverflow.com/q/5499798/168034
    – phunehehe
    Oct 10 '12 at 1:08
  • -1 the script does not solve the problem
    – miracle173
    Oct 10 '12 at 1:51

How this currency value is displayed depends on your CSV viewer, why wouldn't you change the "view" e.g in Excel rather than changing this field?

YAnyway, you might have multiple fields in your csv field, so it would be safer to use a handler script like this:


use strict;
use warnings;
use Text::CSV;

my $csv = Text::CSV->new ( { binary => 1 } );
while ( my $row = $csv->getline(\*STDIN) ) {
    foreach my $item (@$row)
        $item =~ s/,//g if ($item =~ /^\$[0-9,.]+$/);
    $csv->print (\*STDOUT, $row);
    print "\n";

Usage: ./csv.pl < in.csv > ou.csv




  • Thanks @warl0ck. I am getting the following error: Can't locate Text/CSV.pm in @INC. Sounds like I have some perl library missing, but unfortunately do not have privilege to install any module on the machine I am currently running. Do you know of any way to bypass this error?
    – Ali
    Oct 10 '12 at 0:59
  • @Ali use cpan then, cpan install Text::CSV
    – daisy
    Oct 10 '12 at 1:12
  • sounds like I do not have permission to install additional libraries on this computer. I'll try to have it installed on my own machine later today and see if I can work it out. Thanks :)
    – Ali
    Oct 10 '12 at 3:25
  • 1
    @Ali cpan will install the module intto your user account's home folder
    – daisy
    Oct 10 '12 at 4:20

A slightly convoluted awk solution, based on CSV parsing with awk

$0=$0",";                                  # yes, cheating
while($0) {
  match($0,/ *"[^"]*" *,|[^,]*,/);
  sf=f=substr($0,RSTART,RLENGTH);          # save what matched in sf
  gsub(/^ *"?|"? *,$/,"",f);               # remove extra stuff
  if($0 ~ /,[^,]+,$/){
    printf "%s,",f;
    printf "%s",f;
  sub(sf,"");                              # "consume" what matched
printf "\n"

Saved the above as csv.awk and ran like so:

echo '123,7,11,"$343,700.14","$34,928.63","$377,000.00","$15,421.92",19,2'|awk -f csv.awk
  • Thanks @tink. I tried to run your script on the file, but it is getting too long to terminate. The command I ran was cat data.csv | awk -f csv.awk > updated.csv. I think something in the script (or the command I ran) is not optimized for this purpose. Using openoffice's calc, I was able to the same task in a few minutes on the same file. My impression is that awk can do much faster on this job than calc, hum?!
    – Ali
    Oct 10 '12 at 3:23
  • Hard to say what might be going on there. Is all of the data actually formed like the single line you pasted above? If there are deviations in delimiters, or embedded special characters that might be interpreted as part of a RegEx awk might get thrown off.
    – tink
    Oct 10 '12 at 3:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.