I'm far from a spreadsheet expert but my clients use them instead of
databases (but that's another discussion).
Reading this thread suggests that it might be more appropriate for Russell
to use gawk on a .csv text file saved from OO.o/LO. I don't recall seeing
the end use of the data nor whether it's fixed (or otherwise relatively
unchanged), but gawk excels (no pun intended) at pattern matching.
I take spreadsheet-originated data and add fields based on the content of
other fields. As a brief example, here's a script that adds a flag when
water chemical laboratory analytical results are less than the method's
detection limit:
#! /usr/bin/gawk -f
# add-non-detect.awk -- Add both an indicator digit (0 = quantified, 1 = <RL)
# and bounds to all quantities.
# Source fields: site, sampdate, param, quant; output fields add ceneq1,
# floor, ceiling.
BEGIN { FS = ","; OFS = "," }
function abs(value)
{
return (value<0?-value:value);
}
# NULL quantity
$4 ~ /^$/ { next; }
{
if ( NF < 4 ) { next; }
# Non-detected quantity
if ($4 ~ /^-.*/) { print $1, $2, $3, abs($4), "1", "0", abs($4) }
else { print $1, $2, $3, $4, "0", $4, $4 }
}
I also wrote longer scripts that have multiple ifelse lines to cover all
contingencies. The results can always be re-imported into the spreadsheet.
HTH,
Rich
_______________________________________________
PLUG mailing list
[email protected]
http://lists.pdxlinux.org/mailman/listinfo/plug
