From: James Edward Gray II <james@...>
Date: 2005-02-02T06:58:37+09:00
Subject: Re: [SOLUTION] To Excel (#17)

This will probably get me some hate mail from people who like pretty 
code, but I'll defend my reasoning for this approach in the summary:

#!/usr/bin/env ruby

require "csv"

def clean( numbers )
	numbers.map! do |n|
		n.gsub!(",", "")
		if n.sub!(/K$/, "")
			n.to_i * 1000
		elsif n !~ /%/
			n.to_i
		else
			n
		end
	end
	
	numbers.each_with_index do |n, i|
		if n.to_s =~ /%/
			numbers[i] = ( (numbers[i - 2] - numbers[i - 1]) /
						   numbers[i - 1].to_f * 100 ).to_i
		end
	end
	
	numbers
end

def labels
	period = ""
	headers = [ ]
	while line = ARGF.gets
		headers << line
		headers.shift if headers.size > 4
		
		period = $1 if line =~ /General Sales Report\s+(.+?)\s*$/
	
		break if line =~ /^-[- ]+-$/
	end

	pattern = headers.pop.split(" ").map { |s| "a#{s.length}" }.join("x")

	types = { }
	headers.map! do |h|
		h.gsub!(/-+(([A-Z])[^-]+)-+/) do |m|
			types[$2] = $1
			$2 * m.length
		end
		
		h.unpack(pattern).map do |s|
			if s =~ /^([A-Z])\1+$/ and types.include?($1)
				types[$1]
			else
				s.strip
			end
		end
	end
	
	headers.transpose.map { |h| h.join(" ").lstrip } << period
end

puts CSV.generate_line(labels)

header = false
while line = ARGF.gets
	if header
		header = false if line =~ /^-[- ]+-$/
	else
		if line =~ /\f/
			header = true
			next
		end
		next if line =~ /--$/
		
		if line !~ /\S/
			puts CSV.generate_line([""])
		elsif line =~ /^(.+?totals)((?:\s+(?:-?[\d,]+K?|%+)){12})\s*$/i
			puts CSV.generate_line(["", $1.lstrip, *clean($2.split(" "))])
		elsif line =~ /^(\S+)\s+(.+?)((?:\s+(?:-?[\d,]+K?|%+)){12})\s*$/
			puts CSV.generate_line([$1, $2, *clean($3.split(" "))])
		else
			puts CSV.generate_line(["", line.strip])
		end
	end

	break if line =~ /^Report Totals/
end

__END__

If that makes you break out into a cold sweat, submit something 
prettier.  Until then, you can't whine.  ;)

James Edward Gray II

P.S.  Here's the first few customers of output, for the curious:

Part Code,Description,Qty Current Period,Qty LastYr Period,Qty Pct 
Var,Qty Current YTD,Qty LastYr YTD,Qty Pct Var,Pounds Current 
Period,Pounds LastYr Period,Pounds Pct Var,Pounds Current YTD,Pounds 
LastYr YTD,Pounds Pct Var,Period  02/2002
"",Salesperson  00 NOBODY
"",Customer  1036 COMPANY 501
"",SA Sort Code  1.43 WATER DOLLS
78-143FS,17/8# SS MODEL,10,0,0,10,0,0,100,0,0,100,0,0
"",SA Sort Code subtotals,10,0,0,10,0,0,100,0,0,100,0,0
""
"",SA Sort Code  3.3 REMOTE CONTROL CARS
74270,Model 35357-DBL,0,0,0,40,0,0,0,0,0,400,0,0
921137-73,LARGE 19 X 18 X 14,30,0,0,30,0,0,300,0,0,300,0,0
"",SA Sort Code subtotals,30,0,0,70,0,0,300,0,0,700,0,0
"",Customer subtotals,40,0,0,80,0,0,400,0,0,800,0,0
""
"",Customer  14457 COMPANY 518
"",SA Sort Code  11.5 KITCHEN SETS
943437,19/8# SS MODEL,0,0,0,56,0,0,0,0,0,560,0,0
"",SA Sort Code subtotals,0,0,0,56,0,0,0,0,0,560,0,0
"",Customer subtotals,0,0,0,56,0,0,0,0,0,560,0,0
""
"",Customer  1824 COMPANY 529
"",SA Sort Code  19.4 SLIDES
8394,2.5 OZ,0,20,-100,0,40,-100,0,480,-100,0,960,-100
8341,".21 SIZE PLASTIC, NO 
BATT",0,10,-100,60,10,500,0,120,-100,720,120,500
18363,".29 SIZE PLASTIC, NO 
BATT",0,24,-100,0,39,-100,0,720,-100,0,1170,-100
"",SA Sort Code subtotals,0,54,-100,60,89,-33,0,1320,-100,720,2250,-68
"",Customer subtotals,0,54,-100,60,89,-33,0,1320,-100,720,2250,-68
""
...