Database Reference
In-Depth Information
4
5
When it comes down to filtering on a certain pattern within a certain column, we can
use either
csvgrep
,
awk
, or, of course,
csvsql
. For example, to exclude all the bills of
which the party size was 4 or less:
$
csvgrep -c size -i -r
"[1-4]"
tips.csv | csvlook
|--------+------+--------+--------+------+--------+-------|
| bill | tip | sex | smoker | day | time | size |
|--------+------+--------+--------+------+--------+-------|
| 29.8 | 4.2 | Female | No | Thur | Lunch | 6 |
| 34.3 | 6.7 | Male | No | Thur | Lunch | 6 |
| 41.19 | 5.0 | Male | No | Thur | Lunch | 5 |
| 27.05 | 5.0 | Female | No | Thur | Lunch | 6 |
| 29.85 | 5.14 | Female | No | Sun | Dinner | 5 |
| 48.17 | 5.0 | Male | No | Sun | Dinner | 6 |
| 20.69 | 5.0 | Male | No | Sun | Dinner | 5 |
| 30.46 | 2.0 | Male | Yes | Sun | Dinner | 5 |
| 28.15 | 3.0 | Male | Yes | Sat | Dinner | 5 |
|--------+------+--------+--------+------+--------+-------|
Both
awk
and
csvsql
can also do numerical comparisons. For example, to get all the
bills above 40 USD on a Saturday or a Sunday:
$
< tips.csv awk -F,
'($1 > 40.0) && ($5 ~ /S/)'
| csvlook
|--------+------+--------+-----+-----+--------+----|
| 48.27 | 6.73 | Male | No | Sat | Dinner | 4 |
|--------+------+--------+-----+-----+--------+----|
| 44.3 | 2.5 | Female | Yes | Sat | Dinner | 3 |
| 48.17 | 5.0 | Male | No | Sun | Dinner | 6 |
| 50.81 | 10.0 | Male | Yes | Sat | Dinner | 3 |
| 45.35 | 3.5 | Male | Yes | Sun | Dinner | 3 |
| 40.55 | 3.0 | Male | Yes | Sun | Dinner | 2 |
| 48.33 | 9.0 | Male | No | Sat | Dinner | 4 |
|--------+------+--------+-----+-----+--------+----|
The
csvsql
solution is more verbose but is also more robust, as it uses the names of
the columns instead of their indexes:
$
< tips.csv csvsql --query
"SELECT * FROM stdin "
\
>
"WHERE bill > 40 AND day LIKE '%S%'"
| csvlook
|--------+------+--------+--------+-----+--------+-------|
| bill | tip | sex | smoker | day | time | size |
|--------+------+--------+--------+-----+--------+-------|
| 48.27 | 6.73 | Male | 0 | Sat | Dinner | 4 |
| 44.3 | 2.5 | Female | 1 | Sat | Dinner | 3 |
| 48.17 | 5.0 | Male | 0 | Sun | Dinner | 6 |
| 50.81 | 10.0 | Male | 1 | Sat | Dinner | 3 |
| 45.35 | 3.5 | Male | 1 | Sun | Dinner | 3 |
| 40.55 | 3.0 | Male | 1 | Sun | Dinner | 2 |
| 48.33 | 9.0 | Male | 0 | Sat | Dinner | 4 |
|--------+------+--------+--------+-----+--------+-------|
