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:

1. Fields are correctly highlighted, according to the CSV specification: quotes, commas inside quotes, quote inside quotes are all correctly processed.
2. It does not go beyond the last column (from a count of the columns in the first three lines).
3. HJKL go left, down, up, right by "cell". Focus is set to the first character of the cell. Join is remapped to ;j.
4. 0 and $ are changed to highlight the first and last cell. Focus is set to the first character of the cell.
5. The column number and heading (from the first line in the buffer) are displayed when moving around.

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. Press ;j if you need to join two lines.

This is the ftplugin csv.vim script:

if v:version < 700
  finish
endif

" get the number of columns. Count in first three lines in case -- at
" least one of them should not be empty
function! s:GetNumCols()
  let b:max_col = 1
  for l in [1, 2, 3]
    let c = strlen(substitute(getline(l), '\%(\%("\%([^"]\|""\)*"\)\|\%([^,"]*\)\)', '', 'g')) + 1
    if c > b:max_col
      let b:max_col = c
    endif
  endfor
  return b:max_col
endfunction

" Return the regex that can be used 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: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

" Get the number of columns 
call s:GetNumCols()
" start by highlighting the first column
let b:csv_column = 1
silent call s:Highlight(b:csv_column)

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> 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> k <Up>: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> 0 :let b:csv_column=1<CR>:call <SID>Highlight(b:csv_column)<CR>
nnoremap <silent> <buffer> $ :let b:csv_column=b:max_col<CR>:call <SID>Highlight(b:csv_column)<CR>
nnoremap <silent> <buffer> <LocalLeader>J J
nnoremap <silent> <buffer> <LocalLeader>K K

setlocal nowrap

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

Comments

 TO DO 

• Add note about switching to a non-csv buffer (see below).
• Ponder max_col (see below).
• Shouldn't script use 'if exists("b:did_ftplugin")...' to only run once per buffer?

Non-csv buffer

Add note about problem that occurs if you switch to another buffer in the same window (the :match to highlight a column looks crazy in a non-csv buffer). Need to alert reader that command :match will turn off highlighting. I suspect that adding auto commands to handle this would make the tip too complex.

Wow!

This tip is the best example of wiki power that I've seen. The original tip had a lot of promise, but also irritations. Two different (I think) anonymous users added and edited an amazing new script. I spent a lot of time tweaking the script and rewording the tip. Now Inkarkat has elevated it to the next level. Magnificent result! --JohnBeckett 01:16, 10 July 2008 (UTC)