Posts from November 2008

Fixing Postcodes

Published 15 years, 3 months past

In case anyone’s interested, I finally updated the ZIP archive of all the countries and postcodes from the 2008 ALA survey.  The two files are sorted like before, but this time leading-zero postcodes haven’t had their leading zeroes stripped by Excel.  Oh, Excel.

I have learned way more about Excel’s “helpful” handling of CSV and text imports than I ever wanted to know.  The basic drill is, if you want to open a CSV or text file but don’t want Excel to be “helpful”, don’t drop the file onto Excel or double-click the file icon.  No no!  That would be too easy.

Instead, launch Excel, select “File > Open”, and then select the CSV or text file you want to open in the file browser.  Go through the Text Import Wizard carefully:

  1. Tell Excel that the file is delimited on the first screen.  (Or, if it isn’t, then don’t.  I bet it is, though.)
  2. Tell Excel what delimiter you’re using on the second screen.
  3. Then—this is the crucial bit—on the third Wizard screen, select the columns you don’t want Excel to “help” you with and set them to “Text”.  Be careful about setting all the columns as “Text”, though: if you have non-ASCII characters, Excel will “helpfully” replace their contents with octothorpes when you try to export the data later.  Such “help”!  It’s so “helpful”!

Yay!  An open file where the data is all in its original state!

Now you can save the file as an Excel workbook and it should (but please note my use of the word should) leave your data alone.  Ditto if you do “Save As…” to export to CSV or text again, which you might do if you run some calculations and want to capture the result in a basic, portable format.  But remember!  If you ever want to open those CSV/text files in Excel, you can’t just open them.  You have to go through the whole text-import process again.

So the survey files now contain actual useful data, especially for countries where postcodes can start with zeroes.  (Which is a lot of them.)  The files also have the usual bits of abuse that come along with daring to ask people to supply optional information, because I didn’t even try to filter that stuff out.  So, you know, naughty words ahead.

In part, I’m posting this to leave a record for anyone else who runs into the same problems I had, and also to remind myself of what has to be done next year.  Also to provide a heads-up to anyone who’d like to grab the fixed-up data and do fun mapping stuff with it, as did some commenters on the previous post.


Shock and Awe

Published 15 years, 4 months past

I almost feel like the Presidential election didn’t happen.

You see, for the entire second half of Election Day, from almost noon until after midnight, Eastern time, I was aboard a Continental flight to Tokyo.  We had video-on-demand systems but not live satellite television, so as we arced over Canada, Alaska, and the northern reaches of the Pacific Ocean, we flew in ignorance.  As Jeremy Keith put it regarding his own flight to Japan, we were aboard Schrödinger‘s Airplane.

For me, the wave collapsed as we began the initial descent toward Narita.  One of the flight attendants, having announced that they were starting the initial-descent procedures and would like us to check around our seats for any personal items we might like to start stowing, added:  “And for those of you interested in the results of the election, we have a new President: Barack Obama.”

There was a burst of applause from the economy section of the plane.  In business class, there was silence.

Well, not quite.  I was myself sitting in business class, thanks to a great big pile of reward miles and some lucky timing in calling the airline.  As I heard her say Obama’s name, I let out an involuntary “Wow“.  Because until that moment, deep down I had believed, truly believed, that Mr. Obama would not win the Presidency.  That was not the outcome I desired, but it was the outcome I expected.

I am in many ways ashamed of my doubts and fears, because I had thought less of my fellow Americans than they deserved.

Since then, from here in Tokyo, I’ve felt weirdly disconnected from what’s happened.  In time zone terms, I’m fourteen hours in my home’s future, half a day ahead of everyone back home.  But because I received word after it was all over and soon after slept through America’s Wednesday daylight hours, I feel like I’m a day behind.  Time and distance combine to create a feeling of disconnectedness from the end result, as though I’m getting word of election results in Germany or India or Australia: interesting, but something seen at a remove.

It’s odd.  I’m used to being an observer, but this is something else entirely.  I think it’s pure astonishment.


Browse the Archive