I just spent several days of my "spare" time to build a handy travel beerlist for our upcoming family trip to the Netherlands and Belgium. I was quite pleased with the end result, but the process is far from straightforward and simple. This is partly due to the lack of export features in Ratebeer, so maybe after reading this someone could implement a list of all breweries and beers of a certain country with the same data as in current brewery view from Ratebeer.
This is by no means the best or only way to do this, but I'll document it anyway. I'm using a Mac here, but the same can be done on Windows also using almost identical commands. To follow the exact instructions, you'll need the following software:
Open a list of breweries of the country you're interested in (in my case, Belgium) to your favorite browser (in my case, Google Chrome).
Open your favorite spreadsheet application (I'm using Excel 2008 for Mac in the examples). In Excel, first select all cells in the workbook (click on the top left corner with "<>" in it), and choose "Format Cells" from the "Format" menu. Make all cells of type "Text", so Excel will not try to interpret the score data as dates or whatever. This might not be necessary in those countries, that use a dot (.) for decimal separator (in Finland we use comma (,) so scores like 1.12 would be translated as December 1st when pasting - not good!).
Repeat the steps below for all the breweries, in alphabetical order (or whatever order you prefer ;). In my case, I skipped the Trappist breweries (since they are all sampled anyway), and retired breweries (except a few recently closed ones, like Regenboog).
1.1 Select all the rows from the html table, except the headers (see below), and copy them into clipboard. I didn't copy the contract brews for other breweries, so that I ended up with only the beers from one country, and no duplicates.
1.2 Switch to Excel, click the first free cell in top left using the right mouse button, and select "Paste Special...".
1.3 From the Paste Special dialog, select "Unicode Text", so you don't get the images and other crap along with the paste.
1.4 After pasting, the data will look something like what is shown below. The green corner marks indicate numbers stored as text, but you don't need to worry about those at this point. Just keep copying and pasting more breweries, and remember to save the spreadsheet often in case something bad happens!
Now go back to step 1.1, and repeat until all the breweries are done.
Now that we have all the data in Excel, it's time to clean it up a bit.
2.1 Delete the unnecessary columns (in this case, C and J) by clicking on the letter above the column with the right mouse button, and selecting "Delete".
2.2 First, we clean up the beer names. Select the first column with the beer names by clicking on the column header "A", and copy the selected column to clipboard.
2.3 I'll be using MS Word 2008 for Mac for cleaning up. I don't want to paste directly into it, as the beer names will appear as a table in Word, making the operations more difficult. So, open a simple text editor (TeachText, Notebook, etc), that doesn't understand anything about table cells, and paste the column there.
2.4 Now that we got it all in plaintext, select and copy all text in the editor, and paste into Word. The first thing we could do, is get rid of the extra spaces in front of most beer names. We'll do a Find and Replace: type cmd-F (ctrl-F in Windows), and the dialog below will open. Select the Replace tab, and put your cursor in the "Find what:" text box. Now select the "Special" popup menu from the bottom, and choose "Paragraph mark". Alternatively, you can just type ^p into the "Find what:" box.
2.5 Put the same ^p in the "Replace with:" box, but add an extra space after ^p in the "Find what:" box. This will look for spaces that immediately follow after a newline - in other words, it will remove the space from the beginning of each line (except the first one, but there was no space to begin with :). Now click the "Replace All" button. If Word asks you, if it should continue replacing from the beginning of document, click Yes.
2.6 Now the beer names look better, but there might still be extra spaces after each line. You can fix this by repeating the steps 2.4 to 2.5, only this time putting the space before ^p in the "Find what:" box. In my case, there were 2 extra spaces after each name, so you can keep clicking on "Replace All" until Word reports that it made 0 replacements.
2.7 Many brewery names include stuff that's sometimes confusing when browsing them alphabetically. Personally, I prefer to strip off things like 't, Het, Brouwerij, Brasserie, Kleinbrouwerij, etc. and just leave the actual names, so I can sort them alphabetically. For example 't Hofbrouwerijke becomes Hofbrouwerijke, and Brouwerij Van Honsebrouck becomes Honsebrouck. But this is just a personal preference, so suit yourself. If you want to strip the extra stuff out, this is a good time to do that. Just do a search and replace for the things you want to strip off, followed by a space, and replace with an empty string.
2.8 Ok, time to return to Excel. Select all the text in Word, and copy. Switch to Excel, where you probably still have the column A selected. If so, just paste the text in. If not, first select the first column by clicking the A. The pasted text probably has a different font than the rest, so to keep things neat, select the "Match Destination Formatting" from the menu that is shown in the bottom right of your pasting region.
2.9 Next we need to make an extra column for brewery names. Do this by selecting the A header once again with the right mouse button, and select "Insert". A new column will appear on the left of A, making the existing columns shift to right.
2.10 Once again, time for some manual labor (yes, this could be done for us by the nice folks at Ratebeer ;). At each line where a new brewery begins, you must type or copy-paste the brewery name. After this, move the cursor in the bottom right corner of the cell you just typed the brewery name into. When the cursor changes into a black cross, click and drag the corner down as long as there are beers from that brewery. This way, you only need to type each brewery name once.
Well, not actually a discrete step, but Step 2 had so many substeps already, that I'm out of breath.
3.1 The first thing we want is to get rid of any extra spaces in the data area (here the columns C-I). Select those columns by clicking on C, and dragging all the way to I.
3.2 This step is only necessary, if your decimal separator differs from the US standard. Do a Find and Replace by selecting "Find" from the "Edit" menu, or use the familiar cmd-F (ctrl-F in Windows) to open the Find dialog (see the picture in 3.3). Click on the "Replace..." button to reveal the replacing options. Type a . into the "Find what:" box, and , (or whatever your decimal separator is) on the "Replace with:" box. Click "Replace All". The numbers should now be in correct format for the next step.
3.3 Do another Find and Replace: Type a space into the "Find what:" box, and nothing on the "Replace with:" box. Click "Replace All".
The data columns should now look somewhat like this. In my case, the columns E and I are still stored as text, so I'm gonna format those columns as numbers. Also the ABV column D has no decimals, so I'm adding one.
3.4 Select the column you want to change by clicking on the header letter. Select "Cells..." from the "Format" menu, and choose "Number" from the list. You can set the decimal places as you wish, probably 2 for the overall score, and 1 for the ABV and your own scores.
3.5 Woo-hoo!! We finally have the data we want in our spreadsheet. We could be happy with this - but as an engineer I'm not, so let's move on...
I'm going to a beer festival in Bruges, so I wanted to mark the beers available there, and not yet rated by me to be clearly visible in the list. For this, I created an extra column after the data area, and put "1" to each row with such a beer (but first selecting the extra row I and formatting it as number with 0 decimal places, like we did in 3.4). Also, I added beers that will be at the festival, but not yet in Ratebeer (for this, you need to click a row header at the point where you want to add the beer with the right mouse button, and select "Insert..."). Below I have added the extra column with 1s, and a new beer (Montaigu Kerstbier). We will make these lines stand out from the crowd later on, but for now they just have a 1 in the last column.
Now comes another part that requires lots of manual labor (RB admins, take note): resolving aliases. The alias targets are not visible in the brewery listings, so this must be done by hand (or by memory, in which case you probably won't need the list, either ;). You could forget about this, if feeling lazy - but for completeness, and when talking about Belgian brews, it's probably worth the effort. There are probably better ways to do this, but I just checked each aliased beer, and put it into parenthesis after the "actual" beer's name in the list. For example, in the list above, I removed the row with "Strakke Rakker", that has "A" in the third column, and to the row with "'t Hofbrouwerijke Bosprotter", I edited the name to "'t Hofbrouwerijke Bosprotter (Strakke Rakker)". This will take some time, if there are many aliased beers on your list...
Now, after all the hard work, comes the fun part: making Excel really work for your own good. If I remember correctly, conditional formatting is a feature available in Excel 2008 (for Mac) and 2007 (for Windows), and newer. So, users of older versions are out of luck here, and need to use another method (which is out of the scope of this document). Conditional formatting means, that you can control the appearance (colors, fonts, etc.) of certain data based on the data itself, or data somewhere else in the workbook. For example, you can make all retired beers grayed out (which we will also do here, among other things).
5.1 First, we'll do a search and replace or column C with the letters "R" marking the retired beers. We'll replace the R's with 1's to make the next steps easier. At this point, you know how to do this, right? After replacing, change the format of column C into "Number" with 0 decimals.
5.2 Now, select the columns you want to color in the next steps. In my case, I only wanted the beer names to be color-coded, so I selected column B.
5.3 Select "Conditional Formatting..." from the "Format" menu. A dialog will open:
5.4 Here you have different conditions, that are pretty obvious if you just want to highlight, say, the scores of beers with a score over 3.5. However, as we are highlighting the names, not the numerical data, we need to change the default condition from "Cell Value Is" to "Formula Is". Do this, and type =(I1 > 0). This means, that the condition is true when the cell in column I of the same row is larger than 0 (i.e. when you have rated the beer). Then click on the "Format..." button, and select a color you want to highlight already rated beers with. In my case, I selected "Sea Green" from the palette. You should select colors that get printed clearly, i.e. preferably have a high percentage of at least one of the CMYK (cyan, magenta, yellow, black) component colors used in color printers. Otherwise, it can be difficult to read the text in small sizes due to rasterization done in the printer.
After doing this, the dialog should look like:
After clicking "OK", your spreadsheet should look like:
5.5 Now as I also want to highlight retired beers, and those at the festival, I create two other conditions for the column B, similarly to the step 5.4 (click on the "Add >>" button to add conditions). If you don't need the "festival" category, just leave out Condition 3.
Now my spreadsheet looks like below. Note, that I'm using only subset of my Belgian beers list in the examples - just enough data to include some aliases and retired beers, as well as those available at the festival.
5.6 Now let's move the "Retired" column C into a better place for the next steps. Select the column, and choose Cut. Then select the first empty column (here J), and paste. After this, select the column C again with the right mouse button, and click "Delete". Our spreadsheet should now look like this:
Wow! I can now immediately see, that the beers in black text are the ones to look for when shopping around, the orange ones are new and available at the festival, the green ones I've already had, and the gray ones have retired.
Next we're going to move back to Word to prepare the list for printing, but first we'll do a couple more steps in Excel.
6.1 Select all the data in Excel (click on the "<>" cell in the top left corner). Select "Sort..." from the "Data" menu. Select "No header row" from the bottom, and Sort by Column A, then Column B (see image). Click OK. The breweries will now be in the correct alphabetical order, no matter which policy you had in (possibly) shortening their names.
6.2 Resize the columns A-H in Excel so, that everything is visible, but there is not much extra space (especially not in the data area, the beer names are, of course, of greatly variable widths, and some may be cut). Select all data from the columns A-H, and copy it into clipboard.
6.3 Create a new document in Word. Select "Document..." from the "Format" menu, and make the top and bottom margins smaller than default, say 1cm each. Left and right margins could be wider (like 3cm) to accommodate the tabs done manually in the final step. After this, paste the data you copied in 6.1 from Excel. You should now have a Word document with a formatted table like this (again, note that I'm using partial dataset here):
6.4 If necessary, you may adjust the column widths in Word by selecting the whole table from the crossed arrows symbol on the top left corner, and dragging the vertical column lines left or right. Note: this is very slow if you have thousands of rows of data. You can also fine-tune the table by making changes in word wrap settings by column, if you want the beer name to wrap to the next line, but brewery name to just be cut along right column border, for example - but I won't go into the dirty details on that.
6.5 When the data is nicely (enough) laid out and filling the page efficiently, you can make the table easier to read by removing the vertical lines. To do this, select the whole table (top left, remember), and select "Borders and Shading" from the "Format" menu. From this dialog, choose "Grid" from the "Setting:" part on the left, then remove the vertical lines by clicking on the three middle buttons under the Preview window on the right side of dialog. You can also set the line style and width here, if you like.
6.6 We now need to toggle off the display of automatic gridlines of the table. Select the "Table" menu, and uncheck the "Gridlines" option.
Next we print the document as PDF file, and finally print the PDF on paper, using the Booklet feature in Acrobat. There are probably many ways to accomplish this, and this part is slightly different in Windows systems, but here's how I did it...
7.1 Select "Print..." from the "File" menu in Word. Instead of printing directly into printer, click on the "PDF" button, and select "Save as PDF...". Give the PDF a name, and save it somewhere where you can find it :).
7.2 Open the newly created PDF in Acrobat. Note: for the Booklet function to work, you'll need version 8 or newer. If you're not as frustrated at this point as I was, you can create a fancy cover page for your booklet, and add it into the PDF. I settled for a blank page (which my wife later decorated with stickers from some game in the Guitar Hero series ;).
7.3 Select "Print" from the "File" menu in Acrobat. From "Page Scaling:", select "Booklet Printing", "Binding: left" (unless you want to read from right to left in Japanese style ;), and make sure "Auto-Rotate pages" is selected (it isn't by default) if you have odd number of total pages, or you'll end up with a bunch of upside-down pages. If your printer has an automatic duplex unit, you can choose "Both sides" as the "Booklet subset:" option. Otherwise, you'll need to select "Front side only", print once, put the papers back into the printer, and print again using "Back side only". You may need to experiment with the proper page orientation first.
At this point, you should have a pile of paper, that will become a booklet after stapling from the center.
8.1 Measure and mark the center point of the pile, and staple following this line. You'll probably need to fold the other half to reach the middle, unless you have an exceptionally large stapler at hand.
8.2 Fold the booklet along the staple line. If you want to look as professional as myself, you should create a tabbed right edge with the first and last breweries found on each double page listed.
To make the tabs more durable, I put some office tape on top of the text in each tab.
The cover page - not that refined.
DONE! :)
All material on this page is © 2010 Seppo Äyräväinen (sayravai on Ratebeer).