Skip to content

Instantly share code, notes, and snippets.

@gwpl

gwpl/boxes.csv

Last active January 31, 2017 15:51
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save gwpl/4edb432e125662c34a69cfca6c853db3 to your computer and use it in GitHub Desktop.
Save gwpl/4edb432e125662c34a69cfca6c853db3 to your computer and use it in GitHub Desktop.
Download ZIP
csvsql_query.sh is example usage of `csvsql` direct sql execution on `.csv` files examples, inspired by https://source.opennews.org/en-US/articles/eleven-awesome-things-you-can-do-csvkit/
Raw
boxes.csv
id name description
33 Music CDs Box with "Music CDs"
44 Movies "Box" Box with Movies
55 Toys Toys
Raw
csvsql_query.output.csv
id name box_id item_id id name description
23 Toy T 55 23 55 Toys Toys
45 Movie M1 44 45 44 Movies "Box" Box with Movies
44 Movie M2 44 44 44 Movies "Box" Box with Movies
43 Movie M3 44 43 44 Movies "Box" Box with Movies
67 Music CD 1 33 67 33 Music CDs Box with "Music CDs"
68 Music CD 2 33 68 33 Music CDs Box with "Music CDs"
69 Music CD 3 33 69 33 Music CDs Box with "Music CDs"
70 Music CD 4 33 70 33 Music CDs Box with "Music CDs"
Raw
csvsql_query.output.csvlook.txt
| id | name | box_id | item_id | id_2 | name_2 | description |
| -- | ---------- | ------ | ------- | ---- | ------------ | -------------------- |
| 23 | Toy T | 55 | 23 | 55 | Toys | Toys |
| 45 | Movie M1 | 44 | 45 | 44 | Movies "Box" | Box with Movies |
| 44 | Movie M2 | 44 | 44 | 44 | Movies "Box" | Box with Movies |
| 43 | Movie M3 | 44 | 43 | 44 | Movies "Box" | Box with Movies |
| 67 | Music CD 1 | 33 | 67 | 33 | Music CDs | Box with "Music CDs" |
| 68 | Music CD 2 | 33 | 68 | 33 | Music CDs | Box with "Music CDs" |
| 69 | Music CD 3 | 33 | 69 | 33 | Music CDs | Box with "Music CDs" |
| 70 | Music CD 4 | 33 | 70 | 33 | Music CDs | Box with "Music CDs" |
Raw
csvsql_query.sh
csvsql \
--query "select * from
items as i join items_boxes as ib
on (i.id = ib.item_id)
join boxes as b on (ib.box_id = b.id)" \
items.csv boxes.csv items_boxes.csv \
| tee csvsql_query.output.csv \
| csvlook | tee csvsql_query.output.csvlook.txt
Raw
csvsql_query_minimaloutput.output.csv
name name
Toy T Toys
Movie M1 Movies "Box"
Movie M2 Movies "Box"
Movie M3 Movies "Box"
Music CD 1 Music CDs
Music CD 2 Music CDs
Music CD 3 Music CDs
Music CD 4 Music CDs
Raw
csvsql_query_minimaloutput.output.csvlook.txt
| name | name_2 |
| ---------- | ------------ |
| Toy T | Toys |
| Movie M1 | Movies "Box" |
| Movie M2 | Movies "Box" |
| Movie M3 | Movies "Box" |
| Music CD 1 | Music CDs |
| Music CD 2 | Music CDs |
| Music CD 3 | Music CDs |
| Music CD 4 | Music CDs |
Raw
csvsql_query_minimaloutput.sh
csvsql \
--query "select i.name, b.name from
items as i join items_boxes as ib
on (i.id = ib.item_id)
join boxes as b on (ib.box_id = b.id)" \
items.csv boxes.csv items_boxes.csv \
| tee csvsql_query_minimaloutput.output.csv \
| csvlook | tee csvsql_query_minimaloutput.output.csvlook.txt
Raw
items.csv
id name
23 Toy T
45 Movie M1
44 Movie M2
43 Movie M3
67 Music CD 1
68 Music CD 2
69 Music CD 3
70 Music CD 4
Raw
items_boxes.csv
box_id item_id
44 43
44 44
44 45
55 23
33 67
33 68
33 69
33 70
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment