Working with CSV files

Tip 667 Printable Monobook Previous Next

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.

Navigating in csv text

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.
• \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

setlocal nowrap textwidth=0
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

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

" Undo the stuff we changed.
let b:undo_ftplugin = 'setlocal wrap<|match none'

" 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

textwidth=0 is set so that lines are not broken when typed. 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.