2

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?

Improve this question
7
  • 1
    Try adding -r flag to sed.
    – Capt. Crunch
    Mar 15, 2014 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, 2014 at 9:04
  • 1
    Apart from adding -r, i.e. enable extended regex syntax, change [:digit:] to [[:digit:]] or [0-9].
    – devnull
    Mar 15, 2014 at 9:08
  • Thanks! I'm getting an error saying that -r is an illegal option.
    – user1608941
    Mar 15, 2014 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, 2014 at 11:14

2 Answers 2

Reset to default
1

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, ...)

Improve this answer
1
  • Thanks, I also ended up downloading and building the GNU sed for future use!
    – user1608941
    Mar 15, 2014 at 21:56
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .