/
ft-csv.txt
1714 lines (1282 loc) · 65.8 KB
/
ft-csv.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
*ft-csv.txt* For Vim version 7.3 Last Change: Thu, 27 Mar 2014
Author: Christian Brabandt <cb@256bit.org>
Version: 0.30
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|
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|
5. Functions....................................|CSV-Functions|
5.1 CSVPat()................................|CSVPat()|
5.2 CSVField()..............................|CSVField()|
5.3 CSVCol()................................|CSVCol()|
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|
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
==============================================================================
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[!]
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.
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 [range] is not given, it defaults to the current line.
By default, the columns will be righ-aligned. If you want them to be
left-aligned, set the buffer variable b:csv_arrange_leftalign = 1 for that
particular buffer, e.g. >
:let b:csv_arrange_leftalign = 1
<
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). To prevent you
from accidently changing your csv file, the buffer will be set 'readonly'
afterwards. Note: this command does not work for fixed width columns
|csv-fixedwidth|
See also |csv-arrange-autocmd| on how to have vim automaticaly arrange a CSV
file upon entering it.
*: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 don't specify a column number, it will delete the column on which the
cursor is.
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.
*:CSVInitCSV*
3.8 InitCSV *InitCSV*
-----------
Reinitialize the Plugin. Use this, if you have changed the configuration
of the plugin (see |csv-configuration| ).
*: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 youre 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 itsself, 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).
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.
Instead of a column, you can give the flag 'n' to have it sort numerically.
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 numerica 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".
*: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 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
<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 `
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)
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, e.g. to prevent mapping of <CR> in csv files, put >
let g:csv_nomap_cr = 1
<
into your |.vimrc|. Note, the keyname must be lower case.
*: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 then 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.
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.
*: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.
To prevent name clashes with the Tabular plugin, :CSVTabularize is defined as
an alias to the same function.
To be able to create ascii tables in all filetypes, this command is also
available for non-CSV files as :Table
Note: Each row must contain exactly as many fields as columns.
*: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. You 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 bascially 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.
==============================================================================
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. 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 plugin 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.
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 reinitiliaze the plugin using
|InitCSV|
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