Quicken didn’t want to do what I needed, which was to total all my expenses by place of origin for the year to date. Quicken only sorts on category, but I long since gave up keeping Quicken up to date since I don’t want to message around with Web access to fetch my expenses from multiple places.
Enter the shell.
First I want all my comma separated value files in a single file.
for e in $(ls *.csv) ; do cat $e >> all.csv ; done
Next, I want to process them, sorting them uniquely by point of sale location.
cat all.csv | grep '\-[0-9]' | sort -T , -k 3 | awk -F , '{ print $3,",",$5 }' > my.out
What does that process?
03/27/2004,2354,"DOMINO'S PIZZA #05 ","GAINESVILLE FL ", -9.99 03/27/2004,7041,"PERKINS REST &08","GAINESVILLE FL ", -18.00 03/29/2004,8148,"KASH N KARRY #6","GAINESVILLE FL ", -16.23
Finally, some very sloppy Perl code will clean up my mess. (Thanks to WP, replace any [ you see with an actual greater than sign.)
while(<>) {
$_ =~ m/"([^,]*)"&\s+,\s+-(\d+&\.\;d+)/;
$a{$1}+=$2;
print "$1 $2 $a{$1}&\n";
}
while(my($a,$b)=each %a) {
format STDOUT =
@[[[[[[[[[[[[[[[[[@#####.##
$a, $b
.
write;
}
To those who’s nasty looking Perl I comment on all the time… Don’t even start with me… My sloppy code owns you. (And apparently WP likes to eat front slashes and other characters, so I had to hunt down the appropriate HTML escape sequence.)
The output is rather nice, though. (Amounts changed to protect my favourite merchants’ feelings.)
KASH N KARRY #1786 14.71 CABLE & CONNECTOR 60.00 ITALIAN GATOR PIZZ 40.20 OFFICE DEPOT #2145 57.94 NEWEGG COMPUTERS 2031.47