I am trying to use sed to change the format of decimal numbers in a large CSV file before importing it into a SQLite database. They all have two decimal places, may be negative use comma as a decimal separator and are therefore escaped with double quotes. I was trying the following:

sed 's/"(-?)([:digit:]+),([:digit:]{2})"/$1$2.$3/g' input.csv > output.csv

The regex seems to work on a text editor on a sample of the file, but when running it through sed, there are no changes to the original file. What am I doing wrong?

  • 1
    Try adding -r flag to sed. – Amos Shapira Mar 15 '14 at 8:54
  • 1
    Seconded - in the format you've presented -r is a requirement. Depending on sed version [:digit:] may be a problem as well - [0-9] works just as well. – mikeserv Mar 15 '14 at 9:04
  • 1
    Apart from adding -r, i.e. enable extended regex syntax, change [:digit:] to [[:digit:]] or [0-9]. – devnull Mar 15 '14 at 9:08
  • Thanks! I'm getting an error saying that -r is an illegal option. – user1608941 Mar 15 '14 at 10:11
  • 1
    sed only changes the original file if you use the "in-place" option -i. By default, it just prints the edited text to stdout. – n.st Mar 15 '14 at 11:14

Since -r is unavailable, use this leaning toothpick forest:

sed 's/"\(-?[[:digit:]]\+\),\([[:digit:]]\{2\}\)"/\1.\2/g' input.csv > output.csv

sed -r is a GNU extension. And sadly, most tools that use regular expressions implement the language slightly differently (grep/sed, awk, perl, ...)

  • Thanks, I also ended up downloading and building the GNU sed for future use! – user1608941 Mar 15 '14 at 21:56

I find Perl's syntax simpler for such things (I am ignoring the quotes but you can add them if you wish):

perl -pe 's/(-*)(\d+),(\d{0,2})/$1$2.$3/g' input.csv > output.csv

You can also use the -i option to edit the original file directly.

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.