Zenn
🎛️

[csvq] インストールとマッチング

2020/10/01に公開
tool
csvq
idea

published_at: 2019-03-16 18:34

はじめに

ExcelやスプレッドシートのVLOOKUPでのマッチングを
手早くできるほど慣れてないのでこちらでするなり

前提

  • 環境
    • macOS Majave 10.14.3
    • VS Code 1.32.1
      • Rainbow CSV Extension
      • Excel Viewer Extension

インストール [1]

  • go インストール
    • brew install go
      ❯ go version
go version go1.12.1 darwin/amd64
  • csvq バイナリ配置（ path の効いてるところ）
    • wget https://github.com/mithrandie/csvq/releases/download/v1.9.1/csvq-v1.9.1-darwin-amd64.tar.gz
    • tar xzf csvq-v1.9.1-darwin-amd64.tar.gz
    • cd csvq-v1.9.1-darwin-amd64
    • cp -p csvq /usr/local/bin/
      ❯ csvq
csvq interactive shell
Press Ctrl+D or execute "EXIT;" to terminate this shell.

ちょっと使ってみた

Datas

  • users.csv

    id,name,age,tel
1,user1,20,01011112222
2,user2,21,01011113333
3,user3,22,01011114444
4,user4,23,01011115555
5,user5,24,01011116666
6,user6,25,01011117777

  • score.csv

    id,user_id,score,date
1,2,100,2019/3/12 10:00
2,4,200,2019/3/12 10:15
3,6,300,2019/3/12 10:30

simple SELECT

> SELECT * FROM users;
+----+--------+-----+-------------+
| id |  name  | age |     tel     |
+----+--------+-----+-------------+
| 1  | user1  | 20  | 01011112222 |
| 2  | user2  | 21  | 01011113333 |
| 3  | user3  | 22  | 01011114444 |
| 4  | user4  | 23  | 01011115555 |
| 5  | user5  | 24  | 01011116666 |
| 6  | user6  | 25  | 01011117777 |
+----+--------+-----+-------------+

 > SELECT * FROM score;
+----+---------+-------+------------------+
| id | user_id | score |       date       |
+----+---------+-------+------------------+
| 1  | 2       | 100   | 2019/3/12 10:00  |
| 2  | 4       | 200   | 2019/3/12 10:15  |
| 3  | 6       | 300   | 2019/3/12 10:30  |
+----+---------+-------+------------------+

simple JOIN

> SELECT u.name, u.age, s.score, s.date FROM users u JOIN score s ON u.id = s.user_id;
+--------+-----+-------+------------------+
|  name  | age | score |       date       |
+--------+-----+-------+------------------+
| user2  | 21  | 100   | 2019/3/12 10:00  |
| user4  | 23  | 200   | 2019/3/12 10:15  |
| user6  | 25  | 300   | 2019/3/12 10:30  |
+--------+-----+-------+------------------+

> SELECT u.name, u.age, s.score, s.date FROM users u LEFT JOIN score s ON u.id = s.user_id;
+--------+-----+-------+------------------+
|  name  | age | score |       date       |
+--------+-----+-------+------------------+
| user1  | 20  | NULL  |       NULL       |
| user2  | 21  | 100   | 2019/3/12 10:00  |
| user3  | 22  | NULL  |       NULL       |
| user4  | 23  | 200   | 2019/3/12 10:15  |
| user5  | 24  | NULL  |       NULL       |
| user6  | 25  | 300   | 2019/3/12 10:30  |
+--------+-----+-------+------------------+

CSV export (simple JOIN)

❯ csvq -f csv -o left_join.csv "SELECT u.name, u.age, s.score, s.date FROM users u LEFT JOIN score s ON u.id = s.user_id;"

name,age,score,date
user1,20,,
user2,21,100,2019/3/12 10:00
user3,22,,
user4,23,200,2019/3/12 10:15
user5,24,,
user6,25,300,2019/3/12 10:30

refs

脚注

  1. いつからかはわからないですが、macOS で unofficial ながら brew install csvq でいけるようです。 ↩︎

Discussion