JohnBeckett (talk | contribs) (→The code: use matchlist() to parse SC arguments) |
JohnBeckett (talk | contribs) (use <source>) |
||
Line 23: | Line 23: | ||
It's easy to find a column in csv text if you source the following script. Enter a command like <tt>:Csv 23</tt> to highlight column 23. |
It's easy to find a column in csv text if you source the following script. Enter a command like <tt>:Csv 23</tt> to highlight column 23. |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
" Highlight a column in csv text. |
" Highlight a column in csv text. |
||
" :Csv 1 " highlight first column |
" :Csv 1 " highlight first column |
||
Line 41: | Line 41: | ||
endfunction |
endfunction |
||
command! -nargs=1 Csv :call CSVH(<args>) |
command! -nargs=1 Csv :call CSVH(<args>) |
||
− | </ |
+ | </source> |
==Highlighting the header row== |
==Highlighting the header row== |
||
If the first row is a header row, you can highlight the header fields by putting the following lines in the file <tt>syntax/csv.vim</tt> (see [[VimTip1565#Syntax highlighting|syntax highlighting for ''moin'']]). This requires csv file type detection (see [[#Usage|''Usage'' below]]). |
If the first row is a header row, you can highlight the header fields by putting the following lines in the file <tt>syntax/csv.vim</tt> (see [[VimTip1565#Syntax highlighting|syntax highlighting for ''moin'']]). This requires csv file type detection (see [[#Usage|''Usage'' below]]). |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
syntax match csvHeading /\%1l\%(\%("\zs\%([^"]\|""\)*\ze"\)\|\%(\zs[^,"]*\ze\)\)/ |
syntax match csvHeading /\%1l\%(\%("\zs\%([^"]\|""\)*\ze"\)\|\%(\zs[^,"]*\ze\)\)/ |
||
highlight def link csvHeading Type |
highlight def link csvHeading Type |
||
− | </ |
+ | </source> |
==Viewing csv fields== |
==Viewing csv fields== |
||
The following commands can be entered to convert csv text to columns for easy viewing. Work on a temporary copy of your data because these commands will damage it! |
The following commands can be entered to convert csv text to columns for easy viewing. Work on a temporary copy of your data because these commands will damage it! |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
" Convert csv text to columns (press u to undo). |
" Convert csv text to columns (press u to undo). |
||
" Warning: This deletes ',' and crops wide columns. |
" Warning: This deletes ',' and crops wide columns. |
||
Line 60: | Line 60: | ||
:%s/\([^,]*\),\=/\=strpart(submatch(1).fill, 0, width)/ge |
:%s/\([^,]*\),\=/\=strpart(submatch(1).fill, 0, width)/ge |
||
:%s/\s\+$//ge |
:%s/\s\+$//ge |
||
− | </ |
+ | </source> |
Alternatively, you can change each comma to a newline to put each field on its own line: |
Alternatively, you can change each comma to a newline to put each field on its own line: |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
" Change CSV fields on current line to a list of separate items. |
" Change CSV fields on current line to a list of separate items. |
||
:s/,/\r/g |
:s/,/\r/g |
||
Line 69: | Line 69: | ||
" Same, for all lines. |
" Same, for all lines. |
||
:%s/,/\r/g |
:%s/,/\r/g |
||
− | </ |
+ | </source> |
In the replace text of a substitute, <tt>\r</tt> substitutes a newline. |
In the replace text of a substitute, <tt>\r</tt> substitutes a newline. |
||
Line 87: | Line 87: | ||
*Delete column. The command <pre>:DC n</pre> will delete the n-th column. If n is omitted, then the current highlighted column is deleted. |
*Delete column. The command <pre>:DC n</pre> will delete the n-th column. If n is omitted, then the current highlighted column is deleted. |
||
*Specify a character to be used as the delimiter (',' is the default). |
*Specify a character to be used as the delimiter (',' is the default). |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
:Delimiter " show delimiter |
:Delimiter " show delimiter |
||
:Delimiter ; " set delimiter to ';' |
:Delimiter ; " set delimiter to ';' |
||
:Delimiter \t " set delimiter to the tab character |
:Delimiter \t " set delimiter to the tab character |
||
− | </ |
+ | </source> |
*Change the default delimiter (you could put one of the following examples in your [[vimrc]]): |
*Change the default delimiter (you could put one of the following examples in your [[vimrc]]): |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
:let g:csv_delimiter = ";" |
:let g:csv_delimiter = ";" |
||
:let g:csv_delimiter = "\t" |
:let g:csv_delimiter = "\t" |
||
− | </ |
+ | </source> |
*Sort lines by column. This works like the <tt>:sort</tt> command except that the specified field is used for sorting, and the first line (heading) is not included. Some examples follow. |
*Sort lines by column. This works like the <tt>:sort</tt> command except that the specified field is used for sorting, and the first line (heading) is not included. Some examples follow. |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
:Sort " sort by the current column |
:Sort " sort by the current column |
||
:Sort 3 " sort by column 3 |
:Sort 3 " sort by column 3 |
||
Line 116: | Line 116: | ||
:Sort 3 f " same, by column 3 |
:Sort 3 f " same, by column 3 |
||
:5,12Sort! 3 f " reverse sort lines in range using column 3 floats |
:5,12Sort! 3 f " reverse sort lines in range using column 3 floats |
||
− | </ |
+ | </source> |
===Usage=== |
===Usage=== |
||
Put a .csv rule in [[filetype.vim]] |
Put a .csv rule in [[filetype.vim]] |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
autocmd BufNewFile,BufRead *.csv setf csv |
autocmd BufNewFile,BufRead *.csv setf csv |
||
− | </ |
+ | </source> |
Create file <tt>~/.vim/ftplugin/csv.vim</tt> (Unix) or <tt>$HOME/vimfiles/ftplugin/csv.vim</tt> (Windows) containing the script below. |
Create file <tt>~/.vim/ftplugin/csv.vim</tt> (Unix) or <tt>$HOME/vimfiles/ftplugin/csv.vim</tt> (Windows) containing the script below. |
||
Line 131: | Line 131: | ||
You can switch highlighting off by setting the filetype option to nothing: |
You can switch highlighting off by setting the filetype option to nothing: |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
:set ft= |
:set ft= |
||
− | </ |
+ | </source> |
This command switches highlighting on: |
This command switches highlighting on: |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
:set ft=csv |
:set ft=csv |
||
− | </ |
+ | </source> |
===The code=== |
===The code=== |
||
This is the ftplugin <tt>csv.vim</tt> script: |
This is the ftplugin <tt>csv.vim</tt> script: |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
" Filetype plugin for editing CSV files. |
" Filetype plugin for editing CSV files. |
||
" Version 2008-11-05 from http://vim.wikia.com/wiki/csv |
" Version 2008-11-05 from http://vim.wikia.com/wiki/csv |
||
Line 447: | Line 447: | ||
autocmd BufEnter <buffer> silent call <SID>Highlight(b:csv_column) |
autocmd BufEnter <buffer> silent call <SID>Highlight(b:csv_column) |
||
augroup END |
augroup END |
||
− | </ |
+ | </source> |
===Explanation of the regular expression=== |
===Explanation of the regular expression=== |
||
Line 478: | Line 478: | ||
==Working with Excel xls files== |
==Working with Excel xls files== |
||
One can use the [http://search.cpan.org/perldoc?xls2csv <tt>xls2csv</tt> Perl script] to convert Excel files to CSV, and then view/edit with Vim. You may put the following code in your [[vimrc]]: |
One can use the [http://search.cpan.org/perldoc?xls2csv <tt>xls2csv</tt> Perl script] to convert Excel files to CSV, and then view/edit with Vim. You may put the following code in your [[vimrc]]: |
||
+ | <source lang="vim"> |
||
− | <pre> |
||
autocmd BufReadPre *.xls set ro | setf csv |
autocmd BufReadPre *.xls set ro | setf csv |
||
autocmd BufReadPost *.xls silent! %!xls2csv -q -x "%" -c - |
autocmd BufReadPost *.xls silent! %!xls2csv -q -x "%" -c - |
||
autocmd BufReadPost *.xls redraw |
autocmd BufReadPost *.xls redraw |
||
− | </ |
+ | </source> |
It will view the XLS file in readonly mode as a CSV file by first converting on-the-fly. |
It will view the XLS file in readonly mode as a CSV file by first converting on-the-fly. |
||
Revision as of 11:32, 10 September 2009
CSV files (comma-separated values) are often used to save tables of data in plain text. Following are some useful techniques for working with CSV files. You can:
- Highlight all text in any column.
- View fields (convert csv text to columns or separate lines).
- Navigate using the HJKL keys to go left, down, up, right by cell (hjkl work as normal).
- Search for text in a specific column.
- Sort lines by column.
- Delete a column.
- Specify a delimiter other than comma.
Highlighting a column
It's easy to find a column in csv text if you source the following script. Enter a command like :Csv 23 to highlight column 23.
" Highlight a column in csv text.
" :Csv 1 " highlight first column
" :Csv 12 " highlight twelfth column
" :Csv 0 " switch off highlight
function! CSVH(colnr)
if a:colnr > 1
let n = a:colnr - 1
execute 'match Keyword /^\([^,]*,\)\{'.n.'}\zs[^,]*/'
execute 'normal! 0'.n.'f,'
elseif a:colnr == 1
match Keyword /^[^,]*/
normal! 0
else
match
endif
endfunction
command! -nargs=1 Csv :call CSVH(<args>)
Highlighting the header row
If the first row is a header row, you can highlight the header fields by putting the following lines in the file syntax/csv.vim (see syntax highlighting for moin). This requires csv file type detection (see Usage below).
syntax match csvHeading /\%1l\%(\%("\zs\%([^"]\|""\)*\ze"\)\|\%(\zs[^,"]*\ze\)\)/
highlight def link csvHeading Type
Viewing csv fields
The following commands can be entered to convert csv text to columns for easy viewing. Work on a temporary copy of your data because these commands will damage it!
" Convert csv text to columns (press u to undo).
" Warning: This deletes ',' and crops wide columns.
:let width = 20
:let fill = repeat(' ', width)
:%s/\([^,]*\),\=/\=strpart(submatch(1).fill, 0, width)/ge
:%s/\s\+$//ge
Alternatively, you can change each comma to a newline to put each field on its own line:
" Change CSV fields on current line to a list of separate items.
:s/,/\r/g
" Same, for all lines.
:%s/,/\r/g
In the replace text of a substitute, \r substitutes a newline.
The simple script given above does not provide easy navigation, and it assumes that commas are only used as delimiters. The following code has more features, and it is presented as a plugin due to its length.
Features
- Fields are correctly highlighted, according to the CSV specification: quotes, commas inside quotes, quote inside quotes are all correctly processed.
- It does not go beyond the last column (from a count of the columns in the first and last three lines).
- HJKL go left, down, up, right by "cell". Focus is set to the first character of the cell.
- 0 and $ highlight the first and last cell. Focus is set to the first character of the cell.
- Ctrl-f, Ctrl-b page forward and back, while staying in the same column.
- \J is Join :help J and \K is Keyword :help K (assuming the default \ local leader key :help maplocalleader).
- The column number and heading (from the first line in the buffer) are displayed when moving around.
- Search within column. The command
:SC n=str
will search for str in the n-th column. If "n=" is omitted, the search is within the currently highlighted column. For example::SC 2=john " search for john in the 2nd column only
:SC john " search for john in the currently highlighted column
Case sensitivity is the same as for / (toggle with :set ic! ic? see :help 'ignorecase'). After the search, the @/ variable is set. So, for example, after :SC 2=john, one can use g//d to delete all lines whose second field contains john. - Delete column. The command
:DC n
will delete the n-th column. If n is omitted, then the current highlighted column is deleted. - Specify a character to be used as the delimiter (',' is the default).
:Delimiter " show delimiter
:Delimiter ; " set delimiter to ';'
:Delimiter \t " set delimiter to the tab character
- Change the default delimiter (you could put one of the following examples in your vimrc):
:let g:csv_delimiter = ";"
:let g:csv_delimiter = "\t"
- Sort lines by column. This works like the :sort command except that the specified field is used for sorting, and the first line (heading) is not included. Some examples follow.
:Sort " sort by the current column
:Sort 3 " sort by column 3
:Sort! 3 " same, reverse order
:5,12Sort! 3 " same, but sort only lines 5 to 12 inclusive
:Sort n " numeric sort by the current column
:Sort 3 n " same, by column 3
:Sort 3 x " same, hex numbers
:Sort i u " ignore case; omit duplicate lines (keep unique)
:Sort iu " same
:Sort 3 iu " same, by column 3
" The n and x options use integers ("12.9" is 12, overflow is ignored).
" Use the f option for floating point (:version must show +float).
" Non-numbers sort before numbers.
:Sort f " sort using floats in current column
:Sort 3 f " same, by column 3
:5,12Sort! 3 f " reverse sort lines in range using column 3 floats
Usage
Put a .csv rule in filetype.vim
autocmd BufNewFile,BufRead *.csv setf csv
Create file ~/.vim/ftplugin/csv.vim (Unix) or $HOME/vimfiles/ftplugin/csv.vim (Windows) containing the script below.
Open a file (named anything.csv) that contains fields separated by commas. Use H J K L 0 $ to move from cell to cell.
If you open a *.csv file, the current column will be highlighted. Conversely, you may open a file with csv data, yet the file is not named *.csv, so highlighting will not occur.
You can switch highlighting off by setting the filetype option to nothing:
:set ft=
This command switches highlighting on:
:set ft=csv
The code
This is the ftplugin csv.vim script:
" Filetype plugin for editing CSV files.
" Version 2008-11-05 from http://vim.wikia.com/wiki/csv
if v:version < 700 || exists('b:did_ftplugin')
finish
endif
let b:did_ftplugin = 1
" Return number of characters (not bytes) in string.
function! s:CharLen(str)
return strlen(substitute(a:str, '.', 'x', 'g'))
endfunction
" Display a warning message.
function! s:Warn(msg)
echohl WarningMsg
echo a:msg
echohl NONE
endfunction
" Set or show column delimiter.
" Accept '\t' (2 characters: backslash, t) as the tab character.
function! s:Delimiter(delim)
if a:delim != ''
let want = a:delim == '\t' ? "\t" : a:delim
if s:CharLen(want) != 1
call s:Warn('Delimiter must be a single character')
return
endif
let b:csv_delimiter = want
endif
let b:changed_done = -1
let b:csv_column = 1
silent call s:Highlight(b:csv_column)
echo printf('Delimiter = "%s"', b:csv_delimiter == "\t" ? '\t' : strtrans(b:csv_delimiter))
endfunction
command! -buffer -nargs=? Delimiter call <SID>Delimiter('<args>')
" Get string containing delimiter (default ',') specified for current buffer.
" A command like ':let g:csv_delimiter = ";"' changes the default.
function! s:GetStr(id)
if !exists('b:csv_delimiter') || b:csv_delimiter == ''
if exists('g:csv_delimiter') && g:csv_delimiter != ''
let b:csv_delimiter = g:csv_delimiter
else
let b:csv_delimiter = ','
endif
endif
if !exists('b:csv_str')
let b:csv_str = {'delim': ''}
endif
if b:csv_str['delim'] !=# b:csv_delimiter
" Define strings using delimiter ',' then substitute if required.
let b:csv_str['delim'] = ','
let b:csv_str['numco'] = '\%(\%("\%([^"]\|""\)*"\)\|\%([^,"]*\)\)'
let b:csv_str['expr1'] = '\%(\%("\zs\%([^"]\|""\)*\ze"\)\|\%(\zs[^,"]*\ze\)\)'
let b:csv_str['expr2'] = '\%(\%(\zs"\%([^"]\|""\)*",\?\ze\)\|\%(\zs[^,"]*,\?\ze\)\)'
let b:csv_str['expr3'] = '^\%(\%(\%("\%([^"]\|""\)*"\)\|\%([^,"]*\)\),\)\{'
let b:csv_str['delco'] = ',$'
let b:csv_str['sear1'] = '^\%(\%("\%([^,]\|""\)*\zs'
let b:csv_str['sear2'] = '\ze\%([^,]\|""\)*"\)\|\%([^,"]*\zs'
let b:csv_str['sear3'] = '\ze[^,"]*\)\)'
let b:csv_str['sear4'] = '^\%(\%(\%("\%([^"]\|""\)*"\)\|\%([^,"]*\)\),\)\{'
let b:csv_str['sear5'] = '}\%(\%("\%([^,]\|""\)*\zs'
if b:csv_delimiter != ','
for key in keys(b:csv_str)
let b:csv_str[key] = substitute(b:csv_str[key], ',', b:csv_delimiter, 'g')
endfor
endif
endif
return b:csv_str[a:id]
endfunction
" Get the number of columns (maximum of number in first and last three
" lines; at least one of them should contain typical csv data).
function! s:GetNumCols()
let b:csv_max_col = 1
for l in [1, 2, 3, line('$')-2, line('$')-1, line('$')]
" Determine number of columns by counting the (unescaped) delimiters.
" Note: The regexp may also return unbalanced ", so filter out anything
" which isn't a delimiter in the second pass.
let c = s:CharLen(substitute(substitute(getline(l), s:GetStr('numco'), '', 'g'), '"', '', 'g')) + 1
if b:csv_max_col < c
let b:csv_max_col = c
endif
endfor
if b:csv_max_col <= 1
let b:csv_max_col = 999
call s:Warn('No delimiter-separated columns were detected.')
endif
return b:csv_max_col
endfunction
" Return regex to find the n-th column.
function! s:GetExpr(colnr, ...)
if a:0 == 0 " field only
let field = s:GetStr('expr1')
else " field with quotes (if present) and trailing delimiter (if present)
let field = s:GetStr('expr2')
endif
if a:colnr > 1
return s:GetStr('expr3') . (a:colnr - 1) . '}' . field
else
return '^' . field
endif
endfunction
" Extract and echo the column header on the status line.
function! s:PrintColInfo(colnr)
let colHeading = substitute(matchstr(getline(1), s:GetExpr(a:colnr)), '^\s*\(.*\)\s*$', '\1', '')
let info = 'Column ' . a:colnr
if empty(colHeading)
echo info
else
echon info . ': '
echohl Type
" Limit length to avoid "Hit ENTER" prompt.
echon strpart(colHeading, 0, (&columns / 2)) . (len(colHeading) > (&columns / 2) ? '...' : '')
echohl NONE
endif
endfunction
" Highlight n-th column (if n > 0).
" Remove previous highlight match (ignore error if none).
" matchadd() priority -1 means 'hlsearch' will override the match.
function! s:Highlight(colnr)
silent! call matchdelete(b:csv_match)
if a:colnr > 0
if exists('*matchadd')
let b:csv_match = matchadd('Keyword', s:GetExpr(a:colnr), -1)
else
execute '2match Keyword /' . s:GetExpr(a:colnr) . '/'
endif
if b:changed_done != b:changedtick
let b:changed_done = b:changedtick
call s:GetNumCols()
endif
call s:Focus_Col(a:colnr)
endif
endfunction
" Focus the cursor on the n-th column of the current line.
function! s:Focus_Col(colnr)
normal! 0
call search(s:GetExpr(a:colnr), '', line('.'))
call s:PrintColInfo(a:colnr)
endfunction
" Highlight next column.
function! s:HighlightNextCol()
if b:csv_column < b:csv_max_col
let b:csv_column += 1
endif
call s:Highlight(b:csv_column)
endfunction
" Highlight previous column.
function! s:HighlightPrevCol()
if b:csv_column > 1
let b:csv_column -= 1
endif
call s:Highlight(b:csv_column)
endfunction
" Wrapping would distort the column-based layout.
" Lines must not be broken when typed.
setlocal nowrap textwidth=0
" Undo the stuff we changed.
let b:undo_ftplugin = "setlocal wrap< textwidth<"
\ . "|if exists('*matchdelete')|call matchdelete(b:csv_match)|else|2match none|endif"
\ . "|sil! exe 'nunmap <buffer> H'"
\ . "|sil! exe 'nunmap <buffer> L'"
\ . "|sil! exe 'nunmap <buffer> J'"
\ . "|sil! exe 'nunmap <buffer> K'"
\ . "|sil! exe 'nunmap <buffer> <C-f>'"
\ . "|sil! exe 'nunmap <buffer> <C-b>'"
\ . "|sil! exe 'nunmap <buffer> 0'"
\ . "|sil! exe 'nunmap <buffer> $'"
\ . "|sil exe 'augroup csv' . bufnr('')"
\ . "|sil exe 'au!'"
\ . "|sil exe 'augroup END'"
let b:changed_done = -1
" Highlight the first column, but not if reloading or resetting filetype.
if !exists('b:csv_column')
let b:csv_column = 1
endif
" Following highlights column and calls GetNumCols() if set filetype manually
" (BufEnter will also do it if filetype is set during load).
silent call s:Highlight(b:csv_column)
" Return Float value of field in line selected by regex, or the String field.
function! s:GetValue(line, regex)
let field = matchstr(a:line, a:regex)
let val = str2float(field)
if val == 0 && match(field, '^0*\.\?0*$') < 0
return field
endif
return val
endfunction
" Compare lines based on the floating point values in the specified column.
" This uses string compare 'ignorecase' option if neither field is a float.
function! s:CompareLines(line1, line2)
let val1 = s:GetValue(a:line1, b:csv_sort_regex)
let val2 = s:GetValue(a:line2, b:csv_sort_regex)
if type(val1) != type(val2)
let val1 = type(val1)
let val2 = type(val2)
endif
let ascending = val1 > val2 ? 1 : val1 < val2 ? -1 : 0
return b:csv_sort_ascending ? ascending : -ascending
endfunction
" Sort the n-th column, the highlighted one by default.
" Column number is first optional arg; following are flags for :sort.
function! s:SortCol(bang, ...) range
let colnr = b:csv_column
let args = copy(a:000)
if len(args) > 0 && args[0] =~ '^\d\+$'
let colnr = str2nr(args[0])
unlet args[0]
endif
if colnr < 1 || colnr > b:csv_max_col
call s:Warn('column number out of range.')
endif
let first = a:firstline == 1 ? 2 : a:firstline
let flags = join(args)
if flags == 'f'
let b:csv_sort_ascending = (a:bang == '')
let b:csv_sort_regex = s:GetExpr(colnr)
call setline(first, sort(getline(first, a:lastline), function('s:CompareLines')))
else
let cmd = first.','.a:lastline.'sort'.a:bang
execute cmd 'r'.flags '/'.escape(s:GetExpr(colnr), '/').'/'
endif
endfunction
command! -bang -buffer -nargs=* -range=% Sort <line1>,<line2>call <SID>SortCol('<bang>', <f-args>)
" Delete the n-th column, the highlighted one by default.
function! s:DeleteCol(colnr)
if a:colnr == ''
let col = b:csv_column
else
let col = str2nr(a:colnr)
endif
if col < 1 || col > b:csv_max_col
call s:Warn('column number out of range.')
endif
execute '%s/'.escape(s:GetExpr(col, 1), '/').'//'
if col == b:csv_max_col
execute 'silent %s/'.escape(s:GetStr('delco'), '/').'//e'
endif
let b:csv_max_col -= 1
if b:csv_column > b:csv_max_col
call s:HighlightPrevCol()
endif
endfunction
command! -buffer -nargs=? DC call <SID>DeleteCol('<args>')
" Search the n-th column. Argument in n=regex form where n is the column
" number, and regex is the expression to use. If "n=" is omitted, then
" use the current highlighted column.
function! s:SearchCol(args)
let [colstr, target] = matchlist(a:args, '\%(\([1-9][0-9]*\)=\)\?\(.*\)')[1:2]
if colstr == ''
let col = b:csv_column
else
let col = str2nr(colstr)
endif
if col < 1 || col > b:csv_max_col
call s:Warn('column number out of range')
endif
if col == 1
let @/ = s:GetStr('sear1').target.s:GetStr('sear2').target.s:GetStr('sear3')
else
let @/ = s:GetStr('sear4').(col-1).s:GetStr('sear5').target.s:GetStr('sear2').target.s:GetStr('sear3')
endif
endfunction
" Use :SC n=string<CR> to search for string in the n-th column
command! -buffer -nargs=1 SC execute <SID>SearchCol('<args>')|normal! n
nnoremap <silent> <buffer> H :call <SID>HighlightPrevCol()<CR>
nnoremap <silent> <buffer> L :call <SID>HighlightNextCol()<CR>
nnoremap <silent> <buffer> J <Down>:call <SID>Focus_Col(b:csv_column)<CR>
nnoremap <silent> <buffer> K <Up>:call <SID>Focus_Col(b:csv_column)<CR>
nnoremap <silent> <buffer> <C-f> <PageDown>:call <SID>Focus_Col(b:csv_column)<CR>
nnoremap <silent> <buffer> <C-b> <PageUp>:call <SID>Focus_Col(b:csv_column)<CR>
nnoremap <silent> <buffer> <C-d> <C-d>:call <SID>Focus_Col(b:csv_column)<CR>
nnoremap <silent> <buffer> <C-u> <C-u>:call <SID>Focus_Col(b:csv_column)<CR>
nnoremap <silent> <buffer> 0 :let b:csv_column=1<CR>:call <SID>Highlight(b:csv_column)<CR>
nnoremap <silent> <buffer> $ :let b:csv_column=b:csv_max_col<CR>:call <SID>Highlight(b:csv_column)<CR>
nnoremap <silent> <buffer> gm :call <SID>Focus_Col(b:csv_column)<CR>
nnoremap <silent> <buffer> <LocalLeader>J J
nnoremap <silent> <buffer> <LocalLeader>K K
" The column highlighting is window-local, not buffer-local, so it can persist
" even when the filetype is undone or the buffer changed.
execute 'augroup csv' . bufnr('')
autocmd!
" These events only highlight in the current window.
" Note: Highlighting gets slightly confused if the same buffer is present in
" two split windows next to each other, because then the events aren't fired.
autocmd BufLeave <buffer> silent call <SID>Highlight(0)
autocmd BufEnter <buffer> silent call <SID>Highlight(b:csv_column)
augroup END
Explanation of the regular expression
The above code is fairly easy to understand, except perhaps for the regular expressions. The following regex (inspired by RegExLib 1520) is used several times:
\%(\%("\zs\%([^"]\|""\)*\ze"\)\|\%(\zs[^,"]*\ze\)\)
It is explained as follows:
1. \%( # unbackref'ed grouping for one CSV field 2. \%( # first possibility of a field 3. " # starts and ends with " 4. \zs # beginning of matched string 5. \%( 6. [^"]\|"" # anything not ", or a "" (escaped quote) 7. \)* 8. \ze # end of matched string 9. " 10. \) # end first possibility of field 11. \| # or 12. \%( # second possibility 13. \zs 14. [^,"]* # anything but " and comma 15. \ze 16. \) 17. \)
Working with Excel xls files
One can use the xls2csv Perl script to convert Excel files to CSV, and then view/edit with Vim. You may put the following code in your vimrc:
autocmd BufReadPre *.xls set ro | setf csv
autocmd BufReadPost *.xls silent! %!xls2csv -q -x "%" -c -
autocmd BufReadPost *.xls redraw
It will view the XLS file in readonly mode as a CSV file by first converting on-the-fly.
Comments
Note
- Add any problem reports or quick comments below.
- See the discussion page for extended discussions and more information.
- The search system used on this wiki ignores words less than four characters. Therefore, you can't find this page by searching for "CSV" (although entering csv site:vim.wikia.com into Google works). As a workaround, we have a redirect so you can enter "csv" in the search box in the sidebar and press Enter or click Go. However, clicking Search still stupidly finds nothing. You can read a little more on the wikia-l mailing list archive.