1

I have a CSV file where I want to remove every second column. Each column value are separated by quotes and the column values can contain field separators: this means that convenient solutions such as this AWK script here won't work out of the box because of extra delimiters that do not mean delimination of columns inside quotes. Please, try to find some native Vim solution for this.

How can I remove every second column in Vim?


Example data as asked in a comment

"TakeMe","NotMe","TakeMe3","NotMe,4","Take,Me,5"
"1","2","3","4","5,"
"11","22","3,3","44","55"

and intended output

"TakeMe","TakeMe3","Take,Me,5"
"1","3","5,"
"11","3,3","55"

Alternative approaches

  1. :%!awk -F',' '{ for (i=1;i<=NF;i+=2) print $i }', then %s@$@,@g (separate column values), then ggVGJ (to join all values together with ^M as separators) and then %s@\n@\n@ (to remove ^M in bad formated files to separate rows).

  2. Extract value between double quotes in Unix.SE

  3. https://stackoverflow.com/questions/7804673/escaping-separator-within-double-quotes-in-awk

  4. %!cut -d '"' -f2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,...

  • 2
    You can use the csv.vim plugin. – Christian Brabandt Jul 20 '17 at 22:43
  • @Christian Brabandt can you show how? Or how would you do it with the plug-in? I use the plugin but haven't found a convenient way for this with it. – hhh Jul 20 '17 at 22:49
  • I suppose you also opened the corresponding issue at the plugin? I posted an answer there. – Christian Brabandt Jul 21 '17 at 10:19
  • You've stated that the column values can contain commas and included examples of this, but can they also contain extra quote characters, escaped somehow e.g. "a quote \" in an entry"? – Rich Jul 21 '17 at 12:25
3

this is doable with :s, as in

:%s/\("[^"]*"\)\s*,\s*"[^"]*"/\1/g

  • It is not so easy: column values can contain field separators – Christian Brabandt Jul 21 '17 at 10:20
  • @ChristianBrabandt It's a bit unclear, but I interpret the question as meaning that the column values can contain extra commas: the example and I think the approaches included in the question support this. This solution should therefore work fine. – Rich Jul 21 '17 at 13:02
0

I'm a little unclear on whether column values can contain extra " characters, as well as the extra commas that you include in your example.

If the answer is no, then you can record a simple recursive macro to remove every second column on a single line:

  1. gg: Move to start of file,
  2. qqq: Clear the q register: this is necessary for recursive macros,
  3. qq: Start recording in the q register,
  4. f": Find the end of the first entry,
  5. 2dt": Delete to end of second entry,
  6. ll: Move to start of third entry,
  7. @q: Replay the q macro. This is currently empty, so nothing happens,
  8. q: Finish recording,
  9. u: Undo change.

You can then play this macro on every line of the file with the command:

:%normal @q

This may look like a lot of steps, but note that steps 1–3 and steps 7–9 are essentially boilerplate, and don't require any thought, and the remaining steps 4–6 are simple normal-mode movement/editing operations that are just as quick, for me. Macros are always more efficient to use than they look when they are written down.

Note also that if the number of columns is always the same (as in your example), you don't even need a recursive macro. You can just replay an even simpler macro the correct number of times:

gg qq f" 2dt" ll q u

:%normal 3@q

If column values can contain extra quotes, you could in all likelihood still use this approach, but the macro would be more complicated, so another solution might work better.

0

Csv.vim is suggested by its author in GitHub. Perl script runs an external command outputting to a new buffer in Vim.

I tested them on a CSV file with about 400 rows and 30 columns: the for-loop solution hangs up (never getting completed) while perl solution has an instant completion.

Csv.vim

for i in range(100,1,-2) | exe "DeleteColumn" i | endfor

Perl in vim

 :new | r !perl -nle'$i=0;s/^"|"$//g;print map{$i++&1?"":"$_ "}split(/","/)' "#"

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.