Thursday, December 31, 2009

We're having a hard-thinking Hogmanay!

Mike

Posted via email from michaelpaul's posterous

Tuesday, December 29, 2009

Holiday Drinking Guide - Episode 40

Hooray for Episode 40! And beer! And alkyhall. Through our extensive research, we're going to show you how you can ring in the new year with some new GF drinks you may never have heard of... so get ready to have some fun! In this episode, we review: Redbridge Beer, Woodchuck Draft Cider, Blue Ice Vodka, Frangelico (hazelnut liqueur), and a little Parrot Bay (coconut rum). In addition to the products we tried out on this episode, here are some other gluten free alcohol options: Beers: Bard's, Green, New Grist Vodka: Tito's (corn based), Krome (corn based), U4rik (grape based), Ciroc (grape based), Teton Glacier (potato based) Tequila: Don Julio, Patron, Jose Cuervo
Formats available: MPEG-4 Video (.m4v)

Monday, December 28, 2009

Pancake Showdown: Pamela's vs Gluten Free Mama

In this Pancake Showdown, we pit our undefeated champion, Pamela's Pancake Mix, against a new contender, Gluten Free Mama's. Whose cuisine will reign supreme? The battle is on!
Formats available: MPEG-4 Video (.m4v)

Jake Bakes Marshmallows - Episode 38

We never thought of taking marshmallows beyond their sugary base, but the Jake Bakes company believes this industry has a bright future. We reviewed 5 of their custom marshmallows and, while some of them didn't impress, others were right on. Find out which ones deserve to be in your cupboard in this episode of GFree TV!
Formats available: MPEG-4 Video (.m4v)

Saturday, December 26, 2009

His and hers Sick Station (DIY)

Mike

Posted via email from michaelpaul's posterous

Conte's Stuffed Shells - Episode 37

We liked Conte's raviolis when we last reviewed them, but have you ever wondered if those stuffed shells you've seen are worth a try? Well, they are! But they're not without their misgivings: the cheese never sticks to the shells, causing a one-after-the-other eating experience. On the bright side, you get to decide which to eat first: the ricotta cheese or the shell. Add your favorite pasta sauce to complete the meal.
Formats available: MPEG-4 Video (.m4v)

Friday, December 25, 2009

Zing Bars - Episode 36

Zing! Pow! Holy Cow! These Zing Bars are pretty cool. A bit pricey, but they pack a lot of healthy stuff into a tasty snack bar. We review 5 different flavor combinations: Blueberry, Cranberry Orange, Oatmeal Chocolate Chip, Chocolate Peanut Butter, and Peanut Butter Chocolate Chip.
Formats available: MPEG-4 Video (.m4v)

Found a companion who's as tired as I am.

Mike

Posted via email from michaelpaul's posterous

Sunday, December 20, 2009

Mikuni Sushi - Episode 35

Mikuni Sushi just unleashed its new gluten-free menu, so we had to visit and try things out. While they made a nice first attempt, we found that we've had better luck making other rolls of their gluten-free with slight modifications. For the sushi aficionado, this would be a bland experience. To gluten-free people who have been dying for sushi, consider this place your resource if you're ever in the Sacramento area.
Formats available: MPEG-4 Video (.m4v)

GFTV E34 - Norlander's Mesquite Sauce

Formats available: MPEG-4 Video (.m4v)

Saturday, December 19, 2009

Natasha's Health Nut Cookies

gfreetv, gluten free, gluten-free, celiac, review, food,
Formats available: MPEG-4 Video (.m4v)

Kettle Cuisine 3 Bean Chili

Beans, beans, the magical fruit... the more you eat, the more you toot! The more you toot, the better you feel... Let's have beans for every meal! OK, OK, maybe not every meal, but this company makes a 3 bean chili that's at least good enough to have on more than occasion!
Formats available: MPEG-4 Video (.m4v)

Wednesday, December 16, 2009

Pacific Catch (San Francisco, CA) - Episode 31

After swimming the seas of Golden Gate Park for the day, we sniffed out the bait and got hooked on the line of the Pacific Catch restaurant in San Francisco, CA. On the corner of 9th Avenue and Lincon Way, they served up some shrimp ceviche, a sake sake roll, and two Chalkboard Specials: one with shrimp served California Grill Style, and one with salmon served Baja BBQ Style. Will they reel us in, or will they let these two fish get away?!
Formats available: MPEG-4 Video (.m4v)

Monday, December 14, 2009

Enjoy Life Cereals - Episode 30

3 different cereals reviewed from Enjoy Life: cranapple crunch, very berry crunch, and cinnamon crunch.
Formats available: MPEG-4 Video (.m4v)

Friday, December 11, 2009

Ginger Elizabeth Chocolates - Episode 29

If you're anywhere near Sacramento, CA, you'll want to check out Ginger Elizabeth Chocolates. We treated ourselves to a small cup of hot sipping chocolate before headed home to review six different kinds of their gourmet chocolate confections. You MUST watch this, and you MUST have some! They're that good. And no, they didn't pay us to say that, and yes, we bought the chocolate we reviewed. Looking forward to our next visit.
Formats available: MPEG-4 Video (.m4v)

Thursday, December 10, 2009

Automating Excel copy and paste jobs across multiple sheets

When I first started this project, I found myself with this Excel workbook that had a few sheets in it, each sheet containing an extract layout for data of a given record type. For example, if the record type was "Person" the extract layout might look like this:

FIRST_NAME
LAST_NAME
PHONE_NUMBER

etc.

Those things in capital letters are called fields, or headers. In my example above, there are 3 fields/headers. In reality, I currently have a record type that has 118 fields. The problem, as you'll soon see, is that these headers are shown in a top-to-bottom format, instead of left-to-right. My job is to get these guys showing in a left-to-right format. (That's easy; there's a Paste Transpose feature in Excel that does this for me--but it doesn't delete all the other crap in the spreadsheet that I don't need.)

I work with a program called PDI (Pervasive Data Integrator) that doesn't much care for the way these fields are presented in my handy dandy Excel spreadsheet. Instead, it prefers to have those headers across the top of the very first row, like this:

FIRST_NAME LAST_NAME PHONE_NUMBER

Over time, this small Excel spreadsheet grew from having 3 sheets (record layouts) in the workbook to 13 at the time of this writing.

My goal was to automate the process of moving those fields from a top-to-bottom format to a left-to-right format while simultaneously deleting all the extra junk that appeared to the right of each field in the spreadsheet, like the length of the field, the field's data type, which database table it came from, etc.

So basically my goal was to turn this:

FIRST_NAME CHAR 12 PERSON 5
LAST__NAME CHAR 17 PERSON 5
PHONE_NUMB CHAR 10 PERSON 5

...into this:

FIRST_NAME LAST_NAME PHONE_NUMB

See all that extra junk I need to get rid of? At first, I recorded a macro in Excel, but I found that this was simply not acceptable for a number of reasons. The main reason is that it wasn't dynamic. It always selected a certain number of fields, and did whatever it was supposed to do. So, if I had only 10 fields I wanted to do my wizardry on and I used my original code, it would have selected 150 fields (from A2:A151 in Excel terms) and when I then imported those fields into my PDI map, PDI would recognize all 150 fields instead of just the 10. Making matters worse, PDI doesn't let me simply delete all of those extra fields at once (it's buggy like that) so I can only do groups at a time.

Basically, it's a waste of time.

So, I then took that code and decided to make it generic and adaptable. But then there was another problem. "Great, I can get it to automate the process on one sheet, but what about the other 12?" Imagine if there were a hundred sheets. Scale it to a thousand. You get the idea. Time-consuming to do, unless you automate that process.

How did I do it? I did it by using a loop within a loop. The first loop is a For Loop. It says, "For sheet 1 through the very last sheet, do this stuff."

The second loop is a Do While Loop. This loop says "while the currently selected cell is not empty, do this stuff." All it does is examine the cell it's on to see if it's blank or not. If it's not blank, it moves down to the next cell and evaluates it in turn. It does this until it encounters a blank cell, indicating that there is no more data I need to worry about. The key here is that it keeps track of the cell number that it's on.

So, I now have a variable keeping track of my starting and ending cells (myRange1 and myRange2, respectively). That means I can store those guys into a final variable called finalRange, which will be used to tell Excel which range of cells to select.

Cool, huh?

The code below will look like crap on this blog since it's in such a narrow column, but copy and paste it into your Excel workbook's VBA window and it'll work.

Here's the code:

Sub PrepForPDI()
'
' PrepForPDI Macro
'
' Keyboard Shortcut: Ctrl+m
'

'Set up the variables we'll be using
Dim myCounter, endCounter, mySheetCount, i As Integer
Dim myRange1, myRange2, finalRange

'Step 1. Starting at the first sheet, do the following. Repeat for each one until the very last sheet.
For i = 1 To Sheets.Count
Sheets(i).Select 'Select sheet i (starting with 1 since i = 1 above)
myRange2 = "A2" 'This is our starting range
myCounter = 2 'Counter that keeps track of currently selected cell

'While the selected cell is not blank, increase my counter and update my ending range
Do While Range(myRange2).Text <> ""
myCounter = myCounter + 1
myRange2 = "A" & myCounter 'The result of this variable would be A1, A2, A3, depending on where my counter is at
Loop 'go back up to the Do While statement and repeat until a blank cell is encountered

'Once a blank cell is found as we go down our column, jump out of the loop and execute the following:
myRange1 = "A2" 'The start of our selection range in the Excel spreadsheet
endCounter = myCounter - 1 'Subtract 1 from the counter since the counter was last on a blank cell
myRange2 = "A" & endCounter 'The end of our selection range in the Excel spreadsheet
finalRange = myRange1 & ":" & myRange2 'This would look like A2:A114 for example

'Now that we have our final range, select it, copy it, paste (transpose) it, delete the
'rows below row 1 since we only care about preserving this new row 1, and select cell A1
'so that we're always looking at the very first field on any given sheet.
Range(finalRange).Select
Selection.Copy 'Copy it. :-)
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rows("2:150").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Next i 'Now i = 2, so when the For statement loops back to Step 1, it will select Sheet number i, or Sheet number 2.
'Select the very first sheet to bring us back to home base.
Sheets(1).Select
End Sub

Tuesday, December 1, 2009

Farmer's Kitchen Cafe - Episode 27

While passing through Davis, we stopped at Farmer's Kitchen Cafe for some lunch. We were blown away by this unassuming eatery, and found another hole in the wall that's worth the money. What did we order? Watch and find out!
Formats available: MPEG-4 Video (.m4v)