Some time ago, I had a need to look up an assembly in the GAC or some such operation—can’t remember exactly what I needed to do—but was surprised to find no easy way to use a Visual Studio command line utility or even PowerShell to do the work I needed.  Well, a few days ago, I come across this post on how to produce a list of all assemblies in the GAC.  Sweet.  However, let’s do a little more tinkering. 

First, let’s use the windir environment variable so I can make my script a little more portable across OSes.  Second, let’s go ahead and pipe the output to System.Reflection and go ahead and get something nice—like, say, the fully-qualified name of each assembly.  Now we’re talking!

Get-ChildItem $env:windir\assembly\GAC_MSIL -filter *.dll -recurse | %{[System.Reflection.Assembly]::LoadFile($_.FullName).FullName}

I’m writing this post for the same reasons that I write most of my posts:

1. Because once I solve a problem, the solution tends to decay quickly from my mind.  If I don’t get it down on paper in some coherent form, it will be gone—and a month from now, I’ll likely need it again. 
2. And because I’m sure there are many better ways to solve this problem.  My horrible googling skills came up empty, so this is the best I could do.  It works, but I’m sure there are more elegant ways to do this.  Perhaps a more savvy script writer could educate me.

Overview

At work, we develop components, gadgets, and gizmos that others use to do their work.  When we have a new release of said gizmo or some other relevant event, we send out an email to our internal customers with the announcement.  Recently, we learned that there are many other employees in the company that are not on our email distribution list—folks who may also benefit from our products and services—so we wanted to contact these other people to see if they’d like to be added to our mailing list. 

Later, I may post on how I compared our current mailing list to this other list of people to find out who we were actually missing.  For now, assume that I have already sent out my solicitation email and have begun receiving replies.  In my solicitation, I said something to the effect, “if you would like to be added to our list, please reply to this email with the phrase, ‘add me to the list.’”  I wasn’t specific about where to place the phrase, so some respondents put the phrase in the body of their reply while others put it in the subject line of their reply.  Still others replied back with “no, thanks” and a few even replied that they wanted to be added, but didn’t use my phrase: eg. “sounds great!  Sign me up!”

I knew I didn’t want to go through all the replies by hand and add the affirmative replies, one-by-one, to my email list.  Rather, I wanted to find a way to programmatically process the replies and build a single list of affirmatives that I could add, in bulk, to my distribution list.  Here’s what I did:

Step 1: Export to the replies from Lotus Notes into a form I could process

Lotus Notes is not my email client of choice—that’s simply what I’m forced to use.  The SMTP infrastructure at my company is dark and mysterious; while I’m sure there are APIs I could program to, I decided that the quickest approach to extract the emails I wished to process was to simply do it the manual way.  I found that I could checkmark all the email I was interested in, then select the menu File > Export… and produce a single text file I could work with.  The file looked something like this:

Step 2: Transform the replies into some delimited-type format that I could better process

Now, I had to get this dump of email into some form I could work with.  The first thing that came to mind was to see if I could use Powershell to transform the email dump into a delimited file.  If I could get the fields I needed to process into a delimited file, I could write a LogParser query to identify the individuals wishing to be added to my list.  Here’s the Powershell script I came up with:

$delim = "|"
$allResponses = Get-Content d:\Docs\email_dump

$principals = $allResponses | Select-String "^Principal:"
$abstracts =  $allResponses | Select-String "Abstract:"
$subjects = $allResponses | Select-String "Subject: [^My]"
$inetfroms =  $allResponses | Select-String "INetFrom:"

$newFile = ""

for($count = 0; $count -lt $principals.Count; $count++)
{
    $newFile += ($inetfroms[$count] -replace "INetFrom:  ", "") + $delim + `
        ($abstracts[$count] -replace "Abstract:  ", "") + $delim + `
        ($subjects[$count] -replace "Subject:  ", "") + $delim + `
        ($principals[$count] -replace "Principal:  ", "") + "`n"
}

$newFile > D:\Docs\delimResponses.txt

So, let me explain some of this….  I found that there were probably four relevant fields for my objective:

• Principal—the English name (distinguished name) of the respondents
• Abstract—the first line or first X number of characters of the reply
• Subject—the subject line of the reply
• INetFrom—the email address of the respondent

Most respondents placed the “add me to your list” phrase either at the beginning of their response (Abstract field) or in the subject line (Subject field).  Of course, I needed the respondent’s email address (INetFrom) to add to my distribution list, and the respondent’s full name (Prinicipal) was just nice to have in case the email address was too cryptic and I wanted to know the actual respondent’s name.

Anyway, I used the Select-String cmdlet to find all the instances of each field.  For the most part, the pattern match (a Regular Expression) was easy; however, initially, my Select-String pattern for the Subject field was just “Subject:”.  Unfortunately, this pattern not only picked up the respondents’ subject lines, but also the subject line from my original email, which was tacked onto the bottom of every email I received.  I had to figure out how to make sure not to capture that subject line.  So, I came up with the pattern “Subject:  [^My]”.  In Regular Expression-eze, this means, “match on all lines where the line begins with “Subject:  [and any characters that don’t begin with ‘My’]”.  After all, my original subject line was “Subject:  My team’s distribution list”.

[Side note: Regular Expressions drive me batty.  Fortunately, my buddy Rob is especially gifted at them and I tend to contact him when I get in a bind.  This tool was also helpful.  Recently, Jeff Atwood recommended this book as a helpful tool in crafting your regular expressions—think I’ll pick that one up.]

So, my script built arrays for these four fields from each email; then, I wrote a loop to iterate through the arrays, building a delimited line item for each index of the array.  I assumed a couple of things: 1) that each array was built starting at the top of the email dump file and working toward the bottom (such that, say, index 23 of each array contained values from the same email) and 2) that each email actually had all four fields.  My first assumption appeared to be true, as I spot-checked the results against the raw data and the values seemed to line up; however, it turned out that there were a few emails actually missing the INetFrom field, which threw off all the line items processed after the errant email.  Perhaps I could have written more script to catch such problems, but instead, I just identified the errant emails, added the missing fields in the raw email dump, then ran the script again and all was successful.  The resulting file looked something like this:

Notice the dollar sign at the beginning of the second field.  This is the Abstract field.  In the raw email dump, the Abstract field name is actually “$Abstract:”.  In regular expressions, the dollar sign is a special character and to treat it as a literal, you have to escape it.  In my replace operation, I tried “\$Abstract:  “, “\\\$Abstract:  “, and “\\\\$Abstract:  “, but nothing seemed to work.  Frustrating, but I can live with it.

Step 3: Query against my delimited representation of the replies to produce a report of the affirmative replies

With the raw email dump processed into a delimited format, I can now query for the affirmative responses with LogParser.  Here’s the query I came up with:

SELECT
    Field1
FROM delimResponses.txt
WHERE Field2 LIKE '%add me to%' OR Field3 LIKE '%add me to%'

I saved this query in a file called process_email_responses.sql, then, from the command line, executed this:

This query created a list of email addresses that I could just paste into my distribution list (manually, of course, using the Lotus Notes client).  The query yielded about 95% of the affirmative responses; to get the remainder (those that didn’t explicitly include the phrase “add me to your list” in either the body or subject line of the reply), I ran the reverse query and manually reviewed the results:

SELECT
    Field1
FROM delimResponses.txt
WHERE Field2 NOT LIKE '%add me to%' AND Field3 NOT LIKE '%add me to%'

Step 4: Add the affirmative emails to my distribution list

As I said, I took the results of my first query (about 95% of the affirmative responses), manually added the remaining affirmative responses from the second query, and then manually pasted the resulting email addresses into the Lotus Notes Edit Group window of my distribution list.

Over all, this stuff took me a couple of hours to come up with—mainly because I’m a bonehead.  I probably would have spent about the same amount of time processing the list manually—of course, I probably would have made mistakes, too, and ended up adding someone to the list who didn’t want to be added or vice-versa.  At least now that I’ve documented my efforts here, when I send out more solicitation emails in the future (we do anticipate doing this a few more times, at least), I should be able to significantly reduce the processing time of the results. 

The biggest question I have, though, is: is there a smarter way to use Powershell to process a list like my raw email dump?  A list where the fields are listed vertically with some funky character delimiting records?  I couldn’t find examples of anyone processing such files, but I’m glad I found at least one clunky way to do it.  I guess the other question is: did I really need to use LogParser or could I also get Powershell to do my query?  I probably could have gotten Powershell to do my query—with Regular Expressions, no less—but I guess I just felt more confident with LogParser, at least for now.  Anyway, if you have answers or suggestions to these deep and troubling questions of mine, please feel free to comment.

Warning: this post will either a) show that I’m obsessive/compulsive and must be stopped, b) prove that I’m a fool/moron and should be left to my own devices, or c) all of the above.

I’ve been reading David Allen’s Getting Things Done (GTD) with the hope of actually getting something done in 2009.  One of the core concepts of GTD is having a trusted system in which you place data on all your projects, next actions, calendar data, tickler entries, etc.  As a geek, I’d love my trusted system to be some electronic widget that can easily sync with my home and office Outlook, Google Calendars, and so on.  But should that widget be something I combine with my phone—as in a Blackberry, smart phone, iPhone, or whatever—or should it be something separate as in a Pocket PC, PDA, or the like?  Would a tablet PC or one of those tiny notebooks be a better choice?

Surprisingly, I’m finding that many tech-savvy GTDers are going to the low-tech moleskines, Hipster PDAs, and such.  For one thing, there’s no boot time with these low-tech organizers.  (Matthew Cornell has cataloged some discussion points on high-tech versus low-tech trusted systems here.)  So, for the time being, I’m going low-tech.

The wife bought me one of those Franklin-Covey planners and I began loading it into the seven-ring binder when I encountered a dilemma: the paper planner includes 17 double-sided blank address pages.  A little box in the upper right corner seems to shout, “write a letter in me!”  But what letter do I write?  If I write “A” on the first side of page 1, do I write “B” on the backside of page 1 or do I write “A” again and write “B” on the first side of page 2?  How many sides should I allocate for surname letters I perceive to be more popular, like R, S, and T?  To solve this dilemma, I thought I’d take a scientific approach to the problem: namely, google around to see if anyone has posted frequency data on surnames beginning with the various letters of the alphabet.  At this point, I would just be happy with data relegated to the United States.

Alas, as far as I can find, no one has published such information.  However, the United States Census Bureau has published surname frequency data from the 1990 Census (more info here).  This report is a sampling of around 88,000 surnames ranked by popularity and percentage frequency in the sample population.  Hmm.  How could I coalesce this information to show the percent frequency of surnames that begin with the various letters of the English alphabet?  I know, PowerShell (if you haven’t noticed, I’ve been on a bit of a PowerShell rant of late)!  Actually, the solution I came up with combined PowerShell and LogParser. 

I downloaded the report and found that it was persisted in a fixed width format.  Unlike Comma Separated Value (CSV) files, PowerShell doesn’t seem to deal well with fixed width data files.  MOW did some intense work here, but that looks like a lot of work, so I just borrowed this idea to convert the fixed width file to a CSV file and then process the results:

With the CSV file in hand, I could then write my LogParser query to perform my calculations.  I would have liked to use Linq for my queries and thus keep my solution purely within the domain of PowerShell, but I’m not quite sure how to use Linq in PowerShell—or if it’s even possible with version 1.0.  That’s one more research item to add to my list.

Anyway, while constructing my LogParser query, I found something interesting: the percent frequencies of the surnames in the sample don’t add up to 100%.  Rather, they add up to something like 147%.  I wasn’t expecting that.  So, I guess if I want to get an accurate percent frequency of a given surname (and then sum those percentages together for all surnames beginning with the same letter), I’d better divide the value by the true total, 147, and use those results.  Of course, I don’t want to hard-code that total value—I want my query to calculate it for me.

To do this, normally I would nest a SELECT SUM(Percent) AS TotalValue within my larger SELECT query that produces the report I’m interested in, but LogParser seemed to have a problem with this kind of nesting—my guess is that LogParser doesn’t like two or more SELECTs together that are reading from the same file.  Alright, I’ll perform two separate calls to LogParser: one to get my total percent value and the second to use this value to calculate the percentage of each letter in the sample population.  One thing I learned by doing that first query is that I can actually capture the LogParser results back into a PowerShell variable:

Now, with that value in hand, I can use PowerShell and LogParser to render the report I’m looking for (notice that I’ve included a calculation for recommended Address Page allotment into my query by calculating the percentage frequency by 34—the number of blank address pages):

And my report in the PowerShell console:

And now I have a more reasoned way of allotting my blank address pages to particular letters of the alphabet without just guessing.  Of course, the page allotments total 35—I only have 34 to dole out.  And what happens if I meet someone with a last name starting in U or Z?

After I explained this work to my wife, she just said, “why don’t you keep the pages blank until you need them?  If you end up needing more, you can always buy more.”  Huh.  Never thought of that.  Guess that’s a more rational approach.  Darn it.

Last year, I bought a Gold Lantern digital picture frame.  I also bought a 2gb SD card on which I dropped over 2000 pictures.  The frame worked well cycling through all the pictures—for about three months, anyway, at which point it basically blew up (the brightness of the picture dimmed to almost total blackness, the frame smelled of burnt rubber, a thin coil of smoke rose from the AC adapter, etc.).  I replaced the AC adapter with another one from the company, but the problems persisted, so I’m pretty sure it was the frame, not the adapter. 

Out of frustration, I threw away the Gold Lantern frame and bought a new Nextar digital frame.  I plugged in the frame, slipped in the 2gb SD card I used successfully in the old frame, turned on this new one and…nothing.  The thing basically just hourglassed as, I suspect, it was attempting to load the pictures.  Later, my wife tried her 1gb SD card that she uses in her digital frame at work and the Nextar frame played it with no problem.  Thinking then that maybe the frame couldn’t handle a large quantity of files, I made dramatic reductions in the number of photos on my 2gb card all to no avail until eventually I slashed the number down to 20 pictures—which the [expletive deleted] frame happily played.

One idea I had during my futile iterations of edits to my card was that maybe the frame was having a problem rendering my pictures down to its 640 x 480 pixel screen size.  Most of my pictures were taken by my 8mb point-and-shoot camera resulting in 1+ Mb, 2272x1704 JPG files.  I thought that if I could resize my photos down to approximately 640x480, I could ease some of the burden on the frame’s processor—the files themselves would be smaller in size and little-to-no work would need to be done re-rendering the photos to the smaller size of the frame.  Normally when I resize a photo, I do it by hand in some editor.  I was not about to resize over 2000 photos, so what could I do? 

PowerShell, baby!  I wrote the following script to shrink all photos in a given directory, with due credit to this post, this one, and this one:

Unfortunately, shrinking the size of my photos didn’t seem to solve my problem, but I thought I’d post this script anyway because it might be helpful in the future.  As an aside, anyone know why the Gold Lantern frame could play all my photos but this Nextar one couldn’t?  There must be some spec associated with digital frames that will tell me the limits of how many photos a frame can process, how big these photos can be in file size and pixel width/height, etc.  I didn’t do a whole lot of research in this area, but it would sure be nice to know before I buy any more frames.

Over the holidays, I bought a Sansa c250 mp3 player, a) because it was cheap and b) because it contains a microSD card expansion slot, which, in theory, would allow me to expand the disk capacity indefinitely.

Before I learned how to upgrade the firmware on the player, I was relegated to using software like Windows Media Player to move MP3s onto the device—a pox on all devices that force you to use special software to move files onto them (like this dumb digital ornament I bought for my dad—sorry, Dad).  At any rate, I wanted to begin using the device and learn how to use the expansion slot later, so I built up a playlist of tons of my favorite podcasts.  Since the player’s disk was only 2gb, though, I had to be careful not to pack too many files into the playlist.  So my question, how do I know the sum total of all the files I’m trying to sync to the player?

Windows Media Player will tell you the total number of files you have in your playlist, it’ll even tell you the total number of hours and minutes your playlist represents, but as far as I can tell it won’t tell you total size of the files represented by the playlist (even though the Library tab will tell you the size of each individual file).

Fooey on that.  PowerShell can tell me the answer.  I wrote this script to calculate the total size of the files:

The script produced the following results:

So it looks like my playlist will fit nicely on my 2gb player.  Problem solved.

I maintain several Amazon wishlists broken out by the particular topics in which I’m interested.  These wishlists have become the central repository for most/all things (not just books) I’m contemplating purchasing in the future—already, you can buy a great many things from Amazon or their affiliates, but their cool Universal Wishlist Widget kind of seals the deal.

But I have a problem: whenever I visit my favorite book store, my wishlists out on the tubes are useless.  I need to be able to print my lists. 

As far as I can tell, there’s no easy way to do this.  Sure, I can print each page from my browser—I can navigate to page 1, hit the print button in my browser, then navigate to page 2, rinse and repeat.  But that’s lame and time-consuming.  Plus, it would print lots of information and pictures that I don’t need—and needlessly kill my ink cartridge.  Ideally, I’d like to just be able to print a list of the books and authors on my lists.  Even better, I’d like to sort my list alphabetically by the last name of the author since that’s how book stores order their books.  So, here’s how I solved my problem:

Requirements:
Microsoft PowerShell (free)
A free Amazon Web Service account
XML, XSLT, and your favorite browser (my script uses IE)

Step 1: Construct the AWS (Amazon Web Service) url and do my service calls

I’m using PowerShell as the primary engine to do all these operations.  I could have written C# code or the like to perform these tasks, but using PowerShell seemed a little slicker.

Figuring out how to construct the web service URL would have been tough were it not for this article on pulling back an XML representation of one’s wishlist.  I also should credit this article for letting me borrow the PowerShell script they used to call the AWS service.  (Alternatively, I could have scraped my wishlist to get the data I need.  Here’s a hilarious article on someone doing just that—to quasi-subversive ends.)  Here’s my URL syntax:

[Side note: I was glad to see Scott Hanselman’s post on how he highlights code in his blog posts.  This very post marks the first time I’m using Live Writer.  I guess we’ll see how this turns out.  After that, I intend to experiment with how I can use Live Writer to highlight my code.  I suspect, though, that most of these syntax highlighting plugins focus on highlighting the syntax of standard programming languages like C#.  I wonder how these tools deal with PowerShell script?]

Back to the topic at hand…notice the placeholder for the ProductPage querystring argument.  AWS will only send back a few entries per page.  So, I had to write a loop to call the service several times—once for each page—changing that ProductPage value with each call.  Also, with every iteration, I had to persist each service call result, so I created a second XML document that I used to stitch together the results of each call.

Step 2: Reformat the author names for sorting

For each book, AWS returns one or more author elements (or sometimes one or more editor elements).  The value is the full name of the individual (eg. John J. Doe).  Sometimes, the individual has a suffix like Jr., Sr., III, etc.  In order for me to be able to sort by author last name, I had to write script to parse the name and reformat it as last name, first name.  Here’s what I did:

Clunky, yes, but it seems to get the job done.

Step 3: Use XSLT to generate my report

After reformatting all the author names in my stitched up xml file, all my script needs to do now is save the file to disk, then open it up in a browser.  When I originally created my conglomerated XML file, I assigned it a style sheet, as so:

As soon as I load the XML file up in a browser, the browser will do the heavy lifting for me, using the associated stylesheet to transform the XML file into whatever…in this case, my sorted list I’m looking for.

And here are the final results:

Notice the two books at the top of the list with no authors: turns out these books have editors, not authors.  If I wanted to be really fancy, I probably could have wrote more sophisticated XSLT to treat editors like authors and alphabetize accordingly—I might save that for a rainy day.

Another interesting situation I encountered is that AWS returns all the entries I’ve ever made to my wishlist, even though I’ve deleted several entries over the years (clicked the Delete button in the Web UI).  There’s no “deleted” flag, but there is a QuantityReceived element that seems to serve as a deleted flag.  I had my XSLT script filter out entries where the QuantityReceived value was greater than 0.  If you’re interested in this stuff, I’ve posted both my PowerShell script and my XSLT file for your use.  Feel free to post back with suggestions for improving these operations.

Post Script: I printed my list and walked it into my favorite book store, and immediately encountered a problem…how can I move from section to section, thumbing through 6 pages of authors and books I want to purchase?  One improvement I need to make is to group my sorted list by category: fiction, non-fiction, science, philosophy, etc.  As far as I can see, the AWS service doesn’t offer such category information.  Hmm.  I wonder if I could take the ISBN number of each book (AWS does provide the ISBN) and call some other service to get the book’s category.  If I can’t do that, Amazon does provide a Comments field for each book.  I could type in a category in that field.  That would be a lot of work, but the results might be worth it.  The AWS service doesn’t appear to return this field, but I might be able to alter the call to get this field returned.  Looks like I have some more work ahead of me.