--Apple-Mail-1-285132317
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset-ASCII;
	formatðïwed

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-mode66;
	namef_quiz17.rb"
Content-Disposition: attachment;
	filename_quiz17.rb

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

class ReportRecord

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

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

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

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

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

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

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

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

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

--Apple-Mail-1-285132317--