From: James Edward Gray II Date: 2005-02-03T23:20:35+09:00 Subject: [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...