csvt is a command line tool for processing CSV.
csvt consists of multiple subcommands.
- add Add column.
- choose Choose columns.
- concat Concat CSV files.
- count Count the number of records.
- exclude Exclude rows by included in another CSV file.
- group Aggregate by group.
- filter Filter rows by condition.
- head Show head few rows.
- header Show header.
- include Filter rows by included in another CSV file.
- join Join CSV files.
- remove Remove columns.
- rename Rename columns.
- replace Replace values.
- slice Slice specified range of rows.
- sort Sort rows.
- split Split into multiple CSV files.
- transform Transform format.
- unique Extract unique rows.
Flags related to the CSV format are available in each subcommand as common flags.
Global Flags:
--delim string (optional) CSV delimiter. The default is ','
--quote string (optional) CSV quote. The default is '"'
--sep string (optional) CSV record separator. The default is CRLF.
--allquote (optional) Always quote CSV fields. The default is to quote only the necessary fields.
--encoding string (optional) CSV encoding. The default is utf-8.
--bom (optional) CSV with BOM. When reading, the BOM will be automatically removed without this flag.
For example, when dealing with TSV files, change the delimiter to a tab as shown below.
$ csvt count -i INPUT --delim "\t"
The encodings that can be specified with --encoding are as follows.
- utf-8
- ibm866
- iso-8859-2
- iso-8859-3
- iso-8859-4
- iso-8859-5
- iso-8859-6
- iso-8859-7
- iso-8859-8
- iso-8859-8-i
- iso-8859-10
- iso-8859-13
- iso-8859-14
- iso-8859-15
- iso-8859-16
- koi8-r
- koi8-u
- macintosh
- windows-874
- windows-1250
- windows-1251
- windows-1252
- windows-1253
- windows-1254
- windows-1255
- windows-1256
- windows-1257
- windows-1258
- x-mac-cyrillic
- gbk
- gb18030
- big5
- euc-jp
- iso-2022-jp
- shift_jis
- euc-kr
- utf-16be
- utf-16le
It is defined by W3C.
Create a new CSV file by adding column to the input CSV file.
The following values can be set for the new column.
- Fixed value.
- Same value as another column.
- Value by template. As a template engine, text/template will be used.
csvt add -i INPUT -c ADD_COLUMN [--value VALUE | --template TEMPLATE | --copy-column FROM_COLUMN] -o OUTPUT
Usage:
csvt add [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column string Name of the column to add.
--value string (optional) Fixed value to set for the added column.
--template string (optional) Template for the value to be set for the added column.
--copy-column string (optional) Name of the column from which the value is copied.
-o, --output string Output CSV file path.
-h, --help help for add
The contents of input.csv.
col1,col2
1,a
2,b
3,c
Add "col3" as a new column. Set "x" as a fixed value.
$ csvt add -i input.csv -c col3 --value x -o output.csv
The contents of the created output.csv.
col1,col2,col3
1,a,x
2,b,x
3,c,x
Add "col1x" by copying "col1".
$ csvt add -i input.csv -c col1x --copy-column col1 -o output.csv
col1,col2,col1x
1,a,1
2,b,2
3,c,3
Use the template to add a column that combines the values of "col1" and "col2".
$ csvt add -i input.csv -c col3 --template "{{.col1}}-{{.col2}}" -o output.csv
col1,col2,col3
1,a,1-a
2,b,2-b
3,c,3-c
Please refer to the following for template syntax.
Create a new CSV file by choosing columns from the input CSV file.
csvt choose -i INPUT -c COLUMN1 ... -o OUTPUT
Usage:
csvt choose [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column stringArray Name of the column to choose.
-o, --output string Output CSV file path.
-h, --help help for choose
The contents of input.csv.
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,2
2,Hanako,21,1
3,Smith,30,2
4,Jun,22,4
Create output.csv by choosing "Name" and "Age" from input.csv.
$ csvt choose -i input.csv -c Name -c Age -o output.csv
The contents of the created output.csv.
Name,Age
"Taro, Yamada",10
Hanako,21
Smith,30
Jun,22
Create a new CSV file by concat the two CSV files.
Check the column names and concat them into the same column.
csvt concat -i INPUT1 -i INPUT2 [-i INPUT3 ...] -o OUTPUT
Usage:
csvt concat [flags]
Flags:
-i, --input stringArray Input CSV files path.
-o, --output string Output CSV file path.
-h, --help help for concat
The contents of input1.csv.
ID,Name
1,name1
2,name2
The contents of input2.csv.
Name,ID
name3,3
name4,4
Concat input1.csv and input2.csv.
$ csvt concat -i input1.csv -i input2.csv -o output.csv
The contents of the created output.csv.
ID,Name
1,name1
2,name2
3,name3
4,name4
Count the number of records in CSV file.
csvt count -i INPUT [-c COLUMN] [--header]
Usage:
csvt count [flags]
Flags:
-i, --input string CSV file path.
-c, --column string (optional) Name of the column to be counted. Only those with values will be counted.
--header (optional) Counting including header. The default is to exclude header.
-h, --help help for count
The contents of input.csv.
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,2
2,Hanako,21,1
3,Smith,30,
4,Jun,22,4
Count the number of records.
$ csvt count -i input.csv
4
Count the number of lines, including headers.
$ csvt count -i input.csv --header
5
Counts the number of records for which a value exists in "CompanyID".
$ csvt count -i input.csv -c CompanyID
3
Create a new CSV file by exclude on the rows included in another CSV file.
csvt exclude -i INPUT -c COLUMN -a ANOTHER [--column-another COLUMN2] -o OUTPUT
Usage:
csvt exclude [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column string Name of the column to use for exclude.
-a, --another string Another CSV file path. Exclude by included in this CSV file.
--column-another string (optional) Name of the column to use for exclude in the another CSV file. Specify if different from the input CSV file.
-o, --output string Output CSV file path.
-h, --help help for exclude
The contents of input.csv.
col1,col2
1,A
2,B
3,C
4,D
The contents of another.csv.
col1,col3
2,2
3,2
Exclude by "col1" values in another.csv.
$ csvt exclude -i input.csv -c col1 -a another.csv -o output.csv
The contents of the created output.csv.
col1,col2
1,A
4,D
Group by the value of the specified column and perform aggregation.
Currently, only counting is supported.
It's like GROUP BY + COUNT in SQL.
csvt group -i INPUT -c COLUMN [--count-column COUNT_COLUMN] -o OUTPUT
Usage:
csvt group [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column string Name of the column to use for grouping.
--count-column string (optional) Column name for the number of records. (default "COUNT")
-o, --output string Output CSV file path.
-h, --help help for group
The contents of input.csv.
col1,col2
1,B
2,B
3,A
4,D
5,C
6,D
7,D
8,E
9,A
10,D
Group the rows by the value of col2 and aggregate the number of rows.
$ csvt group -i input.csv -c col2 -o output.csv
The contents of the created output.csv.
col2,COUNT
A,2
B,2
C,1
D,4
E,1
Create a new CSV file by filtering the input CSV file to rows that match the conditions.
csvt filter -i INPUT [[-c COLUMN1] ...] [--equal VALUE | --regex REGEX | --equal-column COLUMN] [--not] -o OUTPUT
Usage:
csvt filter [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column stringArray (optional) Name of the column to use for filtering. If not specified, all columns are targeted.
--equal string (optional) Filter by matching value. If neither --equal nor --regex nor --equal-column is specified, it will filter by those with values.
--regex string (optional) Filter by regular expression.
--equal-column string (optional) Filter by other column value.
--not (optional) Filter by non-matches.
-o, --output string Output CSV file path.
-h, --help help for filter
The contents of input.csv.
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,1
2,Hanako,21,1
3,yamada,30,
4,Jun,22,2
Create output.csv by filter by non-empty values of "CompanyID" from input.csv.
$ csvt filter -i input.csv -c CompanyID -o output.csv
The contents of the created output.csv.
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,1
2,Hanako,21,1
4,Jun,22,2
You can also filter by matching the specified value.
Specify a value by using --equal.
$ csvt filter -i input.csv -c CompanyID --equal 2 -o output.csv
UserID,Name,Age,CompanyID
4,Jun,22,2
You can use --not to invert the filtering target.
$ csvt filter -i input.csv -c CompanyID --equal 2 --not -o output.csv
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,1
2,Hanako,21,1
3,yamada,30,
You can also filter by matching with other column.
The column can be specified with --equal-column.
$ csvt filter -i input.csv -c UserID --equal-column CompanyID -o output.csv
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,1
Regular expressions can also be used.
Use --regex to specify a regular expression.
$ csvt filter -i input.csv -c Name --regex [Yy]amada -o output.csv
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,1
3,yamada,30,
Please refer to the following for the syntax of regular expressions.
Show the first few rows.
It will be shown in table format.
csvt head -i INPUT [-n NUMBER]
Usage:
csvt head [flags]
Flags:
-i, --input string Input CSV file path.
-n, --number int The number of records to show. If not specified, it will be the first 10 rows. (default 10)
-h, --help help for head
The contents of input.csv.
UserID,Name,Age
1,"Taro, Yamada",10
2,Hanako,21
3,Smith,30
4,Jun,22
5,Kevin,10
6,Bob,
7,Jackson,51
8,Harry,22
9,Olivia,32
10,Aiko,35
11,Kaede,9
12,Sakura,12
13,Momoka,16
$ csvt head -i input.csv
+--------+--------------+-----+
| UserID | Name | Age |
+--------+--------------+-----+
| 1 | Taro, Yamada | 10 |
| 2 | Hanako | 21 |
| 3 | Smith | 30 |
| 4 | Jun | 22 |
| 5 | Kevin | 10 |
| 6 | Bob | |
| 7 | Jackson | 51 |
| 8 | Harry | 22 |
| 9 | Olivia | 32 |
| 10 | Aiko | 35 |
+--------+--------------+-----+
$ csvt head -i input.csv -n 2
+--------+--------------+-----+
| UserID | Name | Age |
+--------+--------------+-----+
| 1 | Taro, Yamada | 10 |
| 2 | Hanako | 21 |
+--------+--------------+-----+
Show the header of CSV file.
csvt header -i INPUT
Usage:
csvt header [flags]
Flags:
-i, --input string CSV file path.
-h, --help help for header
The contents of input.csv.
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,2
2,Hanako,21,1
3,Smith,30,2
4,Jun,22,4
$ csvt header -i input.csv
UserID
Name
Age
CompanyID
Create a new CSV file by filtering on the rows included in another CSV file.
csvt include -i INPUT -c COLUMN -a ANOTHER [--column-another COLUMN2] -o OUTPUT
Usage:
csvt include [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column string Name of the column to use for filtering.
-a, --another string Another CSV file path. Filter by included in this CSV file.
--column-another string (optional) Name of the column to use for filtering in the another CSV file. Specify if different from the input CSV file.
-o, --output string Output CSV file path.
-h, --help help for include
The contents of input.csv.
col1,col2
1,A
2,B
3,C
4,D
The contents of another.csv.
col1,col3
2,2
3,2
Filter by "col1" values in another.csv.
$ csvt include -i input.csv -c col1 -a another.csv -o output.csv
The contents of the created output.csv.
col1,col2
2,B
3,C
Join CSV files.
Using the first CSV file as a base, join the contents of the second CSV file to create a new CSV file.
It is similar to EXCEL's VLOOKUP.
csvt join -1 INPUT1 -2 INPUT2 -c COLUMN [--column2 COLUMN2] -o OUTPUT [--usingfile] [--norecord]
Usage:
csvt join [flags]
Flags:
-1, --first string First CSV file path.
-2, --second string Second CSV file path.
-c, --column string Name of the column to use for joining.
--column-second string (optional) Name of the column to use for joining in the second CSV file. Specify if different from the first CSV file.
-o, --output string Output CSV file path.
--usingfile (optional) Use temporary files for joining. Use this when joining large files that will not fit in memory.
--norecord (optional) No error even if there is no record corresponding to sencod CSV.
-h, --help help for join
The contents of input1.csv.
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,2
2,Hanako,21,1
3,Smith,30,2
4,Jun,22,4
The contents of input2.csv.
CompanyID,CompanyName
1,CompanyA
2,CompanyB
3,CompanyC
4,"AAA Inc"
Join by "CompanyID".
$ csvt join -1 input1.csv -2 input2.csv -c CompanyID -o output.csv
The contents of the created output.csv.
UserID,Name,Age,CompanyID,CompanyName
1,"Taro, Yamada",10,2,CompanyB
2,Hanako,21,1,CompanyA
3,Smith,30,2,CompanyB
4,Jun,22,4,AAA Inc
If the input2.csv looks like the following and there is no corresponding "CompanyID", an error will occur.
CompanyID,CompanyName
1,CompanyA
2,CompanyB
If you don't want to raise an error even if there is no value, specify --norecord
$ csvt join -1 input1.csv -2 input2.csv -c CompanyID -o output.csv --norecord
If the column name in the second CSV file is different from that in the first CSV file, specify it with --column-second.
$ csvt join -1 input1.csv -2 input2.csv -c CompanyID --column2 ID -o output.csv
If the second CSV file you specify is so large that it would take up too much memory on your PC, specify --usingfile.
If you specify --usingfile, it will use a temporary file for joining instead of memory.
$ csvt join -1 input1.csv -2 input2.csv -c CompanyID -o output.csv --usingfile
Create a new CSV file by remove columns from the input CSV file.
csvt remove -i INPUT -c COLUMN1 ... -o OUTPUT
Usage:
csvt remove [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column stringArray Name of the column to remove.
-o, --output string Output CSV file path.
-h, --help help for remove
The contents of input.csv.
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,2
2,Hanako,21,1
3,Smith,30,2
4,Jun,22,4
Create output.csv by remove "Name" and "Age" from input.csv.
$ csvt remove -i input.csv -c Name -c Age -o output.csv
The contents of the created output.csv.
UserID,CompanyID
1,2
2,1
3,2
4,4
Create a new CSV file by rename columns from the input CSV file.
csvt rename -i INPUT -c BEFORE_COLUMN1 ... -a AFTER_COLUMN1 ... -o OUTPUT
Usage:
csvt rename [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column stringArray Name of column before renaming.
-a, --after stringArray Name of column after renaming.
-o, --output string Output CSV file path.
-h, --help help for rename
The contents of input.csv.
UserID,Name,Age,CompanyID
1,"Taro, Yamada",10,2
2,Hanako,21,1
Create output.csv by renmae "UserID" to "ID" and "CompanyID" to "Company" from input.csv.
$ csvt rename -i input.csv -c UserID -a ID -c CompanyID -a Company -o output.csv
The contents of the created output.csv.
ID,Name,Age,Company
1,"Taro, Yamada",10,2
2,Hanako,21,1
Create a new CSV file by replacing the values in the input CSV file.
Regular expression are used for replace.
csvt replace -i INPUT [[-c COLUMN1] ...] -r REGEX -t REPLACEMENT -o OUTPUT
Usage:
csvt replace [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column stringArray (optional) Name of the column to replace. If not specified, all columns are targeted.
-r, --regex string The regular expression to replace.
-t, --replacement string The string after replace.
-o, --output string Output CSV file path.
-h, --help help for replace
The contents of input.csv.
col1,col2,col3
aa,abc,a1
bb,aabb,99
Create output.csv by replacing a with x in all columns.
$ csvt replace -i input.csv -r a -t x -o output.csv
The contents of the created output.csv.
col1,col2,col3
xx,xbc,x1
bb,xxbb,99
You can specify the target column with -c.
Replacing a with x in "col1" and "col2".
$ csvt replace -i input.csv -c col1 -c col2 -r a -t x -o output.csv
col1,col2,col3
xx,xbc,a1
bb,xxbb,99
You can also use the capture group as -t.
$ csvt replace -i input.csv -c col3 -r ".*?([0-9]+)" -t "#$1" x -o output.csv
col1,col2,col3
aa,abc,#1
bb,aabb,#99
Please refer to the following for the syntax of regular expressions.
Create a new CSV file by slicing the specified range of rows from the input CSV file.
csvt slice -i INPUT [-s START] [-e END] -o OUTPUT
Usage:
csvt slice [flags]
Flags:
-i, --input string Input CSV file path.
-s, --start int The number of the starting row. If not specified, it will be the first row. (default 1)
-e, --end int The number of the end row. If not specified, it will be the last row. (default 2147483647)
-o, --output string Output CSV file path.
-h, --help help for slice
The contents of input.csv.
ID,Name
1,name1
2,name2
3,name3
4,name4
5,name5
Slice the second through fourth records.
$ csvt slice -i input.csv -s 2 -e 4 -o output.csv
The contents of the created output.tsv.
ID,Name
2,name2
3,name3
4,name4
The -s and -e can be omitted.
If you want to extract the first row, it is sufficient to specify only -e, as shown below.
$ csvt slice -i input.csv -e 1 -o output.csv
ID,Name
1,name1
Creates a new CSV file from the input CSV file by sorting by the values in the specified columns.
csvt sort -i INPUT -c COLUMN1 ... [--desc] [--number] -o OUTPUT [--usingfile]
Usage:
csvt sort [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column stringArray Name of the column to use for sorting.
--desc (optional) Sort in descending order. The default is ascending order.
--number (optional) Sorts as a number. The default is to sort as a string.
-o, --output string Output CSV file path.
--usingfile (optional) Use temporary files for sorting. Use this when sorting large files that will not fit in memory.
-h, --help help for sort
The contents of input.csv.
col1,col2
02,a
10,b
01,a
11,c
20,b
Sort by "col1".
$ csvt sort -i input.csv -c col1 -o output.csv
The contents of the created output.tsv.
col1,col2
01,a
02,a
10,b
11,c
20,b
By default, it is sorted as a string. For example, it could look like this
col1
1
12
123
2
21
3
If you want to sort as a number, specify --number.
$ csvt sort -i input.csv -c col1 --number -o output.csv
col1
1
2
3
12
21
123
Split the CSV file by the specified number of rows.
csvt split -i INPUT -r ROWS -o OUTPUT
Usage:
csvt split [flags]
Flags:
-i, --input string Input CSV file path.
-r, --rows int Maximum number of rows.
-o, --output string Output CSV file base path. If you specify "output.csv", the file will be output as "output-1.csv" "output-2.csv" ...
It is also possible to specify the position of the embedded serial number in "%d".
-h, --help help for split
The contents of input.csv.
ID,Name
1,name1
2,name2
3,name3
4,name4
5,name5
Split into multiple CSV files every two rows.
$ csvt split -i input.csv -r 2 -o output.csv
It will be split into the following three files.
output-1.csv
ID,Name
1,name1
2,name2
output-2.csv
ID,Name
3,name3
4,name4
output-3.csv
ID,Name
5,name5
Transform the format of CSV file.
csvt transform -i INPUT -o OUTPUT [--out-delim DELIMITER] [--out-quote QUOTE] [--out-sep SEPARATOR] [--out-allquote] [out-encoding ENCODING] [--out-bom]
Usage:
csvt transform [flags]
Flags:
-i, --input string Input CSV file path.
-o, --output string Output CSV file path.
--out-delim string (optional) Output CSV delimiter. The default is ','
--out-quote string (optional) Output CSV quote. The default is '"'
--out-sep string (optional) Output CSV record separator. The default is CRLF.
--out-allquote (optional) Always quote output CSV fields. The default is to quote only the necessary fields.
--out-encoding string (optional) Output CSV encoding. The default is utf-8.
--out-bom (optional) Output CSV with BOM.
-h, --help help for transform
The contents of input.csv.
UserID,Name
1,"Taro, Yamada"
2,Hanako
Create output.tsv by transform input.csv to a TSV file.
$ csvt transform -i input.csv -o output.tsv --out-delim "\t"
The contents of the created output.tsv.
UserID Name
1 Taro, Yamada
2 Hanako
Use common flag --delim to transform TSV file back to CSV file.
$ csvt transform -i output.tsv -o output2.csv --delim "\t"
Extracts unique records using the value of a specified columns.
csvt unique -i INPUT -c COLUMN1 ... -o OUTPUT
Usage:
csvt unique [flags]
Flags:
-i, --input string Input CSV file path.
-c, --column stringArray Name of the column to use for extract unique rows.
-o, --output string Output CSV file path.
-h, --help help for unique
The contents of input.csv.
col1,col2
1,2
2,1
1,1
1,2
Extract the unique row in "col1".
$ csvt unique -i input.csv -c col1 -o output.tsv
The contents of the created output.tsv.
col1,col2
1,2
2,1
You can also specify multiple columns.
Extract unique rows with "col1" and "col2".
$ csvt unique -i input.csv -c col1 -c col2 -o output.tsv
col1,col2
1,2
2,1
1,1
csvt is implemented in golang and runs on all major platforms such as Windows, Mac OS, and Linux.
You can download the binaries for each OS from the links below.
MIT