--Apple-Mail-1-285132317
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed

Graham Foster wrote ..
> Hello,
>> I think you should give it a shot.
> So I did. Its awfully VBish in its style.
>
> I took the approach that it was a waste of time to migrate the
> subtotals, as spreadsheets do this anyway, and they would be
> automatically recalculated as the data were updated / corrected. I've
> got a bug that I can't see, in that spurious short lines are output
> periodically.. (Its probably obvious - but its late...)
>
> The missing bit (a quick macro to open the output file and run
> sub-totals is simply a modified recorded macro in Excel). I haven't
> figured how to do this programmatically in StarOffice yet - (Which is
> what I use)
>
> It was enjoyable getting this far.. I'll look further at other quizes
> too.
>
> -- 
> Best regards,
>  Graham

--Apple-Mail-1-285132317
Content-Transfer-Encoding: quoted-printable
Content-Type: application/octet-stream;
	x-unix-mode=0666;
	name="gf_quiz17.rb"
Content-Disposition: attachment;
	filename=gf_quiz17.rb

#!/usr/bin/env=20ruby
#=20Ruby=20command=20line=20function=20to=20cleanup=20an=20input=20ASCII=20=
database=20report=20file
#=20and=20output=20a=20file=20easily=20parsed=20/=20read=20into=20Excel
#=20Specification=20-=20Ruby=20Quiz#17=20http://www.rubyquiz.com
#=20Author:=20Graham=20Foster

class=20ReportRecord

=20=20attr_accessor=20:salesman,=20:period,=20:currency,=20:SACode,=20=
:coCode,=20:coDesc
=20=20doneHeaders=20=3D=20false;
=20=20
=20=20def=20initialize
=20=20=20=20=20#=20items=20which=20are=20carried=20over=20from=20line=20=
to=20line.
=20=20=20=20=20@salesman=20=3D=20@period=20=3D=20@currency=20=3D=20=
@SACode=20=3D=20@coCode=20=3D=20@coDesc=20=3D=20'';
=20=20=20=20=20doneHeaders=20=3D=20false;
=20=20end

=20=20def=20itemline(catcode,=20desc,=20columns)
=20=20=20=20#=20ensure=20that=20the=20comma's=20in=20larger=20numbers=20=
are=20removed
=20=20=20=20columns.gsub!(/,/,'')
=20=20=20=20#=20now=20remove=20any=20spaces=20in=20the=20numeric=20=
fields=20and=20replace=20them=20with=20a=20comma=20(field=20seperator)
=20=20=20=20columns.gsub!(/\s+/,',');
=20=20=20=20
=20=20=20=20#=20Mmmh=20-=20how=20to=20handle=20%%=20(field=20overflow)=20=
in=20an=20item=20line?
=20=20=20=20#=20Guess=20I'll=20do=20nothing=20as=20at=20least=20they=20=
are=20easy=20to=20find.=20By=20skipping=20subtotals
=20=20=20=20#=20only=20a=20few=20of=20these=20items=20will=20be=20wrong.
=20=20=20=20
=20=20=20=20#=20now=20check=20to=20see=20if=20this=20is=20a=20valid=20=
data=20line
=20=20=20=20if=20((columns.split(/,/)).length=20>=204)
=20=20=20=20=20=20=20=20#=20this=20line=20determines=20the=20format=20of=20=
the=20printout
=20=20=20=20=20=20=20=20#=20note=20that=20text=20based=20columns=20are=20=
'=20delimited=20to=20ensure=20any=20embedded=20,=20don't=20disrupt=20the=20=
file
=20=20=20=20=20=20=20=20#=20format
=20=20=20=20=20=20=20=20printf("%s,%s,%s,'%s','%s','%s',%s\n",=20=
@salesman,=20@coCode,=20@coDesc,=20@SACode,=20catcode,=20desc.strip,=20=
columns)
=20=20=20=20end=20=20=20=20=20=20=20=20
=20=20end
=20=20
=20=20def=20printHeaders
=20=20=20=20=20=20#=20simply=20output=20the=20columns=20headers.=20=
Obviously=20must=20be=20unique
=20=20=20=20=20=20print=20("Salesman,=20CustomerID,=20CustomerName,=20=
SACode,=20ItemCode,=20ItemDesc,=20spare,=20QtyCurrentPeriod,=20=
QtyLastYrPeriod,=20QtyPctVarPeriod,=20QtyCurrentYTD,=20QtyLastYrYTD,=20=
QtyPctVarYTD,=20ValueCurrentPeriod,=20ValueLastYrPeriod,=20=
ValuePctVarPeriod,=20ValueCurrentYTD,=20ValueLastYrYTD,=20=
ValuePctVarYTD\n");=20=20=20=20=20=20
=20=20end
=20
end

record=20=3D=20ReportRecord.new
#=20just=20insert=20a=20header=20line=20into=20the=20output=20file
record.printHeaders

#=20need=20to=20read=20the=20input=20file=20from=20the=20command=20line=20=
etc
#=20to=20make=20this=20rather=20less=20specific!!
IO.foreach("k:/temp/period2-2002.txt")=20{=20|line|
=20=20=20=20case=20line=20

=20=20=20=20=20=20=20=20#=20Subtotaling=20by=20new=20salesperson
=20=20=20=20=20=20=20=20when=20/=20=20Salesperson=20=20(.*)/
=20=20=20=20=20=20=20=20=20=20=20=20record.salesman=20=3D=20$1.strip;

=20=20=20=20=20=20=20=20#=20if=20a=20line=20starts=20a=20new=20customer
=20=20=20=20=20=20=20=20when=20/^=20=20=20=20Customer=20=20=
(\d+)\s+(\w+.*)/=20
=20=20=20=20=20=20=20=20=20=20=20=20record.coCode=20=3D=20$1.strip;
=20=20=20=20=20=20=20=20=20=20=20=20record.coDesc=20=3D=20$2.strip;

=20=20=20=20=20=20=20=20#=20new=20SACode=20(seems=20to=20be=20a=20=
grouping=20of=20similar=20items)
=20=20=20=20=20=20=20=20when=20/^\s+SA=20Sort=20Code\s+(\d+.*)/
=20=20=20=20=20=20=20=20=20=20=20=20record.SACode=20=3D=20$1.strip;
=20=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20#pick=20up=20which=20period=20this=20is
=20=20=20=20=20=20=20=20#-=20not=20needed=20currently=20but=20output=20=
for=20subtotaling
=20=20=20=20=20=20=20=20when=20/Sales=20Report\s+Period\s+(\d+\/\d+)/
=20=20=20=20=20=20=20=20=20=20=20=20record.period=20=3D=20$1.strip;
=20=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20#=20make=20it=20explicit=20in=20the=20code=20we=20=
are=20NOT=20copying=20sub-totals=20-=20let=20
=20=20=20=20=20=20=20=20#=20Excel=20do=20this=20properly,=20rather=20=
than=20having=20field=20overflow
=20=20=20=20=20=20=20=20when=20/=20subtotals=20/
=20=20=20=20=20=20=20=20=20=20=20=20#=20do=20nothing
=20=20=20=20=20=20=20=20=20=20=20=20;
=20=20=20=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20#pickup=20the=20currency=20field=20-=20not=20=
needed=20as=20yet
=20=20=20=20=20=20=20=20when=20/-Qty-+\s-+(\w+)-+/
=20=20=20=20=20=20=20=20=20=20=20=20record.currency=20=3D=20$1.strip;

=20=20=20=20=20=20=20=20#=20this=20line=20is=20a=20real=20line=20with=20=
sales=20information=20on=20it
=20=20=20=20=20=20=20=20#=20looks=20like=20it=20is=20possible=20for=20=
item=20description=20to=20merge=20with=201st=20column=20of=20data
=20=20=20=20=20=20=20=20#=20hence=20use=20a=20fixed=20width=20field=20to=20=
separate=20description=20from=20data=20columns
=20=20=20=20=20=20=20=20when=20=
/^([0-9A-Z][0-9A-Z.-][\d-]+)\s+(.{25})(.*)/
=20=20=20=20=20=20=20=20=20=20=20=20#=20$3=20is=20the=20remainder=20of=20=
the=20line=20-=20i.e.=20all=20the=20spreadsheet=20data
=20=20=20=20=20=20=20=20=20=20=20=20record.itemline($1,=20$2,=20$3);
=20=20=20=20end
=20=20=20=20}

#=20Next=20part=20is=20to..=20(but=20ran=20out=20of=20time...)
#=20load=20the=20file=20into=20Excel.=20single=20'=20wrapped=20round=20=
text=20fields,=20
#=20comma=20separated=20field=20values
#=20run=20the=20SUBTOTAL=20command=20in=20Excel=20(possibly=20via=20=
OLE??)
#=20using=20the=20Salesman,=20Company=20Desc=20and=20SACode=20as=20the=20=
sub-total=20fields
#=20this=20should=20give=20you=20an=20accurate=20list=20of=20=20what=20=
was=20in=20the=20report=20=20=20=20=20=20=20
=20=20

--Apple-Mail-1-285132317--