Viewing CSV Files

I find CSV (or, to be more general, DSV) files difficult to read on Unix because you can't tell which column a value is in. So I always end up importing them into a spreadsheet which is a pain. Here is an example of a small pipe-delimited file containing book data:

Title|Author|CoAuthor|Year|ISBN
Carrie|Stephen King||1974|978-0385086950
The Human Web|William McNeill|Robert McNeill|2003|978-0393051797

It would be a lot easier to read, if I could convert the file into dictionaries of key:value pairs in order to see which columns the values were referring to, like this:

Title:Carrie
Author:Stephen King
CoAuthor:
Year:1974
ISBN:978-0385086950

Title:The Human Web
Author:William McNeill
CoAuthor:Robert McNeill
Year:2003
ISBN:978-0393051797

So, I wrote the following Bash script to convert a delimiter separated file into a collection of dictionaries. It uses awk to read the first row, which contains the column names, split it and store it in an array. It then prints out the remaining rows along with their column names which are looked up from the array.

#! /bin/bash
# CSV Viewer
# Usage: csv [-d delim] filename
# default delimiter is pipe.
#
# Input file:
# h1|h2|h3
# v1|v2|v3
# w1|w2|w3
#
# Output:
# h1: v1
# h2: v2
# h3: v3
#
# h1: w1
# h2: w2
# h3: w3

delim=|
while getopts "d:" OPTION
do
   case $OPTION in
     d) delim=$OPTARG; shift $((OPTIND-1)) ;;
   esac
done

if [ $# -eq 0 ]
then
    echo "Usage: csv [-d delim] filename" >&2
    exit 1
fi
awk -F "$delim" '{if(NR==1)split($0,arr);else for(i=1;i<=NF;i++)print arr[i]":"$i;print "";}' "$1"

Running the script:

sharfah@starship:~> csv.sh -d '|' file
Title:Carrie
Author:Stephen King
CoAuthor:
Year:1974
ISBN:978-0385086950

Title:The Human Web
Author:William McNeill
CoAuthor:Robert McNeill
Year:2003
ISBN:978-0393051797

Subtract Two Dates [Unix]

In this post, I will describe how you can subtract two dates (which are in the form yyyyMMdd) to get the time between them. My approach involves first parsing both dates in order to extract the year, month and day, converting them to seconds using floating point math and finally subtracting one from the other.

Bash doesn't support floating point arithmetic, but I won't let that stop me. I will use awk instead. Another possible candidate is bc.

1. Parsing the date
We need to extract the year, month and day from our date which is in yyyyMMdd e.g. 20080818. The year is composed of the first four characters, the month is the next two and the day, the final two. We can do this using awk's substr function as shown below. substr(s,m,n) returns the n-character substring of s that begins at position m.

today=20080818
echo $today | awk '{
 year=substr($1,1,4);
 month=substr($1,5,2);
 day=substr($1,7,2);
 }'

If you don't like awk, you can also get substrings using the shell's expr command:

today=20080818
year=`/usr/ucb/expr substr $today 1 4`
month=`/usr/ucb/expr substr $today 5 2`
day=`/usr/ucb/expr substr $today 7 2`

2. Converting to seconds
We will use the formula below to give us the number of days since 1/1/1970:
(year-1970)*365.25 + month*30.5 + day

Now convert the days to seconds by multiplying the days by 24 * 60 * 60:
((year-1970)*365.25 + month*30.5 + day) * 24 * 60 * 60

3. Subtracting the times
Once we have both dates in seconds, we can subtract them. Since we're still dealing with floating point we should use awk or bc for precision.

echo $seconds1 $seconds2 | awk '{print $1 - $2}'

or

echo $seconds1 - $seconds2 | bc

Putting it all together
Here is the complete shell script which takes two dates and returns the number of seconds between them:

#!/usr/bin/bash

date1=$1
date2=$2

#give the dates to awk
echo $date1 $date2 | awk '{

#parse
year1=substr($1,1,4);
month1=substr($1,5,2);
day1=substr($1,7,2);

year2=substr($2,1,4);
month2=substr($2,5,2);
day2=substr($2,7,2);

#get seconds
secs1=((year1 - 1970)*365.25+(month1*30.5)+day1)*24*60*60;
secs2=((year2 - 1970)*365.25+(month2*30.5)+day2)*24*60*60;

#subtract
print secs1 - secs2;
}'

As you can see, all of the computation is in awk! So we could put all of our awk code into a separate file called subtractDates.awk, for instance, and run it on the command line like this: echo 20080830 20080818 | awk -f subtractDates.awk

Now that we have the number of seconds between two dates, we can convert that to days, months and years using a similar approach if we have to.