csvq
SQL-like query language for csv
csvq is a command line tool to operate CSV files.
You can read, update, delete CSV records with SQL-like query.
You can also execute multiple operations sequentially in managed transactions by passing a procedure or using the interactive shell.
In the multiple operations, you can use variables, cursors, temporary tables, and other features.
Features
-
CSV File Operation
- Select Query
- Insert Query
- Update Query
- Replace Query
- Delete Query
- Create Table Query
- Alter Table Query
-
Cursor
-
Temporary Table
-
Transaction Management
-
Support loading data from Standard Input
-
Support following file formats
- CSV
- TSV
- LTSV
- Fixed-Length Format
- JSON
-
Support following file encodings
- UTF-8
- UTF-16
- Shift_JIS
JSON Format supports only UTF-8.
Reference Manual
Installation
Install executable binary
- Download an archive file from release page.
- Extract the downloaded archive and add a binary file in it to your path.
Build from source
Requirements
Go 1.11 or later (ref. Getting Started - The Go Programming Language)
Build with one of the following ways
Use go get
$ go get github.com/mithrandie/csvq
Build with strict dependencies
$ go get -d github.com/mithrandie/csvq- Change directory to
$GOPATH/src/github.com/mithrandie/csvq $ env GO111MODULE=on go install
Install using package manager (Unofficial)
Installing using a package manager does not ensure that you always get the latest version, but it may make installation and updating easier.
macOS or Linux
- Install homebrew (ref. The missing package manager for macOS (or Linux) — Homebrew)
$ brew install csvq
Usage
# Simple query
csvq "select id, name from `user.csv`"
csvq "select id, name from user"
# Specify data delimiter as tab character
csvq -d "\t" "select count(*) from `user.csv`"
# Load no-header-csv
csvq --no-header "select c1, c2 from user"
# Load from redirection or pipe
csvq "select * from stdin" < user.csv
cat user.csv, csvq "select *"
# Load from Fixed-Length Format
cat /var/log/syslog, csvq -n -i fixed -m "[15, 24, 124]" "select *"
# Split lines with spaces automatically
ps, csvq -i fixed -m spaces "select * from stdin"
# Output in JSON format
csvq -f json "select integer(id) as id, name from user"
# Output to a file
csvq -o new_user.csv "select id, name from user"
# Load statements from file
$ cat statements.sql
VAR @id := 0;
SELECT @id := @id + 1 AS id,
name
FROM user;
$ csvq -s statements.sql
# Execute statements in the interactive shell
$ csvq
csvq > UPDATE users SET name = 'Mildred' WHERE id = 2;
1 record updated on "/home/mithrandie/docs/csv/users.csv".
csvq > COMMIT;
Commit: file "/home/mithrandie/docs/csv/users.csv" is updated.
csvq > EXIT;
# Show help
csvq -h
More details >> https://mithrandie.github.io/csvq