1

I wanted to replace Tab character with the following, depending on what is on either side of the tab. This is for quickly converting pasted in text (to vim 7.4) from a spreadsheet (xls/xlsx) that has update requests. Some fields are obviously text. Others could be text or numeric.

This function (if/when it works) allows quick conversion of the spreadsheet spec into part of a SQL command. So, the user must look at the metadata and determine if the field is character or numeric. There are 4 case for each Tab and what is on either side of it: CC (char, char), CN (char, num), NC (num, char), and NN (num, num). I had no problem mapping each of these, but getting it into a function to perform these operations runs into trouble. I get a "trailing characters" error. Here is my script (including control characters so you can see):

$
:map <F1> :%s/^I/,/^M$
:map <F2> :%s/^I/,'/^M$
:map <F3> :%s/^I/',/^M$
:map <F4> :%s/^I/','/^M$
:map nn :%s/^I/,/^M$
:map nc :%s/^I/,'/^M$
:map cn :%s/^I/',/^M$
:map cc :%s/^I/','/^M$
:map qb :%s/^/'/^M$
:map qe :%s/$/'/^M$
:map sp :%s/''/' '/^M$
:map ac :%s/^I/','/g^M:%s/^/'/g^M:%s/$/'/g^M$
:map pr :%s/^.*$/(&),/g^MG$x^[$
$
:function! QuoteSQL(...)$
:   let parsed = split(a:000[0], '\zs')$
:   let i = 1$
:   while i < len(a:000[0])$
:      if parsed[i-1] == 'C' && parsed[i] == 'C'$
:normal %s/^I/','/^M$
:      elseif parsed[i-1] == 'C' && parsed[i] == 'N'$
:normal %s/^I/',/^M$
:      elseif parsed[i-1] == 'N' && parsed[i] == 'C'$
:normal %s/^I/,'/^M$
:      else$
:normal %s/^I/,/^M$
:      endif$
:      let i=i+1$
:   endwhile$
:endfunction$

The call looks like this: :call QuoteSQL("NNCCCCNCCNN") - Each N or C refers to the fields in order. Numeric fields get no quotes and character fields get single quotes. Like I said I had no problem mapping such commands a cc, cn, nc, and nn. Inside the function I could in fact echo things successfully like echo "CC" etc. When I tried to put the operation I want based on each condition it fails. If the 1st 2 in the comparison are C and C, then the Tab is replaced by ,, etc. I will add steps to put needed quotes at the beginning and end of the lines as well. Then the parenthesis and commas. Imagine if you will that a char field will contain a 'C' and a numeric field will contain and 'N' (not a number I know - this is just for example to show output). Output looks like this:

('C','C',N,'C',N,N,'C'),
('C','C',N,'C',N,N,'C'),
.
.

How can I get my function to take the CNNNCNNCNCNCNNC type argument and apply it to the file I have opened in vim? Thanks.

Improve this question
8
  • Looks like the layout is all messed up. I had my source file on seperate lines, but it didn't come out that way in this post.
    – Jeff
    Feb 6, 2017 at 16:11
  • Please, next time, use the {} button to format your code.
    – Luc Hermitte
    Feb 6, 2017 at 16:28
  • Since my attempt of pasting code in did not work, I will pose the question another simple way. I have an argument passed to my function. I had an if, elseif, elseif, else clause on my function I got my function to work just fine as long as the actions based on the if clause was echo "whatever", but when I tried to put in something like %s/<TAB>/','/ it fails with "trailing characters" error. I hope this clarifies what I am having trouble achieving.
    – Jeff
    Feb 7, 2017 at 14:09
  • {:function! QuoteSQL(...)} {: let parsed = split(a:000[0], '\zs')} {: let i = 1} {: while i < len(a:000[0])} {: if parsed[i-1] == 'C' && parsed[i] == 'C'} {:normal %s/^I/','/^M} {: elseif parsed[i-1] == 'C' && parsed[i] == 'N'} {:normal %s/^I/',/^M} {: elseif parsed[i-1] == 'N' && parsed[i] == 'C'} {:normal %s/^I/,'/^M} {: else} {:normal %s/^I/,/^M} {: endif} {: let i=i+1} {: endwhile} {:endfunction}
    – Jeff
    Feb 7, 2017 at 14:20
  • My apologies. This is frustrating. Cannot find an example of how to apply the {}
    – Jeff
    Feb 7, 2017 at 14:20

1 Answer 1

Reset to default
1

You don't need :normal here.

Instead of using special characters use their stringified form -> CTRL-I becomes <tab>, and so on.

EDIT: My mistake, if you want to search for tab, search for \t. The transformations will be:

function! QuoteSQL(...) abort
:   let parsed = a:1
:   let i = 1
:   let len = strlen(a:1)
:   while i < len
:      if parsed[i-1 : i] == 'CC'
:        %s/\t/','/
:      elseif parsed[i-1 : i] == 'CN'
:        %s/\t/',/
:      elseif parsed[i-1 : i ] == 'NC'
:        %s/\t/,'/
:      else
:        %s/\t/,/
:      endif
:      let i += 1
:   endwhile
:endfunction
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.