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 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:

123,7,11,"$343,700.14","$34,928.63","$377,000.00","$15,421.92",19,2

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.

share|improve this question
    
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
add comment

4 Answers

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

Command:

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}'

Output:

123,7,11,343700.14,34928.63,377000.00,15421.92,19,2

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
share|improve this answer
    
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
add comment

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:

:repeat
s/"\$\([^,"]*\),/"$\1/g 
t repeat
s/"\$\([^"]*\)"/\1/g 

This document helped me to solve the problem.

share|improve this answer
add comment

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

{
$0=$0",";                                  # yes, cheating
while($0) {
  gsub(/\$/,"",$0)
  match($0,/ *"[^"]*" *,|[^,]*,/);
  sf=f=substr($0,RSTART,RLENGTH);          # save what matched in sf
  gsub(/^ *"?|"? *,$/,"",f);               # remove extra stuff
  gsub(/,/,"",f);
  if($0 ~ /,[^,]+,$/){
    printf "%s,",f;
  }else{
    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
123,7,11,343700.14,34928.63,377000.00,15421.92,19,2
share|improve this answer
    
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
add comment

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:

#!/usr/bin/perl

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

Input:

abc,"$1,243,311.99",def
abc2,"$43,311.99",def2

Output:

abc,$1243311.99,def
abc2,$43311.99,def2
share|improve this answer
    
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 –  warl0ck 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 –  warl0ck Oct 10 '12 at 4:20
add comment

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.