created March 1, 2004 · complexity intermediate · author Benjamin Peterson · version 7.0
It can be hard finding a column when working with csv files (comma-separated values). It's a lot easier if you source the following script. You can then 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>)
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 version is a much better tool for editing CSV files. One can use the xls2csv Perl script to convert Excel files to CSV, and then view/edit with Vim. Features include:
- 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.
- The column number and heading (from the first line in the buffer) are displayed when moving around.
- Search within column.
:SC n=str
will search for strin the n-th column. If "n=" is omitted, then the search within the current highlighted column. E.g.::SC 2=john
will search for john in the 2nd column only. Case sensitivity is the same as / and ? (set by ignorecase variable). - \J is Join :help J and \K is Keyword :help K (assuming the default \ local leader key :help maplocalleader).
Create file ~/.vim/ftplugin/csv.vim (Unix) or $HOME/vimfiles/ftplugin/csv.vim (Windows) containing the script below.
Define file type detection for *.csv files (see file type detection for moin):
autocmd BufNewFile,BufRead *.csv setf csv
Open a file (named anything.csv) that contains fields separated by commas. Use H J K L 0 $ to move from cell to cell.
This is the ftplugin csv.vim script:
" Filetype plugin for editing CSV files. " From http://vim.wikia.com/wiki/VimTip667 if v:version < 700 || exists("b:did_ftplugin") finish endif let b:did_ftplugin = 1 " 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) commas. " Note: The regexp may also return unbalanced ", so filter out anything " which isn't a comma in the second pass. let c = strlen(substitute(substitute(getline(l), '\%(\%("\%([^"]\|""\)*"\)\|\%([^,"]*\)\)', '', '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 = 10000 echohl WarningMsg echo "No comma-separated columns were detected. " echohl NONE endif return b:csv_max_col endfunction " Return regex to find the n-th column. function! s:GetExpr(colnr) if a:colnr > 1 return '^\%(\%(\%("\%([^"]\|""\)*"\)\|\%([^,"]*\)\),\)\{' . (a:colnr - 1) . '}\%(\%("\zs\%([^"]\|""\)*\ze"\)\|\%(\zs[^,"]*\ze\)\)' else return '^\%(\%(\%("\zs\%([^"]\|""\)*\ze"\)\|\%(\zs[^,"]*\ze\)\)\)' endif endfunction " Extract and echo the column header on the status line. function! s:PrintColInfo(colnr) let l:colHeading = substitute( matchstr( getline(1), s:GetExpr(a:colnr) ), '^\s*\(.*\)\s*$', '\1', '' ) let l:info = 'Column ' . a:colnr if ! empty(l:colHeading) let l:info .= ': ' . l:colHeading endif " Limit length to avoid "Hit ENTER" prompt. echo strpart(l:info, 0, (&columns / 2)) . (len(l:info) > (&columns / 2) ? "..." : "") endfunction " Highlight n-th column. function! s:Highlight(colnr) execute 'match Keyword /' . s:GetExpr(a:colnr) . '/' call s:Focus_Col(a:colnr) 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<|match none' call s:GetNumCols() " Highlight the first column, but not if reloading or resetting filetype " (the highlighting occurs in BufEnter event). if ! exists('b:csv_column') let b:csv_column = 1 endif " 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(colexp) let regex='\%(\([1-9][0-9]*\)=\)\?\(.*\)' let colstr=substitute(a:colexp, regex, '\1', '') let target=substitute(a:colexp, regex, '\2', '') if colstr=="" let col=b:csv_column else let col=str2nr(substitute(a:colexp, '=.*', '', '')) if col<1 || col>b:csv_max_col echoerr "column number out of range" endif endif if col==1 let @/='^\%(\%("\%([^,]\|""\)*\zs'.target.'\ze\%([^,]\|""\)*"\)\|\%([^,"]*\zs'.target.'\ze[^,"]*\)\)' else let @/='^\%(\%(\%("\%([^"]\|""\)*"\)\|\%([^,"]*\)\),\)\{' . (col-1) . '}\%(\%("\%([^,]\|""\)*\zs'.target.'\ze\%([^,]\|""\)*"\)\|\%([^,"]*\zs'.target.'\ze[^,"]*\)\)' endif endfunction " Use :SC n=string<CR> to search for string in the n-th column command! -nargs=1 SC exec <SID>SearchCol("<args>") | call search('') " to avoid press-enter message due to long regex nnoremap <silent> <buffer> n n nnoremap <silent> <buffer> N 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> 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> <LocalLeader>J J nnoremap <silent> <buffer> <LocalLeader>K K " The :match 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> match none autocmd BufEnter <buffer> silent call <SID>Highlight(b:csv_column) augroup END
Comments
Question: Is it possible to highlight the column header on the status line to be the same color as the highlighted column? It is some small convenience, I think it would be nice to have although what we have now is pretty good already.
- No, that's not possible; only the entire status message can be highlighted, not parts of it. -- Inkarkat 09:32, 15 July 2008 (UTC)