One more step towards freedom.
This gives me an ICS ==> CSV translation that renders
EVENT STARTDATE STARTTIME ENDDATE ENDTIME COMMENT
in chronological order.
=====
I know about logging programs, billable time gigs, and the lot. There are lots of them out there, you can Google for them.
But I wanted something that would just simply log what I do. And, if I decide to take a nap, record idle time, and in the morning tell me what time I really went to sleep.
Simple stuff.
Not.
The only thing I 've found like that is a wonderful app for Windows, but I'm not on Windows. I've given him a salute there, but this is here: Linux.
Eventually I found KTimeTracker. It is indeed very cool, collects what you do by the window title, when you started, when you stopped. And has a bunch of reports on how much time you spent on this or that.
But it doesn't give you the times...
It does have a File > Edit History feature that shows exactly what I want, but there is no way to show it or export it.
KTimeTracker does export an .ics file. I had written a BASH script to restart it with a new .ics file. The script is called time_new, which I run every day when I start work. This starts a new .ics file and calls a routine called time_awk.
Now the rubber is starting to meet the road.
I had written this some while ago to parse the .ics file into single line log and event rows, but it was still a mess.
So, then doing what I knew how to do, I delved into writing some Windows Visual Basic macros to further refine it into that which I wanted, that of KTimeTracker's History screen, that could then be saved as an Excel spreadsheet.
But this is tedious. First, I almost never use Windows except for this, then there is the bloatware of starting up Windows 7 (under VirtualBox in my case), and then the actual parsing takes forever under VirtualBox. After the macro runs you get the hourglass forever unless you switch out of the guest to the host Linux platform and back. I haven't taken the time to try to figure out that one.
What a PITA.
So I finally bit the bullet. It took me a couple of days, but I have now constructed an AWK file that does exactly what I want:
- Translate an ICS file exported by KTimeTracker into a CVS file that can be opened in LibreOffice to show:
EVENT STARTDATE STARTTIME ENDDATE ENDTIME COMMENT
in chronological order.
In the process, I've learned a lot more about AWK, SED, and the interactions with the various shells: SH, BASH, CSH, etc.
And yes, Virginia, there is a difference. The most maddening is in the ability (or not) to assign shell variables values from within AWK.
I won't take the time now to explore all that, just give you the answer. It is well commented, but as always, if you don't know what you're doing you might want to wait until you do.
In the meantime, all you gotta do is point this at your .ics file and the result will be a CSV-formatted .xls file with the same name, as in:
time_awk 141105_andy.ics
that results in
141105_andy.xls
Easy when you know how.
Enjoy.
=====
Here it is, under the standard GPL:
#! /bin/bash
#
# License: LGPL v3+ (see the file LICENSE)
# (c)2001-2014 C. Andrews Lavarre
# email : alavarre@gmail.com
#
########################################################################
# This program is free software; you can redistribute it and/or modify #
# it under the terms of the GNU General Public License as published by #
# the Free Software Foundation; either version 3 of the License, or #
# (at your option) any later version. #
# #
# This program is distributed in the hope that it will be useful, #
# but WITHOUT ANY WARRANTY; without even the implied warranty of #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
# GNU General Public License for more details. #
# #
# The GNU General Public License is posted at #
# http://www.gnu.org/licenses/gpl.txt #
# You may also write to the Free Software Foundation, Inc., #
# 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA #
########################################################################
# This routine was modified 141106 by C. A. Lavarre (Andy).
# It invokes AWK to parse an ICS file
# Arguments:
# $1 the path (optional) and filename of the ics
# e.g., /data/info/korganizer/archive/andy/130112_andy.ics
# Usage
# time_awk $1
# Save the input
target=$1
# Check the input
if [ "$target" == "" ]; then
#Complain and offer options
echo "Usage:"
echo "time_awk workingdirectoryandicsfilename"
echo "e.g., /data/info/korganizer/archive/andy/130112_andy.ics"
echo "Quitting"
exit 0
# Finish
fi
# Get the word and its length
source=$target
length=${#source}
# Get the file
myfile=${source##*/}
mybase=${myfile%.*}
mydir=$(dirname $source)
# If blank then
if [ "$mydir" == "." ]; then
# Set the working directory and basename
mydir="/data/info/korganizer/archive/andy"
# Finish
fi
mydir=$mydir"/"
# Show resultsmyfile
target=$mydir$myfile
# Name the new .xls file
output=$mydir$mybase".xls"
cd $basedir
echo "Saving "$target" awk results to "$output
echo "Continue? (y|n):"
read user
if [ "$user" == "n" ]; then
# Quit
echo "cd to the correct directory, follow the usage."
echo "Quitting"
exit 0
# Finish
fi
# Build the file
# Usage: awk [POSIX or GNU style options] [--] 'program' file ...
# awk -F 'all the instructions' $target
# the -F : option declares the colon as a field separator
# Between DTSTART and DTEND {do THIS} (^ means look at start of line)
# Then after SUMMARY {do THAT}
# and after COMMENT {do THE OTHER}
# and after END:VEVENT {FINISH}
#where
# THIS is
# {printf "%s," $2} which instructs to
# print the second field $2 as a string
# THAT is
# {printf "\"%s\",", $2} which instructs to
# print the second field $2 as a string in double quotes followed by an @ sign as the delimiter
# THE OTHER is
# {c=$2} which instructs to
# set the variable c to (copy) the value of the second field
# FINISH is
# {print c; c=""} which instructs to
# Print the COMMENT and clear the copy variable
# then pipe the whole lot to the tr command:
# Usage: tr [OPTION]... SET1 [SET2]
#Translate, squeeze, and/or delete characters from standard input,
# tr -d '\015' deletes character with octal value 015 (CR)
# Pipe it to awk to strip off the first line
# Pipe it to sed
# sed 's/,//g' removes all commas
# Pipe it to awk to remove lines with only two fields
# Pipe it to Sort it by columns #2 and #3
# then repeat this for all lines
# and send the lot to $output
awk -F :\
'/^DTSTART/||/^DTEND/ {printf "%s@", $2}\
/^SUMMARY/ {printf "\"%s\"@", $2}\
/^COMMENT/{c=$2}\
/END:VEVENT/ {print c; c=""}' $target\
| tr -d '\015'\
| awk 'NR>1'\
| sed 's/,//g'\
| awk -F @ '$3!=""'\
| sort -t "@" -k2\
> $output
chmod +x $output
# Create alternate work files
output1=$output"1"
output2=$output"2"
# Pipe it to awk to
# Move field $3 to $4
# and send the lot to $output1
awk -F @\
'{$(NF)=$3;}1' OFS="," $output > $output1
# Split field #2 on the T delimiter into $2 and $3:
# Split field $4 on the T delimiter into $4 and $5
# and send the lot to the file $output2
awk -F ,\
'\
{split($2, a, "T"); $3 = a[2]; $2 = a[1];}\
{split($4, a, "T"); $5 = a[2]; $4 = a[1];}\
{ print $0;}
' OFS="," $output1 > $output2
# Convert columns $2 and $4 in YYYYMMDD to MM/DD/YYYY
# Convert columns $3 and $5 to HH:MM:SS
# and send the lot to $output
awk -F ,\
'\
{$2 = substr($2,5,2)"/"substr($2,7,2)"/"substr($2,1,4)}\
{$3 = substr($3,1,2)":"substr($3,3,2)":"substr($3,5,2)}\
{$4 = substr($4,5,2)"/"substr($4,7,2)"/"substr($4,1,4)}\
{$5 = substr($5,1,2)":"substr($5,3,2)":"substr($5,5,2)}\
{print $0;}
' OFS="," $output2 > $output
# Delete the temporary files
rm $output1
rm $output2
# Report
echo "Parsing complete."
