mjhjmtu

I have a CSV file with numbers in double quotes and without double quotes and for some numbers. I have to fix the negative symbols for only numbers: the tailing negative symbol has to be removed and added to the beginning.

Sample Input:

column 1, column 2, column 3, column 4, column 5
12-,"455,365.44-","string with quotes-and with a comma in between","4,432",6787

Sample Output:

column 1, column 2, column 3, column 4, column 5
-12,"-455,365.44","string with quotes-and with a comma in between","4,432",6787

GNU awk solution:

awk -v FPAT='[^,"]+|"[^"]+"' '
 NR==1; NR>1 
 for (i=1; i<=NF; i++) 
 if ($i~/^"?[0-9]+([0-9,.]+[0-9]+)?-"?$/) 
 sub(/-/, "", $i); 
 sub(/[0-9]/, "-&", $i); 
 
 printf "%s%s",$i,(i==NF? ORS:",") 
 
 ' file.csv

• 
  -v FPAT='[^,"]+|"[^"]+"' - regex pattern defining field value


• 
  $i~/^"?[0-9]+([0-9,.]+[0-9]+)?-"?$/ - check if a field contains a number with trailing minus sign - (number could be double-quoted)

The output:

column 1, column 2, column 3, column 4, column 5
-12,"-455,365.44","string with quotes-and with a comma in between","4,432",6787

CSV data requires a CSV parser. Ruby has one:

$ cat file.csv
12-,"455,365.44-","string with quotes-and with a comma in between","4,432",6787

$ ruby -rcsv -e '
 CSV.foreach(ARGV.shift) do |row|
 corrected = row.collect 
 puts CSV.generate_line(corrected)
 end
' file.csv

-12,"-455,365.44",string with quotes-and with a comma in between,"4,432",6787

The CSV generator decided that the "string with quotes" did not need to be quoted because it does not contain a comma.

awk solution that does not use gawk FPAT:

NR==1;

NR > 1 
 $0 = $0","

 while ($0) 
 ( f ~ /^[0-9]+[.]?[0-9]+-$/ ) 

Output for supplied sample file is:

column 1, column 2, column 3, column 4, column 5
-12,"-455,365.44","string with quotes-and with a comma in between","4,432",6787

With perl, but not suitable for all sorts of csv formats, works for given sample

$ perl -ne '@cols=/"[^"]+"|[^,]+/g;
 map s/"(.*)-"$/"-$1"/ or s/(.*)-$/-$1/ @cols;
 print join ",", @cols' ip.csv
column 1, column 2, column 3, column 4, column 5
-12,"-455,365.44","string with quotes-and with a comma in between","4,432",6787

• 
  @cols=/"[^"]+"|[^,]+/g save fields in array


• 
  map s/"(.*)-"$/"-$1"/ or s/(.*)-$/-$1/ @cols change each element of array. The second substitution is used only if first doesn't succeed
  
  
  • change (.*) to ([d,.]+) to sort of restrict the matching to digits and , and . only. This would still match strings like ..,..-
    
  
  


• 
  print join ",", @cols print the changed array with , as separator

a bit of fun without using temp array

perl -ne 'print join ",", map /^"/ ? s/"(.*)-"$/"-$1"/r : s/(.*)-$/-$1/r /"[^"]+"|[^,]+/g'

With sed implementations supporting -E, assuming the double quotes embedded in string fields are encoded as "" and that those string fields don't contain newline characters:

sed -E '
 :1
 s/^(("[^"]*"|[^"])*,)?([0-9.]+)-(,|$)/1-34/;
 s/^(("[^"]*"|[^"])*,)?"([0-9,.]+)-"(,|$)/1"-3"4/
 t1' < file

Depending on the nature of the input, you may want to be stricter in matching the numbers. For instance ([0-9.]+)- here would match on 12- but also on ...-. If that type of input may occur in the input, you could change it to ([0-9]*.?[0-9]+)- for instance.