diff options
Diffstat (limited to 'doc/ft-csv.txt')
| -rw-r--r-- | doc/ft-csv.txt | 1768 |
1 files changed, 0 insertions, 1768 deletions
diff --git a/doc/ft-csv.txt b/doc/ft-csv.txt deleted file mode 100644 index 7ef33464..00000000 --- a/doc/ft-csv.txt +++ /dev/null @@ -1,1768 +0,0 @@ -if !exists('g:polyglot_disabled') || index(g:polyglot_disabled, 'csv') == -1 - -*ft-csv.txt* For Vim version 7.4 Last Change: Thu, 15 Jan 2015 - -Author: Christian Brabandt <cb@256bit.org> -Version: 0.31 -Homepage: http://www.vim.org/scripts/script.php?script_id=2830 - -The VIM LICENSE applies to the CSV filetype plugin (see |copyright|). -NO WARRANTY, EXPRESS OR IMPLIED. USE AT-YOUR-OWN-RISK. - *csv-toc* -1. Introduction.................................|csv-intro| -2. Installation.................................|csv-installation| -3. CSV Commands.................................|csv-commands| - 3.1 WhatColumn..............................|WhatColumn_CSV| - 3.2 NrColumns...............................|NrColumns_CSV| - 3.3 SearchInColumn..........................|SearchInColumn_CSV| - 3.4 HiColumn................................|HiColumn_CSV| - 3.5 ArrangeColumn...........................|ArrangeColumn_CSV| - 3.6 UnArrangeColumn.........................|UnArrangeColumn_CSV| - 3.7 DeleteColumn............................|DeleteColumn_CSV| - 3.8 InitCSV.................................|InitCSV| - 3.9 Header..................................|Header_CSV| - 3.10 Sort...................................|Sort_CSV| - 3.11 CopyColumn.............................|Copy_CSV| - 3.12 MoveColumn.............................|MoveCol_CSV| - 3.13 Sum of a column........................|SumCol_CSV| - 3.14 Create new records ....................|NewRecord_CSV| - 3.15 Change the delimiter...................|NewDelimiter_CSV| - 3.16 Check for duplicate records............|Duplicate_CSV| - 3.17 Normal mode commands...................|csv-mapping| - 3.18 Convert CSV file.......................|csv-convert| - 3.19 Dynamic filters........................|csv-filter| - 3.20 Analyze a column.......................|csv-analyze| - 3.21 Vertical Folding.......................|csv-vertfold| - 3.22 Transposing columns....................|csv-transpose| - 3.23 Transforming into a table..............|csv-tabularize| - 3.24 Add new empty columns..................|AddColumn_CSV| - 3.25 Substitute in columns..................|Substitute_CSV| - 3.26 Count values inside a column...........|Count_CSV| - 3.27 Maximum/Minimum values ................|MaxCol_CSV| - 3.28 Average values.........................|AvgCol_CSV| - 3.29 Variance of a Column...................|VarCol_CSV| - 3.30 Standard Deviation of a Column.........|StdDevCol_CSV| - 3.31 Duplicate columns......................|DupColumn_CSV| - 3.32 Column width...........................|ColumnWidth_CSV| -4. CSV Filetype configuration...................|csv-configuration| - 4.1 Delimiter...............................|csv-delimiter| - 4.2 Column..................................|csv-column| - 4.3 HiGroup.................................|csv-higroup| - 4.4 Strict Columns..........................|csv-strict| - 4.5 Concealing..............................|csv-conceal| - 4.6 Newlines................................|csv-newline| - 4.7 Highlight column automatically..........|csv-hicol| - 4.8 Fixed width columns.....................|csv-fixedwidth| - 4.8.1 Manual setup - 4.8.2 Setup using a Wizard - 4.9 CSV Header lines........................|csv-header| - 4.10 Number format..........................|csv-nrformat| - 4.11 Move folded lines......................|csv-move-folds| - 4.12 Using Comments.........................|csv-comments| - 4.13 Size and performance considerations....|csv-size| -5. Functions....................................|CSV-Functions| - 5.1 CSVPat()................................|CSVPat()| - 5.2 CSVField()..............................|CSVField()| - 5.3 CSVCol()................................|CSVCol()| - 5.4 CSVSum()................................|CSVSum()| - 5.5 CSVCount()..............................|CSVCount()| - 5.6 CSVMax()................................|CSVMax()| - 5.7 CSVMin()................................|CSVMin()| - 5.8 CSVAvg()................................|CSVAvg()| - 5.9 CSVWidth()..............................|CSVWidth()| -6. CSV Tips and Tricks..........................|csv-tips| - 6.1 Statusline..............................|csv-stl| - 6.2 Slow CSV plugin.........................|csv-slow| - 6.3 Defining custom aggregate functions.....|csv-aggregate-functions| - 6.4 Autocommand on opening/closing files....|csv-arrange-autocmd| - 6.5 CSV Syntax error........................|csv-syntax-error| - 6.6 Calculating new column values...........|csv-calculate-column| - 6.7 Using the result of an evaluation.......|b:csv_result| -7. CSV Changelog................................|csv-changelog| - -============================================================================== -1. Introduction *csv-intro* - -This plugin is used for handling column separated data with Vim. Usually those -files are called csv files and use the ',' as delimiter, though sometimes they -use e.g. the '|' or ';' as delimiter and there also exists fixedwidth columns. -The aim of this plugin is to ease handling these kinds of files. - -This is a filetype plugin for CSV files. It was heavily influenced by -the Vim Wiki Tip667 (http://vim.wikia.com/wiki/VimTip667), though it -works differently. For instructions on installing this file, type -:help add-local-help |add-local-help| inside Vim. For a screenshot, of -how the plugin can be used, see http://www.256bit.org/~chrisbra/csv.gif - -It will make use of the |+vartabs| feature for tab delimited files to arrange -them automatically. - -============================================================================== -2. Installation *csv-installation* - -In order to have vim automatically detect csv files, you need to have -|ftplugins| enabled (e.g. by having this line in your |.vimrc| file: > - - :filetype plugin on - -< -The plugin already sets up some logic to detect CSV files. By default, -the plugin recognizes *.csv and *.dat files as CSV filetype. In order that the -CSV filetype plugin is loaded correctly, vim needs to be enabled to load -|filetype-plugins|. This can be ensured by putting a line like this in your -|.vimrc|: > - :filetype plugin on -< -(see also |filetype-plugin-on|). - -In case this did not work, you need to setup vim like this: - -To have Vim automatically detect csv files, you need to do the following. - - 1) Create your user runtime directory if you do not have one yet. This - directory needs to be in your 'runtime' path. In Unix this would - typically the ~/.vim directory, while in Windows this is usually your - ~/vimfiles directory. Use :echo expand("~") to find out, what Vim thinks - your user directory is. - To create this directory, you can do: > - - :!mkdir ~/.vim -< - for Unix and > - - :!mkdir ~/vimfiles -< - for Windows. - - 2) In that directory you create a file that will detect csv files. > - - if exists("did_load_csvfiletype") - finish - endif - let did_load_csvfiletype=1 - - augroup filetypedetect - au! BufRead,BufNewFile *.csv,*.dat setfiletype csv - augroup END -< - You save this file as "filetype.vim" in your user runtime diretory: > - - :w ~/.vim/filetype.vim -< - 3) To be able to use your new filetype.vim detection, you need to restart - Vim. Vim will then load the csv filetype plugin for all files whose - names end with .csv. - -============================================================================== -3. Commands *csv-commands* - -The CSV ftplugin provides several Commands. All commands are also provided -with the prefix :CSV (e.g. |:CSVNrColumns|) - - *:CSVWhatColumn* -3.1 WhatColumn *WhatColumn_CSV* --------------- - -If you would like to know, on which column the cursor is, use > - :WhatColumn -< -or > - :CSVWhatColumn -< -Use the bang attribute, if you have a heading in the first line and you want -to know the name of the column in which the cursor is: > - :WhatColumn! -< - *:CSVNrColumns* -3.2 NrColumns *NrColumns_CSV* --------------- - -`:NrColumns` and `:CSVNrColumns` outputs the maximum number of columns -available. It does this by testing the first 10 lines for the number of -columns. This usually should be enough. If you use the '!' attribute, it -outputs the number of columns in the current line. - - *:CSVSearchInColumn* -3.3 SearchInColumn *SearchInColumn_CSV* ------------------- - -Use `:SearchInColumn` or `:CSVSearchInColumn` to search for a pattern within a -specific column. The usage is: > - - :SearchInColumn [<nr>] /{pat}/ -< - -So if you would like to search in Column 1 for the word foobar, you enter > - - :SearchInColumn 1 /foobar/ - -Instead of / as delimiter, you can use any other delimiter you like. If you -don't enter a column, the current column will be used. - - *:CSVHiColumn* -3.4 HiColumn *HiColumn_CSV* ------------- - -`:HiColumn` or `:CSVHiColumn` <nr> can be used to highlight Column <nr>. -Currently the plugin uses the WildMenu Highlight Group. If you would like to -change this, you need to define the variable |g:csv_hiGroup|. - -If you do not specify a <nr>, HiColumn will highlight the column on which the -cursor is. Use > - - :HiColumn! - -to remove any highlighting. - -If you want to automatically highlight a column, see |csv-hicol| - - *:ArrangeColumn* *:CSVArrangeColumn* -3.5 ArrangeColumn *ArrangeColumn_CSV* ------------------ - -If you would like all columns to be visually arranged, you can use the -`:ArrangeColumn` or `:CSVArrangeColumn` command: > - - :[range]ArrangeColumn[!] [<Row>] - -Beware, that this will change your file and depending on the size of -your file may slow down Vim significantly. This is highly experimental. -:ArrangeCommand will try to vertically align all columns by their maximum -column size. While the command is run, a progressbar in the statusline 'stl' -will be shown. - -Use the bang attribute to force recalculating the column width. This is -slower, but especially if you have modified the file, this will correctly -calculate the width of each column so that they can be correctly aligned. If -no column width has been calculated before, the width will be calculated, even -if the '!' has not been given. - -If <Row> is given, will use the Row, to calculate the width, else will -calculate the maximum of at least the first 10,000 rows to calculate the -width. The limit of 10,000 is set to speed up the processing and can be -overriden by setting the "b:csv_arrange_use_all_rows" variable (see below). - -If [range] is not given, it defaults to the current line. - - *csv_arrange_align* -By default, the columns will be right-aligned. If you want a different -alignment you need to specify this through the b:csv_arrange_align variable. -This is a string of flags ('r': right align, 'l': left align, 'c': center -alignment, '.': decimal alignment) where each flag defines the alignment for -a particular column (starting from left). Missing columns will be right aligned. -You can use '*' to repeat the previous value until the end. -So this: > - - :let b:csv_arrange_align = 'lc.' -< -Will left-align the first column, center align the second column, decimal -align the third column and all following columns right align. (Note: decimal -aligning might slow down Vim and additionally, if the value is no decimal -number it will be right aligned). -And this: > - - :let b:csv_arrange_align = 'l*' - -Will left align all columns. - -If you change the alignment parameter, you need to use the "!" attribute, the -next time you run the |:ArrangeCol| command, otherwise for performance -reasons, it won't be considered. - -Note, arranging the columns can be very slow on large files or many columns (see -|csv-slow| on how to increase performance for this command). For large files, -calculating the column width can take long and take a considerable amount of -memory. Therefore, the csv plugin will at most check 10.000 lines for the -width. Set the variable b:csv_arrange_use_all_rows to 1 to use all records: > - - :let b:csv_arrange_use_all_rows = 1 -< -(this could however in the worst case lead to a crash). - -To disable the statusline progressbar set the variable g:csv_no_progress: > - - :let g:csv_no_progress = 1 -< -This will disable the progressbar and slightly improve performance (since no -additional redraws are needed). - -Note: this command does not work for fixed width columns |csv-fixedwidth| - -See also |csv-arrange-autocmd| on how to have vim automatically arrange a CSV -file upon entering it. - -By default, all lines in the file are considered to calculate the column width. -If you want to ignore some lines at the beginning of the file, set the global -variable g:csv_skipfirst to the number of lines to be ignored, e.g. the first -line of the file can be ignored with > - - :let g:csv_skipfirst = 1 -< -Then use the '!' to force recalculation of column width when applying -:ArrangeColumn - - *:CSVUnArrangeColumn* -3.6 UnArrangeColumn *UnArrangeColumn_CSV* ------------------ - -If you would like to undo a previous :ArrangeColumn command, you can use this -`:UnArrangeColumn` or `:CSVUnArrangeColumn` command: > - - :[range]UnArrangeColumn - -Beware, that is no exact undo of the :ArrangeColumn command, since it strips -away all leading blanks for each column. So if previously a column contained -only some blanks, this command will strip all blanks. - -If [range] is given, it defaults to the current line. - - *:CSVDeleteColumn* -3.7 DeleteColumn *DeleteColumn_CSV* ----------------- - -The command `:DeleteColumn` or `:CSVDeleteColumn` can be used to delete a specific column. > - - :DeleteColumn 2 - -will delete column 2. If you use `:DeleteColumn 2-3` columns 2 and 3 will be -deleted. - -If you don't specify a column number, it will delete the column on which the -cursor is. Alternatively, you can also specify a search string. The plugin -will then delete all columns that match the pattern: > - - :DeleteColumn /foobar -< -will delete all columns where the pattern "foobar" matches. - - *:CSVInit* - *:InitCSV* -3.8 CSVInit ------------ -Reinitialize the Plugin. Use this, if you have changed the configuration -of the plugin (see |csv-configuration| ). -If you use the bang (!) attribute, it will keep the b:delimiter configuration -variable. - - *:CSVHeader* -3.9 Header lines *Header_CSV* ----------------- -The `:Header` or `:CSVHeader` command splits the csv-buffer and adds a window, -that holds a small fraction of the csv file. This is useful, if the first line -contains some kind of a heading and you want always to display it. This works -similar to fixing a certain line at the top. As optional argument, you can -give the number of columns from the top, that shall be displayed. By default, -1 is used (You can define your own default by setting the b:csv_headerline -variable, see |csv-header|). Use the '!' to close this window. So this > - - :Header 3 - -opens at the top a split window, that holds the first 3 lines, is fixed -and horizontally 'scrollbind'ed to the csv window and highlighted using the -CSVHeaderLine highlighting. -To close the header window, use > - - :Header! - -Note, this won't work with linebreaks in the column. - -Note also, that if you already have a horizontal header window (|VHeader_CSV|), -this command will close the horizontal Header window. This is because of a -limitation of Vim itself, which doesn't allow to sync the scrolling between -two windows horizontally and at the same time have another window only sync -its scrolling vertically. - -Note: this command does not work for fixed width columns |csv-fixedwidth| - - *:CSVVHeader* *VHeader_CSV* -If you want a vertical header line, use `:VHeader` or `:CSVVHeader`. This works -similar to the |Header_CSV| command, except that it will open a vertical split -window with the first column always visible. It will always open the first -column in the new split window. Use the '!' to close the window. If you -specify a count, that many columns will be visible (default: the first). Add -the bang to the count, if you only want the specific column to be visible. -> - :VHeader 2 -< -This will open a vertical split window containing the first 2 columns, while -> - :VHeader 2! -< -Opens a new vertical split window containing only the 2 second column. - -Note, this won't work with linebreaks in the column. -Note also: this command does not work for fixed width columns |csv-fixedwidth| - - - *:CSVVHeaderToggle* *:CSVHeaderToggle* - *VHeaderToggle_CSV* *HeaderToggle_CSV* -Use the `:HeaderToggle` and `:VHeaderToggle` command to toggle displaying the -horizontal or vertical header line. Alternatively, use `:CSVHeaderToggle` or -`:CSVVHeaderToggle` - - - *:CSVSort* -3.10 Sort *Sort_CSV* ---------- -The command `:Sort` or `:CSVSort` can be used to sort the csv file on a -certain column. If no range is given, is sorts the whole file. Specify the -column number to sort on as argument. Use the '!' attribute to reverse the -sort order. For example, the following command sorts line 1 til 10 on the 3 -column > - - :1,10Sort 3 - -While this command > - - :1,10Sort! 3 - -reverses the order based on column 3. - -The column number can be optionally followed by any of the flags [i], [n], -[x] and [o] for [i]gnoring case, sorting by [n]umeric, he[x]adecimal -or [o]ctal value. - -When no column number is given, it will sort by the column, on which the -cursor is currently. - - *:CSVColumn* -3.11 Copy Column *Copy_CSV* ----------------- -If you need to copy a specific column, you can use the command `:CSVColumn` or -`:Column` > - - :[N]Column [a] - -Copy column N into register a. This will copy all the values, that are -not folded-away (|csv-filter|) and skip comments. - -If you don't specify N, the column of the current cursor position is used. -If no register is given, the default register -|quotequote| is used. - - *:CSVMoveCol* -3.12 Move A Column *MoveCol_CSV* ------------------- -You can move one column to the right of another column by using the -`:CSVMoveColumn` or `:MoveColumn` command > - - :[range]MoveColumn [source] [dest] - -This moves the column number source to the right of column nr destination. If -both arguments are not given, move the column on which the cursor is to the -right of the current last column. If [range] is not given, MoveColumn moves -the entire column, otherwise, it moves the columns only for the lines within -the range, e.g. given that your first line is a header line, which you don't -want to change > - - :2,$MoveColumn 1 $ - -this would move column 1 behind the last column, while keeping the header line -as is. - - - *:CSVSumCol* -3.13 Sum of a Column *SumCol_CSV* --------------------- -You can let Vim output the sum of a column using the `:CSVSumCol` or `:SumCol` -command > - - :[range]SumCol [nr] [/format/] - -This outputs the result of the column <nr> within the range given. If no range -is given, this will calculate the sum of the whole column. If <nr> is not -given, this calculates the sum for the column the cursor is on. Note, that the -delimiter will be stripped away from each value and also empty values won't be -considered. - -By default, Vim uses the a numerical format that uses the '.' as decimal -separator while there is no thousands separator. If youre file contains -the numbers in a different format, you can use the /format/ option to specify -a different thousands separator or a different decimal separator. The format -needs to be specified like this: - /x:y/ -where 'x' defines the thousands separator and y defines the decimal -separator and each one is optional. This means, that > - - :SumCol 1 /:,/ - -uses the default thousands separator and ',' as the decimal separator and > - - :SumCol 2 / :./ - -uses the Space as thousands separator and the '.' as decimal separator. - -Note, if you Vim is compiled without floating point number format (|+float|), -Vim will only aggregate the integer part and therefore won't use the 'y' -argument in the /format/ specifier. - -See also |csv-aggregate-functions| - - *:CSVNewRecord* -3.14 Create new Records *NewRecord_CSV* ------------------------ -If you want to create one or several records, you can use the `:NewRecord` or -`:CSVNewRecord` command: > - - :[range]NewRecord [count] - -This will create in each line given by range [count] number of new empty -records. If [range] is not specified, creates a new line below the line the -cursor is on and if count is not given, it defaults to 1. - - - *:CSVNewDelimiter* -3.15 Change the delimiter *NewDelimiter_CSV* -------------------------- -If you want to change the field delimiter of your file you can use the -`:CSVNewDelimiter` or `:NewDelimiter` command: > - - :NewDelimiter char - -This changes the field delimiter of your file to the new delimiter "char". -Note: Will remove trailing delimiters. - - *:CSVDuplicate* -3.16 Check for duplicate records *Duplicate_CSV* --------------------------------- -If you want to check the file for duplicate records, use the command -`:Duplicate` or `:CSVDuplicate`: > - - :Duplicate columnlist -< - -Columnlist needs to be a numeric comma-separated list of all columns that you -want to check. You can also use a range like '2-5' which means the plugin -should check columns 2,3,4 and 5. - -If the plugin finds a duplicate records, it outputs its line number (but it -only does that at most 10 times). - -3.17 Normal mode commands *csv-mapping* -------------------------- -The csv filetype plugin redefines the following keys (in normal Mode) as: - -<C-Right> or L or W Move [count] field forwards - -<C-Left> or E or H Move [count] field backwards (but see |csv-mapping-H| - for the movement of H). - -<Up> or K Move [count] lines upwards within the same column - -<Down> or J Move [count] lines downwards within the same column - -The upwards and downwards motions try to keep the cursor in the relative -position within the cell when changing lines. That is not a guaranteed to work -and will fail if the upper/lower cell is of a different width than the -starting cell. - -<Enter> Dynamically fold all lines away, that don't match - the value in the current column. See |csv-filter| - - In |Replace-mode| and |Virtual-Replace-mode| does not - create a new row, but instead moves the cursor to the - beginning of the same column, one more line below. - -<Space> Dynamically fold all lines away, that match - the value in the current column. See |csv-filter| - -<BS> Remove last item from the dynamic filter. - See |csv-filter| - - *csv-mapping-H* -Note how the mapping of 'H' differs from 'E' - -H step fields backwards but also stops at where the content of the columns -begins. - -If you look into this example (with the cursor being '|') - - aaa, bbbb,|ccc ` - -Pressing 'H' moves to - - aaa, |bbbb,ccc ` - -Pressing 'H' again moves to - - aaa,| bbbb,ccc ` - -Pressing 'H' again moves to - - |aaa, bbbb,ccc ` - -While with 'E', the cursor moves to: - - aaa,| bbbb,ccc ` - -and pressing 'E' again, it would move directly to - - |aaa, bbbb,ccc ` - - *csv-textobjects* -Also, the csv plugin defines these text-object: - -if Inner Field (contains everything up to the delimiter) -af Outer Field (contains everything up to and including - the delimiter) -iL Inner Line (visually linewise select all lines, that - has the same value at the cursor's column) - -Note, that the <BS>, <CR>, K and J overlap Vim's default mapping for |<CR>|, -|<BS>|, |J| and |K| respectively. Therefore, this functionality has been -mapped to a sane default of <Localleader>J and <LocalLeader>K. If you haven't -changed the |<Leader>| or |<LocalLeader>| variables, those the <Localleader> -is equival to a single backslash '\', e.g. \K would run the lookup function on -the word under the cursor and \J would join this line with the previous line. - -If you want to prevent the mapping of keys, simply set the global variable -g:csv_nomap_<key> to 1, (remove the special characters `<>` and replace `-` by -`_`), e.g. to prevent mapping of <CR> and <C-Left> in csv files, put > - - let g:csv_nomap_cr = 1 - let g:csv_nomap_c_left = 1 -< -into your |.vimrc|. Note, the keyname must be lower case. - -Also the csv plugins follows the general consensus, that when the variable -g:no_plugin_maps or g:no_csv_maps is set, no key will be mapped. - - - *:CSVConvertData* *ConvertData_CSV* -3.18 Converting a CSV File *csv-convert* --------------------------- -You can convert your CSV file to a different format with the `:ConvertData` -or `:CSVConvertData` command > - - ConvertData - -Use the the ! attribute, to convert your data without the delimiter. - -This command will interactively ask you for the definition of 3 variables. -After which it will convert your csv file into a new format, defined by those -3 variables and open the newly created file in a new window. Those 3 variables -define how the text converted. - -First, You need to define what has to be done, before converting your column -data. That is done with the "pre convert" variable. The content of this -variable will be put in front of the new document. - -Second, you define, what has to be put after the converted content of your -column data. This happens with the "post convert" variable. Basically the -contents of this variable will be put after processing the columns. - -Last, the columns need to be converted into your format. For this you can -specify a printf() format like string, that defines how your data will be -converted. You can use '%s' to specify placeholders, which will later be -replaced by the content of the actual column. - -For example, suppose you want to convert your data into HTML, then you first -call the > - - :ConvertData - -At this point, Vim will ask you for input. First, you need to specify, what -needs to be done before processing the data: - - Pre convert text: <html><body><table> ` - -This would specify to put the HTML Header before the actual data can be -processed. If the variable g:csv_pre_convert is already defined, Vim will -already show you its' content as default value. Simply pressing Enter will use -this data. After that, Vim asks, what the end of the converted file needs to -look like: - - Post convert text: </table></body></html> ` - -So here you are defining how to finish up the HTML file. If the variable -g:csv_post_convert is already defined, Vim will already show you its' content -as default value which you can confirm by pressing Enter. Last, you define, -how your columns need to be converted. Again, Vim asks you for how to do that: - - Converted text, use %s for column input: ` - <tr><td>%s</td><td>%s</td><td>%s</td></tr> - -This time, you can use '%s' expandos. They tell Vim, that they need to be -replaced by the actual content of your file. It does by going from the first -column in your file and replacing it with the corresponding %s in that order. -If there are less '%s' expandos than columns in your file, Vim will skip the -columns, that are not used. Again If the variable g:csv_convert is already -defined, Vim will already show you its' content as default value which you can -confirm by pressing Enter. - -After you hit Enter, Vim will convert your data and put it into a new window. -It may look like this: - - <html><body><table> ` - <tr><td>1,</td><td>2,</td><td>3,</td></tr> ` - <tr><td>2,</td><td>2,</td><td>4,</td></tr> ` - </table></body></html> ` - -Note, this is only a proof of concept. A better version of converting your -data to HTML is bundled with Vim (|:TOhtml|). - -But may be you want your data converted into SQL-insert statements. That could -be done like this: > - - ConvertData! -< - Pre convert text: ` - -(Leave this empty. It won't be used). - - Post convert text: Commit; ` - -After inserting the data, commit it into the database. - - Converted text, use %s for column input: ` - Insert into table foobar values ('%s', '%s', %s); ` - -Note, that the last argument is not included within single quotation marks, -since in this case the data is assumed to be integer and won't need to be -quoted for the database. - -After hitting Enter, a new Window will be opened, which might look like this: - - Insert into table foobar values('Foobar', '2', 2011); ` - Insert into table foobar values('Bar', '1', 2011); ` - Commit; ` - -Since the command was used with the bang attribute (!), the converted data -doesn't include the column delimiters. - -Now you can copy it into your database, or further manipulate it. - -3.19 Dynamic filters *csv-filter* --------------------- -If you are on a value and only want to see lines that have the same value in -this column, you can dynamically filter the file and fold away all lines not -matching the value in the current column. To do so, simply press <CR> (Enter). -Now Vim will fold away all lines, that don't have the same value in this -particular row. Note, that leading blanks and the delimiter is removed and the -value is used literally when comparing with other values. If you press <Space> -on the value, all fields having the same value will be folded away. -Pressing <BS> will remove the last item from the dynamic filter. To remove all -filters, keep pressing <BS> until no more filters are present. - -The way this is done is, that the value from the column is extracted and a -regular expression for that field is generated from it. In the end this -regular expression is used for folding the file. - -A subsequent <CR> or <Space> on another value, will add this value to the -current applied filter (this is like using the logical AND between the -currently active filter and the new value). To remove the last item from the -filter, press <BS> (backspace). If all items from the filter are removed, -folding will be disabled. - -If some command messes up the folding, you can use |zX| to have the folding -being reinitialized. - -By default, the first line is assumed to be the header and won't be folded -away. See also |csv-header|. - -If you have set the g:csv_move_folds variable and the file is modifiable, all -folded lines will be moved to the end of the file, so you can view all -non-folded lines as one consecutive area (see also |csv-move-folds|) - - *:CSVFilter* *:Filter* *Filter_CSV* -To see the active filters, you can use the `:Filter` or `:CSVFilter` command. -This will show you a small summary, of what filters are active and looks like -this: - -Nr Match Col Name Value ~ -===================================================== ` -01 - 07 Price 23.10 ` -02 + 08 Qty 10 ` - -This means, there are two filters active. The current active filter is on -column 7 (column name is Price) and all values that match 23.10 will be folded -away AND all values that don't match a value of 10 in the QTY column will also -be folded away. -When removing one item from the filter by pressing <BS>, it will always remove -the last item (highest number in NR column) from the active filter values. - -Note, that depending on your csv file and the number of filters you used, -applying the filter might actually slow down vim, because a complex regular -expression is generated that is applied by the fold expression. Look into the -@/ (|quote_/|) register to see its value. - -Use |zX| to apply the current value of your search register as filter. Use > - - :Filters! - -to reapply all values from the current active filter and fold non-matching -items away. - - *:CSVAnalyze* *Analyze_CSV* -3.20 Analyze a Column *csv-analyze* ---------------------- -If you'd like to know, how the values are distributed among a certain column, -you can use the `:CSVAnalyze` or `:Analyze` command. So > - - :Analyze 3 - -outputs the the distribution of the top 5 values in column 3. This looks like -this: - -Nr Count % Value ~ -============================= ` -01 20 50% 10 ` -02 10 25% 2 ` -03 10 25% 5 ` - -This tells you, that the the value '10' in column 3 occurs 50% of the time -(exactly 20 times) and the other 2 values '2' and '5' occur only 10 times, so -25% of the time. - -In addition, a second argument may be used to specify the number of top values. -So - - :Analyze 3 10 - -outputs the the distribution of the top 10 values in column 3, respectively. - - *:CSVVertFold* *VertFold_CSV* -3.21 Vertical Folding *csv-vertfold* ---------------------- -Sometimes, you want to hide away certain columns to better view only certain -columns without having to horizontally scroll. You can use the `:CSVVertFold` -or `:VertFold` command to hide certain columns: > - - :VertFold [<nr>] -< -This will hide all columns from the first until the number entered. It -currently can't hide single columns, because of the way, syntax highlighting -is used. This command uses the conceal-feature |:syn-conceal| to hide away -those columns. If no nr is given, hides all columns from the beginning till -the current column. - -Use > - :VertFold! - -to display all hidden columns again. - - *:CSVTranspose* *Transpose_CSV* -3.22 Transposing a column *csv-transpose* -------------------------- -Transposing means to exchange rows and columns. You can transpose the csv -file, using the `:CSVTranspose` or `:Transpose` : > - - :[range]Transpose -< -command. If [range] is not given, it will transpose the complete file, -otherwise it will only transpose the lines in the range given. Note, comments -will be deleted and transposing does not work with fixed-width columns. - - *:CSVTabularize* -3.23 Transforming into a table *:CSVTable* *csv-tabularize* ------------------------------- -You can also transform your csv data into a visual table, using the -`:CSVTabularize` or `:CSVTable`: > - - :CSVTabularize -< -command. This will make a frame around your csv data and substitute all -delimiters by '|', so that it will look like a table. - -e.g. consider this data: > -> -First,Second,Third ~ -10,5,2 ` -5,2,10 ` -2,10,5 ` -10,5,2 ` - -This will be transformed into: > - - |---------------------| - | First| Second| Third| - |------|-------|------| - | 10| 5| 2| - | 5| 2| 10| - | 2| 10| 5| - | 10| 5| 2| - |---------------------| - -If your Vim uses an unicode 'encoding', the plugin makes a nice table using -special unicode drawing glyphs (but it might be possible, that those chars are -not being displayed correctly, if either your terminal or the gui font doesn't -have characters for those codepoints). If you use the bang form, each row will -be separated by a line. -You can also visual select a range of lines and use :Tabularize to have only -that range converted into a nice ascii table. Else it try to use the current -paragraph and try to transform it. - -If you use the '!' bang argument, between each row, a line will be drawn. - -In csv files, you can also use the :CSVTabularize command, in different -filetypes you can use the :CSVTable command (and is available as plugin so it -will be available for non-CSV filetypes). - -Set the variable g:csv_table_leftalign=1 if you want the columns to be -leftaligned. - -Note: Each row must contain exactly as many fields as columns. - -This command is available as default plugin. To disable this feature, set the - variable g:csv_disable_table_command to 1: > - - :let g:csv_disable_table_command = 1 -< - *:CSVAddColumn* -3.24 Add new empty columns *AddColumn_CSV* --------------------------- -If you want to add new empty columns to your file you can use the -`:CSVAddColumn` or `:AddColumn` command: > - - :[range]AddColumn [column] [count] - -By default, this works for the whole file, but you can give a different range -to which the AddColumn command applies. If no arguments are given, the new -empty column will be added after the column on which the cursor is. You can -however add as first argument the column number after which the new column -needs to be added. - -Additionally, you can also add a count number to add several columns at once -after the specified column number. Use 0 for the column number, if you want to -add several columns after the current column. - - *:CSVSubstitute* -3.25 Substitute in columns *Substitute_CSV* --------------------------- -If you want to substitute only in specific columns, you can use the -`:CSVSubstitute` or `:Substitute` command: > - - :[range]Substitute [column/]pattern/string[/flags] - -This means in the range and within the given columns replace pattern by -string. This works basically like the |:s| command, except that you MUST use -forward slashes / to delimit the command. The optional part `[column/]` can -take either the form of an address or if you leave it out, substitution will -only happen in the current column. Additionally, you can use the `1,5/` form -to substitute within the columns 1 till 5 or you can even use `1,$` which -means to substitute in each column (so in fact this simplifies to a simple -`:s` command whithin the given range. For the use of `[/flags]` see |:s_flags| -Here are some examples: > - - :%Substitute 1,4/foobar/baz/gce - -Substitutes in the whole file in columns 1 till 4 the pattern foobar by baz -for every match ('g' flag) and asks for confirmation ('c' flag). - - :%S 3,$/(\d\+)/\1 EUR/g - -Substitutes in each column starting from the third each number and appends the -EURO suffix to it. - -3.26 Count Values inside a Column *Count_CSV* ---------------------------------- -You can let Vim output the number of values inside a column using the `:CSVCountCol` -command > - - :[range]CountCol [nr] [distinct] - -This outputs the number of [distinct] values visible in the column [nr] -If [distinct] is not given, count's all values. Note, header rows and folded -rows won't be counted. - -The result is also available in the buffer-local variable `b:csv_result`. - -See also |csv-aggregate-functions| - -3.27 Maximum/Minimum value of a Column *MaxCol_CSV* *MinCol_CSV* ---------------------------------------- -You can let Vim output the 10 maximum/minimum values of a column using the -`:CSVMaxCol` command > - - :[range]MaxCol [nr][distinct] [/format/] - :[range]MinCol [nr][distinct] [/format/] - -This outputs the result of the column <nr> within the range given. If no range -is given, this will calculate the max value of the whole column. If <nr> is not -given, this calculates the sum for the column the cursor is on. Note, that the -delimiter will be stripped away from each value and also empty values won't be -considered. - - *format_number_csv* -By default, Vim uses the a numerical format that uses the '.' as decimal -separator while there is no thousands separator. If youre file contains -the numbers in a different format, you can use the /format/ option to specify -a different thousands separator or a different decimal separator. The format -needs to be specified like this: - /x:y/ -where 'x' defines the thousands separator and y defines the decimal -separator and each one is optional. This means, that > - - :MaxCol 1 /:,/ - -uses the default thousands separator and ',' as the decimal separator and > - - :MaxCol 2 / :./ - -uses the Space as thousands separator and the '.' as decimal separator. - -If [distinct] is given, only returns the number of distinct values. - -The result is also available in the buffer-local variable `b:csv_result`. - -3.28 Average value of a Column *AvgCol_CSV* ------------------------------- -You can let Vim output the value of a column using the `:CSVAvgCol` command > - - :[range]AvgCol [nr] [/format/] - -This outputs the result of the column <nr> within the range given. If no range -is given, this will calculate the average value of the whole column. If <nr> is not -given, this calculates the sum for the column the cursor is on. Note, that the -delimiter will be stripped away from each value and also empty values won't be -considered. - -For the [/format/] part, see |format_number_csv|. - -The result is also available in the buffer-local variable `b:csv_result`. - -See also |csv-aggregate-functions| - -3.29 Variance of a Column *VarCol_CSV* *SmplVarCol* *PopVarCol* -_________________________ - - :[range]PopVarCol [nr] [/format/] - - :[range]SmplVarCol [nr] [/format/] - -Calculate the Population or Sample Variance for the specified column. - -This outputs the result of the column `<nr>` within the range given. If no range -is given, this will calculate the statistical variance of the whole column. If <nr> is not -given, this calculates the variance for the column the cursor is on. Note, that the delimiter -will be stripped away from each value and also empty values won't be considered. - -The result is also available in the buffer-local variable `b:csv_result`. - -For the [/format/] part, see |format_number_csv|. - -3.30 Standard Deviation of a Column *StdDevCol_CSV* *PopStdCol* *SmplStdCol* -___________________________________ - - :[range]PopStdCol [nr] [/format/] - - :[range]SmplStdCol [nr] [/format/] - -Calculate the Population or Sample Standard Deviation for the specified column. - -This outputs the result of the column `<nr>` within the range given. If no range -is given, this will calculate the standard deviation of the whole column. If <nr> is not -given, this calculates the standard deviation for the column the cursor is on. Note, that -the delimiter will be stripped away from each value and also empty values won't be considered. - -The result is also available in the buffer-local variable `b:csv_result`. - -For the [/format/] part, see |format_number_csv|. - - *:CSVDupColumn* -3.31 Duplicate columns *DupColumn_CSV* ----------------------- -If you want to add duplicate an existing column you can use the -`:CSVDupColumn` or `:DupColumn` command: > - - :[range]DupColumn [column] [count] - -By default, this works for the whole file, but you can give a different range -to which the command applies. By default it will duplicate the column on which -the cursor is, but you can add as first argument which column will be duplicated. - -Additionally, you can also provide a count to copy several columns at once. - - *ColumnWidth_CSV* -3.32 Column Width *:CSVColumnWidth* ------------------ -If you want to know the width of each column, you can use the `:CSVColumnWidth` command: > - - :CSVColumnWidth - -This will output the width for each column at the bottom. See also -|CSVWidth()| function - -3.33 Sum of Numbers in a Row *SumRow_CSV* ----------------------------- -You can let Vim output the sum of a field in a row using the `:CSVASumRow` command > - - :[range]SumRow [/format/] - -This outputs the sum of the row [range]. If no range is given, this will -calculate the sum for the current row. Note, that the delimiter will be -stripped away from each value and also empty values won't be considered. - -For the [/format/] part, see |format_number_csv| -============================================================================== -4. CSV Configuration *csv-configuration* - -The CSV plugin tries to automatically detect the field delimiter for your -file, cause although often the file is called CSV (comma separated values), a -semicolon is actually used. By default the plugin tries the following -delimiters: ',', ';', '|', '\t', '^', ':'. -The column separator is stored in the buffer-local variable b:delimiter. This -delimiter is heavily used, because you need it to define a column. Almost all -commands use this variable therefore. - -4.1 Delimiter *csv-delimiter* -------------- -To override the automatic detection of the delimiter and define the separator -manually, use: > - - :let g:csv_delim=',' - -to let the comma be the delimiter. This sets the buffer local delimiter -variable b:delimiter. - -You can also set default delimiter to prevent a warning if no delimiter can -be detected: > - - :let g:csv_default_delim=',' - -If your file does not consist of delimited columns, but rather is a fixed -width csv file, see |csv-fixedwidth| for configuring the plugin appropriately. - -If you changed the delimiter, you should reinitialize the plugin using -|InitCSV| - -Note: the delimiter will be used to generate a regular expression that matches -a column. Internally the plugin uses the very-nomagic setting for the -delimiter, so escaping is not neccessary. - -If you want to override which delimiters are probed automatically, set the -g:csv_delim_test variable like this: > - :let g:csv_delim_test = ',;|' - -This will only make the plugin test the possible delimiters ',', ';' and '|'. -This will also make the automatic detection a bit faster, since it does not -need to test that many delimiters. - -4.2 Column *csv-column* ----------- -The definition, of what a column is, is defined as buffer-local variable -b:col. By default this variable is initialized to: > - - let b:col='\%(\%([^' . b:delimiter . ']*"[^"]*"[^' . b:delimiter . ']*' - \. b:delimiter . '\)\|\%([^' . b:delimiter . ']*\%(' . b:delimiter - \. '\|$\)\)\)' - -This should take care of quoted delimiters within a column. Those should -obviously not count as a delimiter. This regular expression is quite -complex and might not always work on some complex cases (e.g. linebreaks -within a field, see RFC4180 for some ugly cases that will probably not work -with this plugin). - -If you changed the b:delimiter variable, you need to redefine the b:col -variable, cause otherwise it will not reflect the change. To change the -variable from the comma to a semicolon, you could call in your CSV-Buffer -this command: > - - :let b:col=substitute(b:col, ',', ';', 'g') - -Check with :echo b:col, if the definition is correct afterwards. - -You can also force the plugin to use your own defined regular expression as -column. That regular expression should include the delimiter for the columns. -To define your own regular expression, set the g:csv_col variable: > - - let g:csv_col='[^,]*,' - -This defines a column as a field delimited by the comma (where no comma can be -contained inside a field), similar to how |csv-strict| works. - -You should reinitialize the plugin afterwards |InitCSV| - -4.3 Highlighting Group *csv-higroup* ----------------------- -By default the csv ftplugin uses the WildMenu highlighting Group to define how -the |HiColumn| command highlights columns. If you would like to define a -different highlighting group, you need to set this via the g:csv_hiGroup -variable. You can e.g. define it in your |.vimrc|: > - - :let g:csv_hiGroup = "IncSearch" - -You need to restart Vim, if you have changed this variable or use |InitCSV| - -The |hl-Title| highlighting is used for the Header line that is created by the -|Header_CSV| command. If you prefer a different highlighting, set the -g:csv_hiHeader variable to the prefered highlighting: > - - let g:csv_hiHeader = 'Pmenu' -< -This would set the header window to the |hl-Pmenu| highlighting, that is used -for the popup menu. To disable the custom highlighting, simply |unlet| the -variable: > - - unlet g:csv_hiHeader - -You should reinitialize the plugin afterwards |InitCSV| - -4.4 Strict Columns *csv-strict* ------------------- -The default regular expression to define a column is quite complex -(|csv-column|). This slows down the processing and makes Vim use more memory -and it could still not fit to your specific use case. - -If you know, that in your data file, the delimiter cannot be contained inside -the fields quoted or escaped, you can speed up processing (this is quite -noticeable when using the |ArrangeColumn_CSV| command) by setting the -g:csv_strict_columns variable: > - - let g:csv_strict_columns = 1 - -This would define a column as this regex: > - - let b:col = '\%([^' . b:delimiter . ']*' . b:delimiter . '\|$\)' - -Much simpler then the default column definition, isn't it? -See also |csv-column| and |csv-delimiter| - -You can disable the effect if you |unlet| the variable: > - - unlet g:csv_strict_columns - -You should reinitialize the plugin afterwards |InitCSV| - -For example when opening a CSV file you get the Error |E363|: pattern uses -more memory than 'maxmempattern'. In this case, either increase the -'maxmempattern' or set the g:csv_strict_columns variable. - - -4.5 Concealing *csv-syntax* *csv-conceal* --------------- -The CSV plugin comes with a function to syntax highlight csv files. Basically -allt it does is highlight the columns and the header line. - -By default, the delimiter will not be displayed, if Vim supports |conceal| of -syntax items and instead draws a vertical line. If you don't want that, simply -set the g:csv_noconceal variable in your .vimrc > - - let g:csv_no_conceal = 1 - -and to disable it, simply unlet the variable > - - unlet g:csv_no_conceal - -You should reinitialize the plugin afterwards |InitCSV| -Note: You can also set the 'conceallevel' option to control how the concealed -chars will be displayed. - -If you want to customize the syntax colors, you can define your own groups. -The CSV plugin will use already defined highlighting groups, if they are -already defined, otherwise it will define its own defaults which should be -visible with 8, 16, 88 and 256 color terminals. For that it uses the -CSVColumnHeaderOdd and CSVColumnHeaderEven highlight groups for syntax -coloring the first line. All other lines get either the CSVColumnOdd or -CSVColumnEven highlighting. - -In case you want to define your own highlighting groups, you can define your -own syntax highlighting like this in your |.vimrc| > - - hi CSVColumnEven term=bold ctermbg=4 guibg=DarkBlue - hi CSVColumnOdd term=bold ctermbg=5 guibg=DarkMagenta - hi CSVColumnHeaderEven ... - hi CSVColumnHeaderOdd ... - -Alternatively, you can simply link those highlighting groups to some other -ones, you really like: > - - hi link CSVColumnOdd MoreMsg - hi link CSVColumnEven Question -< -If you do not want column highlighting, set the variable -g:csv_no_column_highlight to 1 > - - :let g:csv_no_column_highlight = 1 -< -Note, these changes won't take effect, until you restart Vim. - - -4.6 Newlines *csv-newline* ------------- -RFC4180 allows newlines in double quoted strings. By default, the csv-plugin -won't recognize newlines inside fields. It is however possible to make the -plugin aware of newlines within quoted strings. To enable this, set > - - let g:csv_nl = 1 - -and to disable it again, simply unset the variable > - - unlet g:csv_nl - -It is a good idea to reinitialize the plugin afterwards |InitCSV| - -Note, this might not work correctly in all cases. The syntax highlighting -seems to change on cursor movements. This could possibly be a bug in the -syntax highlighting engine of Vim. Also, |WhatColumn_CSV| can't handle -newlines inside fields and will most certainly be wrong. - -4.7 Highlight column automatically *csv-hicol* ----------------------------------- -You can let vim automatically highlight the column on which the cursor is. -This works by defining an |CursorMoved| autocommand to always highlight the -column, when the cursor is moved in normal mode. Note, this does not update -the highlighting, if the Cursor is moved in Insert mode. To enable this, -define the g:csv_highlight_column variable like this > - - let g:csv_highlight_column = 'y' - -and to disable it again, simply unset the variable > - - unlet g:csv_highlight_column - -It is a good idea to reinitialize the plugin afterwards |InitCSV| - -4.8 Fixed width columns *csv-fixedwidth* ------------------------ -Sometimes there are no real columns, but rather the file is fixed width with -no distinct delimiters between each column. The CSV plugin allows you to -handle such virtual columns like csv columns, if you define where each column -starts. - -Note: Except for |ArrangeColumn_CSV| and the |Header_CSV| commands, all -commands work in either mode. Those two commands won't do anything in the case -of fixedwidth columns, since they don't really make sense here. - -4.8.1 Manual setup ------------------- -You can do this, by setting the buffer-local variable -b:csv_fixed_width like this > - - let b:csv_fixed_width="1,5,9,13,17,21" - -This defines that each column starts at multiples of 4. Be sure, to issue -this command in the buffer, that contains your file, otherwise, it won't -have an effect, since this is a buffer-local option (|local-option|) - -After setting this variable, you should reinitialize the plugins using -|InitCSV| - - *CSVFixed* -4.8.2 Setup using a Wizard --------------------------- -Alternatively, you can setup the fixed width columns using the :CSVFixed -command. This provides a simple wizard to select each column. If you enter -the command: > - :CSVFixed -< -The first column will be highlighted and Vim outputs: -<Cursor>, <Space>, <ESC>, <BS>, <CR>... -This means, you can now use those 5 keys to configure the fixed-width columns: - - <Cursor> Use Cursor Left (<Left>) and Cursor Right (<Right>) to move the - highlighting bar. - <Space> If you press <Space>, this column will be fixed and remain - highlighted and there will be another bar, you can move using - the Cursor keys. This means this column will be considered to be - the border between 2 fixed with columns. - <ESC> Abort - <BS> Press the backspace key, to remove the last column you fixed with - the <Space> key. - <CR> Use Enter to finish the wizard. This will use all fixed columns - to define the fixed width columns of your csv file. The plugin - will be initialized and syntax highlighting should appear. - -Note: This only works, if your Vim has the 'colorcolumn' option available -(This won't work with Vim < 7.3 and also not with a Vim without +syntax -feature). - - -4.9 CSV Header lines *csv-header* --------------------- -By default, dynamic filtering |csv-filter| will not fold away the first line. -If you don't like that, you can define your header line using the variable -b:csv_fold_headerline, e.g. > - - let b:csv_headerline = 0 - -to disable, that a header line won't be folded away. If your header line -instead is on line 5, simply set this variable to 5. This also applies to the -|Header_CSV| command. - -4.10 Number format *csv-nrformat* ------------------- -When using the |SumCol_CSV| command, you can specify a certain number format -using the /x:y/ argument. You can however also configure the plugin to detect -a different number format than the default number format (which does not -support a thousands separator and uses the '.' as decimal separator). - -To specify a different thousands separator by default, use > - - let b:csv_thousands_sep = ' ' - -to have the space use as thousands separator and > - - let b:csv_decimal_sep = ',' - -to use the comma as decimal separator. - -4.11 Move folded lines *csv-move-folds* ----------------------- -If you use dynamic filters (see |csv-filter|), you can configure the plugin to -move all folded lines to the end of the file. This only happens if you set the -variable > - - let g:csv_move_folds = 1 -< -and the file is modifiable. This let's you see all non-folded records as a -consecutive area without being disrupted by folded lines. - -4.12 Using comments *csv-comments* -------------------- -Strictly speaking, in csv files there can't be any comments. You might however -still wish to comment or annotate certain sections in your file, so the CSV -plugin supports Comments. - -Be default, the CSV plugin will use the 'commentstring' setting to identify -comments. If this option includes the '%s' it will consider the part before -the '%s' as leading comment marker and the part behind it as comment -delimiter. - -You can however define your own comment marker, using the variable -g:csv_comment. Like with the 'commentstring' setting, you can use '%s' -expandos, that will denote where the actual comment text belongs. To define -your own comment string, put this in your |.vimrc| > - - :let g:csv_comment = '#' -< -Which will use the '#' sign as comment leader like in many scripting -languages. - -After setting this variable, you should reinitialize the plugins using -|InitCSV| - - *csv-foldtext* -By default, the csv plugin sets the 'foldtext' option. If you don't want this, -set the variable `g:csv_disable_fdt` in your |.vimrc| > - - :let g:csv_disable_fdt = 1 - -4.13 Size and performance considerations *csv-size* ----------------------------------------- -By default, the csv plugin will analyze the whole file to determine which -delimiter to use. Beside specifying the the actual delimiter to use -(|csv-delimiter|) you can restrict analyzing the plugin to consider only a -certain part of the file. This should make loading huge csv files a log -faster. To only consider the first 100 rows set the `g:csv_start` and -`g:csv_end` variables in your |.vimrc| like this > - - :let g:csv_start = 1 - :let g:csv_end = 100 - -Also note, you can use the Large File plugin -(http://www.drchip.org/astronaut/vim/index.html#LARGEFILE) which however will -disable syntax highlighting and the filetype commands for very large csv files -(by default larger than 100 MB). - -See also |csv-slow| -============================================================================== -5. Functions *CSV-Functions* - -The csv plugins also defines some functions, that can be used for scripting -when a csv file is open - -5.1 CSVPat() *CSVPat()* ------------- -CSVPat({column}[, {pattern}]) - -This function returns the pattern for the selected column. If only columns is -given, returns the regular expression used to search for the pattern '.*' in -that column (which means the content of that column). Alternatively, an -optional pattern can be given, so the return string can be directly feeded to -the |/| or |:s| command, e.g. type: > - - :s/<C-R>=CSVPat(3, 'foobar')<cr>/baz - -where the <C-R> means pressing Control followed by R followed by = -(see |c_CTRL-R_=|). A prompt will apear, with the '=' as the first character -on which you can enter expressions. - -In this case enter CSVPat(3, 'foobar') which returns the pattern to search for -the string 'foobar' in the third column. After you press enter, the returned -pattern will be put after the :s command so you can directly enter / and the -substitute string. - -5.2 CSVField(x,y[, orig]) *CSVField()* -------------------------- -This function returns the field at index (x,y) (starting from 1). If the -parameter orig is given, returns the column "as is" (e.g. including delimiter -and leading and trailing whitespace, otherwise that will be stripped.) - -5.3 CSVCol([name]) *CSVCol()* ------------------- -If the name parameter is given, returns the name of the column, else returns -the index of the current column, starting at 1. - -5.4 CSVSum(col, fmt, startline, endline) *CSVSum()* ----------------------------------------- -Returns the sum for column col. Uses fmt to parse number format (see -|:CSVSumCol|) startline and endline specify the lines to consider, if empty, -will be first and last line. - -5.5 CSVCount(col, fmt, startline, endline[, distinct]) *CSVCount()* ------------------------------------------------------- -Returns the count of values for column col. If the optional parameter -[distinct] is given, only returns the distinct number of values. - -5.6 CSVMax(col, fmt, startline, endline) *CSVMax()* ------------------------------------------------------- -Returns the 10 largest values for column col. - -5.7 CSVMin(col, fmt, startline, endline) *CSVMin()* ------------------------------------------------------- -Returns the 10 smallest values for column col. - -5.8 CSVAvg(col, fmt, startline, endline) *CSVAvg()* ------------------------------------------------------- -Returns the average value for column col. - -5.9 CSVWidth([silent]) *CSVWidth()* ------------------------------------------------------- -Returns a list with the width for each column. If the first argument is given -and non-zero, do not output warning message about the number of records used -to calculate the width (else it might output something like: - CSV: File too large, only checking the first 10000 rows for the width -), default: 1 - -============================================================================== -6. CSV Tips and Tricks *csv-tips* - -Here, there you'll find some small tips and tricks that might help when -working with CSV files. - -6.1 Statusline *csv-stl* --------------- -Suppose you want to include the column, on which the cursor is, into your -statusline. You can do this, by defining in your .vimrc the 'statusline' like -this: > - - function MySTL() - if has("statusline") - hi User1 term=standout ctermfg=0 ctermbg=11 guifg=Black guibg=Yellow - let stl = ... - if exists("*CSV_WCol") - let csv = '%1*%{&ft=~"csv" ? CSV_WCol() : ""}%*' - else - let csv = '' - endif - return stl.csv - endif - endfunc - set stl=%!MySTL() -< - -This will draw in your statusline right aligned the current column and max -column (like 1/10), if you are inside a CSV file. The column info will be -drawn using the User1 highlighting (|hl-User1|), that has been defined in the -second line of the function. In the third line of your function, put your -desired 'statusline' settings as |expression|. Note the section starting with -'if exists(..)' guards against not having loaded the filetype plugin. - -Note: vim-airline (https://github.com/bling/vim-airline) by default supports -the csv plugin and enables a nice little csv statusline which helps for -navigating within a csv file. For details, see the Vim-Airline documentation. - - *CSV_WCol()* -The CSV_WCol() function controls, what will be output. In the simplest case, -when no argument is given, it simply returns on which column the cursor is. -This would look like '1/10' which means the cursor is on the first of 10 -columns. If you rather like to know the name of the column, simply give as -parameter to the function the string "Name". This will return the column name -as it is printed on the first line of that column. This can be adjusted, to -have the column name printed into the statusline (see |csv-stl| above) by -replacing the line > - - let csv = '%1*%{&ft=~"csv" ? CSV_WCol() : ""}%*' -< -by e.g. - - let csv = '%1*%{&ft=~"csv" ? CSV_WCol("Name") . " " . CSV_WCol() : ""}%*' - -which will output "Name 2/10" if the cursor is in the second column -which is named "Name". - -6.2 Slow CSV plugin *csv-slow* -------------------- -Processing a csv file using |ArrangeColumn_CSV| can be quite slow, because Vim -needs to calculate the width for each column and then replace each column by -itself widened by spaces to the optimal length. Unfortunately, csv files tend -to be quite big. Remember, for a file with 10,000 lines and 50 columns Vim -needs to process each cell, which accumulates to 500,000 substitutions. It -might take some time, until Vim is finished. - -You can speed up things a little bit, if you omit the '!' attribute to the -|ArrangeColumn| (but this will only work, if the width has been calculated -before, e.g. by issuing a :1ArrangeColumn command to arrange only the first -line. Additionally you can also configure how this command behaves by setting -some configuration variables. - -Also note, using dynamic filters (|csv-filter|), can slow down Vim -considerably, since they internally work with complex regular expressions, and -if you have a large file, containing many columns, you might hit a performance -penalty (especially, if you want to filter many columns). It's best to avoid -those functions if you are using a large csv file (so using strict columns -|csv-strict| might help a little and also setting 're' to 1 might also -alleviate it a little). - - -6.3 Defining custom aggregate functions *csv-aggregate-functions* ---------------------------------------- -The CSV plugin already defines the |SumCol_CSV| command, to let you calculate -the sum of all values of a certain column within a given range. This will -consider all values within the range, that are not folded away (|csv-filter|), -and also skip comments and the header lines. The delimiter will be deleted -from each field. - -But it may be, that you don't need the sum, but would rather want to have the -average of all values within a certain column. You can define your own -function and let the plugin call it for a column like this: - - 1) You define your own custom function in the after directory of your - vim runtime path |after-directory| (see also #2 below) > - - fun! My_CSV_Average(col) - let sum=0 - for item in a:col - let sum+=item - endfor - return sum/len(a:col) - endfun -< - This function takes a list as argument, and calculates the average for - all items in the list. You could also make use of Vim's |eval()| - function and write your own Product function like this > - - fun! My_CSV_Product(col) - return eval(join(a:col, '*')) - endfun -< - - 2) Now define your own custom command, that calls your custom function for - a certain column > - - command! -buffer -nargs=? -range=% AvgCol - \ :echo csv#EvalColumn(<q-args>, - \ "My_CSV_Average", <line1>,<line2>) -< - This command should best be put into a file called csv.vim and save - it into your ~/.vim/after/ftplugin/ directory. Create directories - that don't exist yet. For Windows, this would be the - $VIMRUNTIME/vimfiles/after/ftplugin directory. - - 3) Make sure, your |.vimrc| includes a filetype plugin setting like this > - - filetype plugin on -< - This should make sure, that all the necessary scripts are loaded by - Vim. - - After restarting Vim, you can now use your custom command definition - :AvgCol. Use a range, for the number of lines you want to evaluate and - optionally use an argument to specify which column you want to be - evaluated > - - :2,$AvgCol 7 -< - This will evaluate the average of column seven (assuming, line 1 is the - header line, which should not be taken into account). - - Note: this plugin already defines an average function. - -6.4 Autocommand on opening/closing files *csv-arrange-autocmd* ----------------------------------------- -If you want your CSV files to always be displayed like a table, you can -achieve this using the |ArrangeColumn_CSV| command and some autocommands. -Define these autocommands in your |.vimrc| > - - aug CSV_Editing - au! - au BufRead,BufWritePost *.csv :%ArrangeColumn - au BufWritePre *.csv :%UnArrangeColumn - aug end - -Upon Entering a csv file, Vim will visually arrange all columns and before -writing, those columns will be collapsed again. The BufWritePost autocommand -makes sure, that after the file has been written successfully, the csv file -will again be visually arranged. - -You can also simply set the variable > - - let g:csv_autocmd_arrange = 1 -< -in your vimrc and an autocmd will be installed, that visually arranges your -csv file whenever you open them for editing. Alternatively, you can restrict -this setting to files below a certain size. For example, if you only want to -enable this feature for files smaller than 1 MB, put this into your |.vimrc| > - - let g:csv_autocmd_arrange = 1 - let g:csv_autocmd_arrange_size = 1024*1024 - -Note, this is highly experimental and especially on big files, this might -slow down Vim considerably. - -6.5 Syntax error when opening a CSV file *csv-syntax-error* ----------------------------------------- -If you see this error: > - - CSV Syntax:Invalid column pattern, using default pattern \%([^,]*,\|$\) -< -This happens usually, when the syntax script is read before the filetype -plugin, so the plugin did not have a chance to setup the column delimiter -correctly. - -The easy way to fix it, is to make sure the :syntax on (|:syn-on|) statement -comes after the :filetype plugin (|:filetype-plugin-on|) statement in your -|.vimrc| - -Alternatively, you can simply call |InitCSV| and ignore the error. - -Note: It could also be caused by lazy loading feature by a vim plugin -manager. For example this line might also cause it: > - - Plug 'https://github.com/chrisbra/csv.vim', { 'for' : 'csv' } -< - -The fix would then be: > - - Plug 'https://github.com/chrisbra/csv.vim' - -6.6 Calculate new columns *csv-calculate-column* -------------------------- -Suppose you have a table like this: - -Index;Value1;Value2~ -1;100;3 ` -2;20;4 ` - -And you need one more column, that is the calculated product of column 2 and -3, you can make use of the provided |CSVField()| function using a -|sub-replace-expression| of an |:s| command. In this case, you would do this: > - - :2,3s/$/\=printf("%s%.2f", b:delimiter, - (CSVField(2,line('.'))+0.0)*(CSVField(3,line('.'))+0.0/ - -Note: Enter as single line. The result will be this: > - -Index;Value1;Value2~ -1;100;3;300.00 ` -2;20;4;80.00 ` - -6.7 Using the result of an evaluation in insert mode *b:csv_result* ----------------------------------------------------- -The result of the last evaluation like e.g. |SumCol_CSV| will be available in -the buffer-local variable `b:csv_result`. This allows to easily enter the -result in a new new cell while in insert mode, using |i_CTRL-R|(e.g. in insert -mode press Ctrl-R followed by "=b:csv_result<enter>". - -You can also easily copy and paste it into e.g. the system clipboard using > - :let @+=b:csv_result - -============================================================================== -7. CSV Changelog *csv-changelog* - -see CHANGELOG.md in root directory of the plugin. - -# vim:ft=help - -endif |