Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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?

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

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
Thanks, I also ended up downloading and building the GNU sed for future use! –  user1608941 Mar 15 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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