[SUMMARY] To Excel (#17)

Probably the first question to ask when looking at this quiz is, what 
makes this
report so unfriendly, not just to Excel, but to work with in general?

1. There's a lot of junk we don't need.  Headers, footers, dashed 
lines.  All
that needs to go, so we can focus on the data.

2. The data itself isn't pure.  Have a look at these two fragments:

	50,589     358 %%%%  56,652     430 %%%%
	924,553 704,028   31  1,612K  1,226K

Those aren't numbers in there.  Yuck.

3. This report is ALMOST a fixed width file, which would help a lot, 
but it
fails in a few areas:

	TEE_X_101 26-Mar-2002 15:26                        1: GE
	
	
	                                         ---------------
	                                         Current  LastYr
	Part Code       Description               Period  Period
	--------------- ------------------------ ------- -------
	  Salesperson  00 NOBODY
	    Customer  1036 COMPANY 501
	      SA Sort Code  1.43 WATER DOLLS
	78-143FS        17/8# SS MODEL                10       0
	                                         ------- -------
	      SA Sort Code subtotals                  10       0
	
	      SA Sort Code  3.3 REMOTE CONTROL CARS
	74270           Model 35357-DBL                0       0
	921137-73       LARGE 19 X 18 X 14            30       0
	                                         ------- -------
	      SA Sort Code subtotals                  30       0
	                                         ------- -------
	    Customer subtotals                        40       0
	
	    Customer  14457 COMPANY 518
	      SA Sort Code  11.5 KITCHEN SETS
	943437          19/8# SS MODEL                 0       0
	                                         ------- -------
	      SA Sort Code subtotals                   0       0
	                                         ------- -------
	    Customer subtotals                         0       0
	
	    Customer  1824 COMPANY 529
	      SA Sort Code  19.4 SLIDES
	8394            2.5 OZ                         0      20
	8341            .21 SIZE PLASTIC, NO BATT      0      10
	18363           .29 SIZE PLASTIC, NO BATT      0      24
	                                         ------- -------
	      SA Sort Code subtotals                   0      54
	                                         ------- -------
	    Customer subtotals                         0      54

Use the dashed lines as a guide and you can see how the sub-headers
(Salesperson, Customer, and SA Sort Code) don't fit into columns.  
Subtotal
lines have the same problem.  Also notice that the Description content 
is
allowed to overflow it's column by one character.  That's a mess.

4. This one is less obvious, but equally real.  The data isn't very 
useful in
this format.  Christian Neukirchen did a nice job of recognizing this 
and taking
steps to correct it.

Now that we've identified the issues, let's work through them.  When I 
began
playing with a foreign report like this, I do just that:  Play around a 
bit.  My
first script may not end up being the one I keep, but I need to get 
familiar
with the data.  I'll show you what works for me, step by step.

Whenever I think, "I need to translate this into..." I mentally switch 
that to:

	while line = ARGF.gets  # traditional Unix filter
	
		print line
	end

It may not be much, but it's a start.  Now, let's tackle problem number 
1 and
jettison the junk.  The majority of it is those silly page headers.  
Those
should be easy enough to drop.  We stop printing when we enter a header 
and
start up again when we leave.

I think in patterns, so I would then start looking for a way to spot 
that I'm
entering a header.  I'm leery to anchor against arbitrary text, if I 
don't have
too, so I would like to find something better.  If you're familiar with 
these
page print style reports, you might have a good guess of what comes 
before that
header.  Even if you're not, it pays to look at a foreign document with 
the
"invisible characters" turned on, if your editor can do that.  Here's 
what a
header really looks like:

	      SA Sort Code subtotals                  20       0
	
	\f  <-- Look what's hiding here!  A form-feed character.
	TEE_X_101 26-Mar-2002 15:26                        1: GE
	
	
	                                         ---------------
	                                         Current  LastYr
	Part Code       Description               Period  Period
	--------------- ------------------------ ------- -------
	      SA Sort Code  5.207 BUILDING BLOCKS

That makes spotting the headers easy.  The very first one doesn't have 
the
form-feed, but that's not hard to work around.

Once we're in a header, the last line is just a solid line of dashes 
and spaces.
  Just be careful to distinguish it from the other dashed line in the 
header and
you can build a solution:

	header = true  # start in header
	while line = ARGF.gets
		if header  # we're inside the header
			header = false if line =~ /^-[- ]+-$/  # watch for the end
		else  # we're not in the header
			if line =~ /\f/  # watch for header beginning
				header = true
				next
			end
			
			print line
		end
	end

If you run that on the report, you'll see that we did indeed remove all 
the page
headers.  I showed (and even submitted) that version because it's easy 
to break
down and understand, but if all that code bothers you, we can shorten 
it up:

	while line = ARGF.gets
		# the next line skips all headers
		next if ($. == 1 or line =~ /\f/) .. line =~ /^-[- ]+-$/
			
		print line
	end

That does exactly the same thing, making use of Ruby's versatile 
Ranges.  The
range acts as a toggle here.  When the first condition becomes true, 
the Range
will evaluate to true (skipping lines with next()) until the second 
condition
becomes true.  Then the process begins again.  My first condition 
searches for
line one of the report or a form-feed character.  The second condition 
looks for
the solid line of dashes.  Again, the code works the same, so you can 
use
whatever you are comfortable with.

We're not done dropping junk yet!  Let's toss out dashed lines and the 
report
footer.  Both are trivial:

	while line = ARGF.gets
		next if ($. == 1 or line =~ /\f/) .. line =~ /^-[- ]+-$/
		next if line =~ /--$/  # skip dashed lines
			
		print line
	
		break if line =~ /^Report Totals/  # stop with "Report Totals"
	end

What's the report look like now?

	  Salesperson  00 NOBODY
	    Customer  1036 COMPANY 501
	      SA Sort Code  1.43 WATER DOLLS
	78-143FS        17/8# SS MODEL                10       0
	      SA Sort Code subtotals                  10       0
	
	      SA Sort Code  3.3 REMOTE CONTROL CARS
	74270           Model 35357-DBL                0       0
	921137-73       LARGE 19 X 18 X 14            30       0
	      SA Sort Code subtotals                  30       0
	    Customer subtotals                        40       0
	
	    Customer  14457 COMPANY 518
	      SA Sort Code  11.5 KITCHEN SETS
	943437          19/8# SS MODEL                 0       0
	      SA Sort Code subtotals                   0       0
	    Customer subtotals                         0       0

We're making progress.  It's almost readable.

There are four types of lines left in this report that we need to deal 
with.
Yes, I'm sure it's four and not three.  Sub-headers, product sales, 
subtotals
and blank lines.  At this point, I would try to build selectors to 
handle each
of them:

	while line = ARGF.gets
		next if ($. == 1 or line =~ /\f/) .. line =~ /^-[- ]+-$/
		next if line =~ /--$/
		
		# handle blank lines...
		if line !~ /\S/
			print "  BLANK:  ", line
		
		# handle subtotals
		elsif line =~ /^.+?totals(?:\s+(?:-?[\d,]+K?|%+)){12}\s*$/i
			print "  TOTAL:  ", line
		
		# hande product sales
		elsif line =~ /^\S/
			print "PRODUCT:  ", line
		
		# handle subheaders
		else
			print " HEADER:  ", line
		end
	
		break if line =~ /^Report Totals/
	end

Uh oh, scary Regexp in there.  Let's break it down:

	^ # the beginning of the line
	    .+?    # skip some of the beginning looking for...
	    totals # the word totals (to match subtotals or Report Totals with 
/i)
	    (?: # grouping
	        \s+ # some whitespace
	        (?: # start group
	            -?     # an optional minus
	            [\d,]+ # a comma separated digit sequence
	            K?     # an optional trailing K
	            |      # or ...
	            %+     # a run of % signs
	        ) # end group
	    ){12} # end group -- find exactly 12 of those
	    \s*   # optional trailing whitespace
	$ # the end of the line

In short, it's just looking for the word "totals" followed by 12 things 
that
could be numbers in this report.  You really don't even need an 
expression that
complex, in this case, but since the headers can hold arbitrary text 
(company
names) I want to make sure I'm matching what I'm looking for.  Ruby 
will even
let you drop that commented Regexp in your code with /x if you like.

Notice that I'm being careful to use generic patterns.  For example, I 
never
match Salesperson, Company or SA Sort Code.  What if the company 
happens to have
another report that includes Brokers?  (Trick question.  I know it to 
be true in
this case!)  These patterns will treat that like any other sub-header 
and just
work as expected.

So does that successfully locate everything?

	 HEADER:    Salesperson  00 NOBODY
	 HEADER:      Customer  1036 COMPANY 501
	 HEADER:        SA Sort Code  1.43 WATER DOLLS
	PRODUCT:  78-143FS        17/8# SS MODEL                10       0
	  TOTAL:        SA Sort Code subtotals                  10       0
	  BLANK:
	 HEADER:        SA Sort Code  3.3 REMOTE CONTROL CARS
	PRODUCT:  74270           Model 35357-DBL                0       0
	PRODUCT:  921137-73       LARGE 19 X 18 X 14            30       0
	  TOTAL:        SA Sort Code subtotals                  30       0
	  TOTAL:      Customer subtotals                        40       0
	  BLANK:
	 HEADER:      Customer  14457 COMPANY 518
	 HEADER:        SA Sort Code  11.5 KITCHEN SETS
	PRODUCT:  943437          19/8# SS MODEL                 0       0
	  TOTAL:        SA Sort Code subtotals                   0       0
	  TOTAL:      Customer subtotals                         0       0

Sure does.  Now it's easy to just transform that data into CSV, which 
will fix
our not-quite-fixed-width-columns problem:

	require "csv"
	
	while line = ARGF.gets
		next if ($. == 1 or line =~ /\f/) .. line =~ /^-[- ]+-$/
		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, *$2.split(" ")])
	
		elsif line =~ /^(\S+)\s+(.+?)((?:\s+(?:-?[\d,]+K?|%+)){12})\s*$/
			puts CSV.generate_line([$1, $2, *$3.split(" ")])
	
		else
			puts CSV.generate_line(["", line.strip])
		end
	
		break if line =~ /^Report Totals/
	end

I snuck another nasty Regexp in there didn't I?  Actually, if you look 
closely,
you'll see that it's nearly identical to the other one, save that it 
also
catches product descriptions.  It's just there to help me locate all 
the parts
of the line.  After capturing the parts, I use split() to break them 
up, strip()
to clean them, and let Ruby's CSV handle the rest.

If you open the CSV output from this in Excel, you'll see that I've 
just shifted
sub-headers and subtotals into the second column.  That pretty much 
maintains
the familiar report format, while eliminating the column breakup issues.

That's really a lot of the clean up work done already.  The nagging 
problem is
that Excel doesn't consider 1,612K or %%%% numbers.  We can fix the 
first one
easy enough, but what the heck is causing the %%%%s?

More detective work is needed.

Those %%%% entries only happen in the small "Pct Var" column, which is 
four
characters wide.  "Var" huh?  Variance maybe?  If you look around a 
bit, the
formula is easy to calculate from a row like this:

	                                         Current  LastYr  Pct
	Part Code       Description               Period  Period  Var
	--------------- ------------------------ ------- ------- ----
	
	613433          .22 SIZE PLASTIC, NO BATT     65       1 6400

That looks like (65 - 1) * 100.  That doesn't make much sense though, 
because
we're probably talking about a percentage.  More likely is (65 - 1) / 1 
* 100.
You can test that on other rows to prove that it is indeed correct.

Okay, so why are we getting %%%%s?

	Current  LastYr  Pct
	    YTD     YTD  Var
	------- ------- ----
	
	    125       1 %%%%

(125 - 1) / 1 * 100 = 12400.  That's five characters and we're only 
allowed
four.  It's a column overflow problem.  Our CSV approach has no such 
limitation,
so we can recalculate those numbers and add them to the output.

Here's a routine to clean up the numbers:

	def clean( numbers )
		# turn them back into numbers...
		numbers.map! do |n|
			n.gsub!(",", "")  # drop commas
			if n.sub!(/K$/, "")  # when there's a K...
				n.to_i * 1000    # multiple by 1,000
			elsif n !~ /%/
				n.to_i           # numify
			else
				n
			end
		end
		
		# recalculate %%%% columns...
		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  # return results
	end

To use that, we just call it twice:

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

The rest of my solution (not shown) was boring header parsing, to print 
out the
column labels and the report "Period".  You can follow the link in the 
sidebar
if you want to examine that.

At this point, we've solved problems 1, 2 and 3 from the beginning of 
this
summary.  I want to talk a little about problem 4, then I promise to 
end this
ridiculously long message.

Christian Neukirchen's short solution (not shown) does away with 
sub-header and
subtotal lines.  That filter tacks on the Salesperson, Customer and SA 
Sort Code
to each product entry.  This makes all the needed information available 
on every
line.  This is a big win for using Excel's filters, making the report a 
lot
easier to examine and change.

When I did this project for work, I did the same thing.  However, I put 
the
metadata at the back of the line and left in the sub-headers, subtotals 
and
blanks.  I also added a column to identify each type of line:  Header, 
total,
data or blank.  This gave me all the same benefits as Christian's code 
has,
while keeping the familiar format.  I could filter down to the "data" 
lines to
have nearly identical results.  This feature turned out to be a big hit,
drastically increasing company productivity.

Sorry for sending in a quiz that "looked too much like real work".  I 
promise,
we're back to fun and games tomorrow...