Database Reference
In-Depth Information
It should be noted that the flexibility of the
WHERE
clause in an SQL query cannot be
easily matched with other command-line tools, as SQL can operate on dates and sets
and form complex combinations of clauses.
Merging Columns
Merging columns is useful for when the values of interest are spread over multiple
columns. This may happen with dates (where year, month, and day could be separate
columns) or names (where the first name and last name are separate columns). Let's
consider the second situation.
The input CSV is a list of contemporary composers. Imagine our task is to combine
the first name and the last name into a full name. We'll present four different
approaches for this task:
sed
,
awk
,
cols
/
tr
, and
csvsql
. Let's have a look at the input
CSV:
$
< names.csv csvlook
|-----+-----------+------------+-------|
| id | last_name | first_name | born |
|-----+-----------+------------+-------|
| 1 | Williams | John | 1932 |
| 2 | Elfman | Danny | 1953 |
| 3 | Horner | James | 1953 |
| 4 | Shore | Howard | 1946 |
| 5 | Zimmer | Hans | 1957 |
|-----+-----------+------------+-------|
The first approach,
sed
, uses an expression with two parts. The first part is to replace
the header and the second part is a regular expression with back references applied to
the second row onwards:
$
< names.csv sed -re
'1s/.*/id,full_name,born/g;'
\
>
'2,$s/(.*),(.*),(.*),(.*)/\1,\3 \2,\4/g'
| csvlook
|-----+---------------+-------|
| id | full_name | born |
|-----+---------------+-------|
| 1 | John Williams | 1932 |
| 2 | Danny Elfman | 1953 |
| 3 | James Horner | 1953 |
| 4 | Howard Shore | 1946 |
| 5 | Hans Zimmer | 1957 |
|-----+---------------+-------|
The
awk
approach looks as follows:
$
< names.csv awk -F,
'BEGIN{OFS=","; print "id,full_name,born"}'
\
>
'{if(NR > 1) {print $1,$3" "$2,$4}}'
| csvlook
|-----+---------------+-------|
| id | full_name | born |
|-----+---------------+-------|
| 1 | John Williams | 1932 |
