VI - How to Replace text between the 6th comma and the 9th comma

user_14226526 0
Viewed 170

In VIM, How to use regexp or other skills to remove the period between the 6th comma and the 9th comma for the multiple lines of the following texts?

Change from:
4,Afghanistan,2,Medium,1951,1951.5,4134.756,3705.395,7840.151,12.009 4,Afghanistan,5,Constant fertility,2023,2023.5,21628.67,20554.282,42182.952,64.613

To:
4,Afghanistan,2,Medium,1951,1951.5,4134756,3705395,7840151,12.009 4,Afghanistan,5,Constant fertility,2023,2023.5,2162867,20554282,42182952,64.613

I have a csv file with lots of similar records like this. As the position of the 6th and 9th comma may not be in the same column, I can't use block-wise visual mode to delete those periods.

One way to do this is to split comma to columns in excel and replace it in the columns of excel. But my csv file is more than 100 MegaBytes and my computer is too slow to do it.

Per my vi and regexp knowledge, I could use below regexp to locate the position of the 6th comma, but that's not enough.
/\([^,]*,\)\{-6}

Is there any advanced regexp usages to remove the period between the 6th comma and the 9th comma in a multi-line csv file?

vim regex
3 Answers

As suggested in comments you can use a simple awk like this:

awk 'BEGIN {FS=OFS=","} {for (i=7; i<=9; ++i) sub(/\./, "", $i)} 1' file.csv

4,Afghanistan,2,Medium,1951,1951.5,4134756,3705395,7840151,12.009
4,Afghanistan,5,Constant fertility,2023,2023.5,2162867,20554282,42182952,64.613
user_5482250

Here we go with a pure Vim solution:

:%normal 6f,v3f,:s/\%V.\+\%V/\=substitute(submatch(0),'\.','','g')/
  • :[range]normal <command> executes normal mode command <command> on every line covered by [range]. See :help :normal.
  • % is a range that covers the whole buffer so the command will be executed on every line. See :help :range if your needs are different.
  • 6f, moves the cursor on the 6th , on the current line. See :help f.
  • v3f, starts a visual selection that extends to the third , to the right, effectively covering the text from the 6th , to the 9th ,.
  • :s/<pattern>/<replacement> substitutes the first occurence of <pattern> in the current line with <replacement>. See :h :s.
  • \%V.\+\%V is our pattern. The \%V at the beginning and end limit the search to the last visual selection and .\+ just means "1 or more of any character", so our substitution only works on the text from the 6th , to the 9th ,. See :help \%V and :help \+.
  • The <replacement> part of the command starts with \=<expression>, a special item that allows us to use the output of <expression> as replacement. See :help sub-replace-expression.
  • Our expression is substitute(submatch(0),'\.','','g') which substitutes every ('g') literal . ('\.') in the whole match of the parent substitution (submatch(0)) with nothing (''), effectively removing them. See :help substitute()and :help submatch().
user_5468610

visual select and use :s/\%V\.//g on the selection

user_20491410
Related