#!/bin/bash

################################################################################
# Module .... evconv
# Desc ...... EventVue xls/xlsx/csv to tsv converter script
# Date ...... 2008-Dec-28
# Author .... Neil Simon
#
# Usage ..... evconv {fileToConvert.xls | fileToConvert.xlsx | fileToConvert.csv}
# Ex ........ evconv fileToConvert.xls
################################################################################

################################################################################
# Tools Installation
################################################################################

# 1. Install OdfConverter
#    Doc ........ http://www.oooninja.com/2008/01/convert-openxml-docx-etc-in-linux-using.html
#    Download ... http://download.go-oo.org/red-carpet/ooo-680/sled-10-sp-i586/odf-converter-1.1-7.i586.rpm
#
#    Notes ...... Converts xlsx files to ods (an itermediary file).
#                 The ods file can be converted to csv.
#
#    Installation procedure:
#    - Download to desktop
#    - Open a terminal window
#    - Cd to the /Desktop ... cd ~/Desktop
#    - Unpack the rpm ....... rpm2cpio odf-converter*rpm | cpio -ivd
#    - Copy the binary ...... sudo cp usr/lib/ooo-2.0/program/OdfConverter /usr/bin
#    - Remove the rpm ....... rm odf-converter*rpm
#    - Optional symlink ..... cd /usr/lib
#                             ln -s libtiff.so.4 lib libbtiff.so.3
#
# 2. Install unoconv
#
#    Notes ...... Runs as a background process as an OpenOffice file format converter.
#                 Converts ods file to csv.
#
#    Installation procedure:
#    - sudo apt-get install unoconv
# 
# 3. Install xls2csv
#
#    Notes ...... Converts xls to csv.
#
#    Installation procedure:
#    - sudo apt-get install xls2csv 
################################################################################

################################################################################
# Script 
################################################################################

# Grab argcount ... ex. "evconv fileToConvert.xls" yields 1
ARGCOUNT=$#

# MUST have 1 arg exactly, otherwise, display usage
if [ $ARGCOUNT -ne 1 ]; then
    echo Usage: evconv {fileToConvert.xls \| fileToConvert.xlsx}
    echo Ex:    evconv fileToConvert.xls
    echo Creates a tab-delimited-file with a .tsv extension.

# File specified MUST exist
elif [ ! -f $1 ]; then
    echo ERROR: File $1 does not exist.

else
    # Grab filename arg and parse it into useful var names
    FILE_TO_CONVERT_FULLNAME=$1
    FILE_TO_CONVERT_NAME=${1%%.*}
    FILE_TO_CONVERT_EXTENSION=${1##*.}

    # If csv, convert to tsv
    if [ $FILE_TO_CONVERT_EXTENSION == "csv" ]; then
        # Convert csv -> tsv
        echo Converting $FILE_TO_CONVERT_FULLNAME to $FILE_TO_CONVERT_NAME.tsv

        # The sed regexp 's/,/\t/g' takes the csv and outputs a tsv-formatted stream
        cat $FILE_TO_CONVERT_FULLNAME | sed 's/,/\t/g' > $FILE_TO_CONVERT_NAME.tsv
        echo $FILE_TO_CONVERT_FULLNAME converted to $FILE_TO_CONVERT_NAME.tsv successfully.

    # If xls, convert to tsv
    elif [ $FILE_TO_CONVERT_EXTENSION == "xls" ]; then
        # Convert xls -> intermediate csv -> tsv
        echo Converting $FILE_TO_CONVERT_FULLNAME to $FILE_TO_CONVERT_NAME.tsv

        # The sed regexp 's/,/\t/g' takes the csv and outputs a tsv-formatted stream
        xls2csv $FILE_TO_CONVERT_FULLNAME | sed 's/,/\t/g' > $FILE_TO_CONVERT_NAME.tsv
        echo $FILE_TO_CONVERT_FULLNAME converted to $FILE_TO_CONVERT_NAME.tsv successfully.

    # If xlsx, convert to tsv
    elif [ $FILE_TO_CONVERT_EXTENSION == "xlsx" ]; then
        # If the OpenOffice background process is not running...
        if !(ps ax | grep -v grep | grep 'soffice.bin' > /dev/null) ; then
            # Start it -- and give it time to load
            echo Starting OpenOffice background conversion process
            unoconv -l &
            sleep 5
        fi

        # Convert xlsx -> ods (suppress stdout, stderr)
        echo Converting $FILE_TO_CONVERT_FULLNAME to $FILE_TO_CONVERT_NAME.ods
        OdfConverter /i $FILE_TO_CONVERT_FULLNAME > /dev/null 2>&1

        # Convert ods -> csv
        echo Converting $FILE_TO_CONVERT_NAME.ods to $FILE_TO_CONVERT_NAME.csv 
        unoconv -f csv $FILE_TO_CONVERT_NAME.ods

        # No longer need the temporary ods file
        rm -f $FILE_TO_CONVERT_NAME.ods

        # Convert csv -> tsv
        echo Converting $FILE_TO_CONVERT_NAME.csv to $FILE_TO_CONVERT_NAME.tsv
        cat $FILE_TO_CONVERT_NAME.csv | sed 's/,/\t/g' > $FILE_TO_CONVERT_NAME.tsv
        echo $FILE_TO_CONVERT_FULLNAME converted to $FILE_TO_CONVERT_NAME.tsv successfully.

        # No longer need the temporary csv file
        rm -f $FILE_TO_CONVERT_NAME.csv

    # Neither xls or xlsx specified... error out
    else
        echo ERROR: File extension MUST be xls or xlsx.
    fi
fi

echo

################################################################################
# End
################################################################################