2

I'm trying to replace part of a string in a file

For example I have a csv file.

r1,col1,col2,35,000,col4,col5
r2,col1,col2,1,000,col4,col5
r3,col1,col2,325.33,col4,col5
r4,col1,col2,4,325.33,col4,col5

Essentially I want to replace the , in what appears to be col3 above. While preserving the first x numbers so it appears like the following:

r1,col1,col2,35000,col4,col5
r2,col1,col2,1000,col4,col5
r3,col1,col2,325.33,col4,col5
r4,col1,col2,4325.33,col4,col5

normally I'd execute

:%s/\,[0-9]*\,/\,\1/g

But when I run that I get

r1,col1,col2,000,col4,col5
r2,col1,col2,000,col4,col5
r3,col1,col2,325.33,col4,col5
r4,col1,col2,325.33,col4,col5

What should I be using in the second part of my replace so I get the desired output.

  • 1
    Depending on the values of your other columns, this is not as robust as Rastapopoulus' solution but for your given inputs, this is actually all it takes: %s/\v,\zs(\d+),(\d+)/\1\2 – Lieven Keersmaekers Nov 3 '17 at 6:00
1

You likely want :%s/\v(([^,]*,){3})([0-9]+),([0-9])/\1\3\4/.

Your goal is to remove a comma from the fourth field, if present, without removing commas elsewhere and without removing any other text. The complicating factor is that commas are also used as field separators. To solve the problem, you must consider what you know about the conditions under which commas can appear inside a field. After all, without any further constraints, your records are ambiguous.

It's tempting to consider any comma straddling two digits as subject to removal, but this won't work. Your sample input shows that you can have one field end with a digit and the next field start with one (col2,35,000).

If you know the first three fields don't themselves contain commas, then the problem becomes much easier, because then the first three sequences of zero or more non-commas followed by commas can be skipped over before removing any commas. Then the question becomes how to figure out when the fourth field has ended. You should ask yourself if you want to remove multiple commas from the fourth field, or if is it always either no commas or one comma.

I will assume, for the sake of this answer, that the fourth field contains at most one comma that ought to be removed. I will further assume that the comma appears after one or more digits and before at least one digit. Then you can use this in Vim:

:%s/\v(([^,]*,){3})([0-9]+),([0-9])/\1\3\4/

Or, if you prefer to used Sed:

sed -r 's/(([^,]*,){3})([0-9]+),([0-9])/\1\3\4/' filename.csv

How it Works

The regular expression (([^,]*,){3}) matches the first three fields and the field separators that follow them, all of which you will want to keep the same. [^,] matches any single character but a ,. The * after it causes zero or more of them to be matched instead of exactly one. The , after that matches the actual comma that follows this field of non-commas. This is all grouped with ( ) and the {3} applied to it causes it to be matched three times instead of once. Then that whole thing is grouped so that it can be accessed with \1. (The inner group also captures and could be accessed as \2.)

Then ([0-9]+) matches one or more (+) digit ([0-9]) and captures the match (( )) so it can be accessed as \3. The , character matches a literal comma; this is the part we are not going to retain. Then ([0-9]) captures a single digit so it can be accessed as \4.

You can make the regular expression a little simpler by using a single group for \1 and \3, i.e., (([^,]*,){3}[0-9]+). I've avoided that because I feel that it conceals the structure of your records--that they are composed of fields separated by commas--but there's nothing wrong with doing it that way. If you did that, \4 would become \3, so in the replacement pattern you'd use \1\3 instead of \1\3\4.

Finally, the \v at the start of the Vim regex and the -r passed to the sed serve to allow you to use extended regular expression syntax. That's why I was able to write ( and ) instead of \( and \), and + instead of \+.

1

You can do it from within vim using the following regex:

 %s/\([^,]\+,\)\{3}[^,]*\zs,\ze[^,]*\(,[^,]\+\)\{2}//

Here is an explanation:

  • \([^,]\+,\)\{3} matches exactly 3 csv fields and the following commas.

  • \(,[^,]\+\)\{2} matches exactly 2 csv fields and the preceding commas.

  • What's in the middle of these two expressions will capture the field from which one comma has to be removed.

0
$ sed 's/,\([0-9]\+\),\([0-9]\+\)/,\1\2/' input
r1,col1,col2,35000,col4,col5
r2,col1,col2,1000,col4,col5
r3,col1,col2,325.33,col4,col5
r4,col1,col2,4325.33,col4,col5

To explain the expression used:

  NODE                     EXPLANATION
  ,                        ','
  (                        group and capture to \1:
    [0-9]+                   any character of: '0' to '9' (1 or more
                             times (matching the most amount
                             possible))
  )                        end of \1
  ,                        ','
  (                        group and capture to \2:
    [0-9]+                   any character of: '0' to '9' (1 or more
                             times (matching the most amount
                             possible))
  )                        end of \2

We then replace the match with ,\1\2.

  • Except that it did work without escaping the +s. I don't know why it failed to see the '2' in col2 in the test case I copied the output from, but I have edited the expression accordingly. – DopeGhoti Nov 2 '17 at 21:13
  • @DopeGhoti Since the question, though not actually unclear, is somewhat underspecified, you may want to clarify the assumptions informing your use of a leading , in the regular expression. For example, r1,col1,5432,35,000,col4,col5 becomes r1,col1,543235,000,col4,col5. The sample input in the question doesn't fall prey to this problem. But the question, at least as I understand it, doesn't rule out such input. If explicated, I don't think this is really a weakness in your answer: , is only sometimes a field separator so records are ambiguous unless further constraints are assumed. – Eliah Kagan Nov 2 '17 at 21:24
  • If the columnar data other than column three are integer numbers, clearly this sort of solution would fall flat on its face. This solution rather does presume that (at least the first two fields of) the data are non-numeric. If we don't want to make that presumption given the sample data and lack of information to the contrary, a possibly rather more complex expression could be constructed that doesn't rely on the particulars of the data set. – DopeGhoti Nov 2 '17 at 21:32
0

You can use awk for this task. This script can process multiple commas in the fourth column. It is difficult to process this case (multiple commas) using the vim, I think. But it is easy by using awk.

Note: this solution for six columns only (I am counting r1 column, too).

awk '
BEGIN {
    FS = ",";
    OFS = ",";
}
{
    accum = "";
    for(i = 4; i < NF - 1; i++) {
        accum = accum $i;       
    }

    print $1, $2, $3, accum, $(NF - 1), $NF;
}' input.txt

Input (lines, with more than one comma in the target field, were added for testing)

r1,col1,col2,35,000,col4,col5
r2,col1,col2,1,000,col4,col5
r3,col1,col2,325.33,col4,col5
r4,col1,col2,4,325.33,col4,col5
r5,col1,col2,4,325,250.33,col4,col5
r6,col1,col2,4,100,325,250.33,col4,col5

Output

r1,col1,col2,35000,col4,col5
r2,col1,col2,1000,col4,col5
r3,col1,col2,325.33,col4,col5
r4,col1,col2,4325.33,col4,col5
r5,col1,col2,4325250.33,col4,col5
r6,col1,col2,4100325250.33,col4,col5

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.