需求說明

有時候，經(jīng)常需要將excel表格中數(shù)據(jù)，轉化成sql，方便在數(shù)據(jù)庫中進行靈活的查詢。
比如，如下是從excel表格中復制出的數(shù)據(jù)：

NAME    AGE RANK
paopao  5   6
kobe    45  1
tim 36  2
park    35  4
james   35  7
westbrook   32  99

如果將其轉化成sql腳本，放到數(shù)據(jù)庫中，就可以方便地進行查詢：

-- NAME AGE RANK
select 'paopao' NAME, '5' AGE, '6' RANK from dual union all
select 'kobe' NAME, '45' AGE, '1' RANK from dual union all
select 'tim' NAME, '36' AGE, '2' RANK from dual union all
select 'park' NAME, '35' AGE, '4' RANK from dual union all
select 'james' NAME, '35' AGE, '7' RANK from dual union all
select 'westbrook' NAME, '32' AGE, '99' RANK from dual

VimScript實現(xiàn)

首先實現(xiàn)一個函數(shù)，用于將給一行數(shù)據(jù)添加列：

" combine the column value list with column name, adding prefix and suffix
function! CombineColumnValueAndName(valList, prefix, colList, suffix)
    let resultStr = ''
    let i = 0
    for colValue in a:valList
        let resultStr = resultStr . "\'" . colValue . "\' " . a:colList[i] . ", "
        let i = i + 1
    endfor
    " remove the last ', ' at the end
    let resultStr = strpart(resultStr, 0, strlen(resultStr) - 2)
    return a:prefix . resultStr . a:suffix
endfunction

然后，定義一個函數(shù)，讀取整個緩沖區(qū)的內(nèi)容，然后逐行處理：

" convert the buffer into select-from-dual clause
function! GenerateSelectFromDual()
    " read all the buffer
    let allLines = []
    g/$/let line = getline(".") | call add(allLines, line)

    let resultLines = []

    " get all column names into a list. split by \t and keep empty
    let colNameList = split(allLines[0], '\t', 1)
    " add column names into result comment, split by space
    call add(resultLines, '-- ' . join(colNameList, ' '))
    
    " convert line by line
    let index = 1
    while index < len(allLines)
        let curLine = allLines[index]
        let valList = split(curLine, '\t', 1)
        call add(resultLines, CombineColumnValueAndName(valList, "select ", colNameList, " from dual union all"))
        let index = index + 1
    endwhile
    let resultStr = join(resultLines, "\n")
    let @u = strpart(resultStr, 0, strlen(resultStr) - 10)
    normal! ggdG
    normal! 0"up
    return ''
endfunction

最后定義一個自定義的命令，來調(diào)用這個函數(shù)：

command GenerateSelectFromDual exec GenerateSelectFromDual()

效果

將前面提到的三段vimscript腳本，添加到.vimrc文件中，source $MYVIMRC生效后。執(zhí)行命令:GenerateSelectFromDual就可以達到前面需求章節(jié)描述的效果(支持某些列為空的情況)。
轉換前：

NAME    AGE RANK
paopao  5   6
kobe    45  1
kobe        1
tim 36  2
Tim 36  
park    35  4
    35  4
james   35  7
        
westbrook   32  99

轉換后：

-- NAME AGE RANK
select 'paopao' NAME, '5' AGE, '6' RANK from dual union all
select 'kobe' NAME, '45' AGE, '1' RANK from dual union all
select 'kobe' NAME, '' AGE, '1' RANK from dual union all
select 'tim' NAME, '36' AGE, '2' RANK from dual union all
select 'tim' NAME, '36' AGE, '' RANK from dual union all
select 'park' NAME, '35' AGE, '4' RANK from dual union all
select '' NAME, '35' AGE, '4' RANK from dual union all
select 'james' NAME, '35' AGE, '7' RANK from dual union all
select '' NAME, '' AGE, '' RANK from dual union all
select 'westbrook' NAME, '32' AGE, '99' RANK from dual

效果如下：

VimScriptConvertExcelData2Sql

參考資料

主要參考vim自帶的幫助系統(tǒng)，比如:help add查看add函數(shù)的幫助，通過Ctrl+]可以跳轉到指定關鍵字的章節(jié)，通過Ctrl+o可以跳轉回剛才的位置，通過Ctrl+i可以在歷史位置列表中往前跳轉。