Excel and OLEDB in .NET

Posted April 18, 2006 1:01 PM Categories: .NET
While attempting to do some data merging across several Excel spreadsheets, I found some interesting behavior in how OLEDB determines data types of columns with an Excel source.

For me, the issue manifested itself with a column that looked like the following (shortened for brevity's sake):

10132
10133
10134D
10134
10135


By default, Excel looks at the first 8 rows of each column to determine the type.  If most of the items are numeric, it will create the column as an int or double type.  The problem is, if the column also contains text data, like the one above (10134D), the column can't be converted to a numeric type and OLEDB will just drop the value with no error or explanation.

With the above data, since 4 out of 5 are numeric, OLEDB turns the column into a numeric type, and the 3rd row's column of that data would be NULL.

I tried several ways of fixing the issue but found that the only solution that worked was to set the IMEX property in the connection string to 1 in the Extended Properties section like so:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=blah.xls;Extended Properties="Excel 8.0;IMEX=1;"

An additional extended property you might find useful is "HDR=1", which tells the driver that the first row of the spreadsheet contains text data as a header and not column data.

Apparently this tells the OLEDB driver for Excel that the data is "intermixed" and that it should convert any intermixed data to the type specified in the registry at:

HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes

By default, this is "Text", which is exactly what I wanted.  You'll also notice a key in there named "TypeGuessRows".  This is the number of rows that Excel will use to determine the data-type of the column, the default being 8 as described above.  You may need to tweak this value to a number greater than the row at which your data changes in the column you want as a string type.

Anyway, it's probably useless to most, but it took me a bit of time and research to figure out so I thought I'd pass it along.

Odama

Posted April 14, 2006 12:34 PM Categories: Gaming

If you own a GameCube and haven’t picked up Odama yet, what are you waiting for?  I saw this game about a year ago and have been awaiting its arrival in the US ever since.

For those that don’t know, Odama is a game created by Yoot Saito and Vivarium, father of Seaman on the Dreamcast.  Oh, and if you haven’t played Seaman, go buy that now, too.  

Odama combines a pinball with some elements of real-time strategy and tosses in a microphone as an input for voice commands for good measure.

The gameplay itself is relatively simple.  Your ancient Japanese samurai warriors need to make their way from the front of the playfield to the back, through a gate, while opposing warriors attempt to stop you.  All the while you’re batting around this enormous ball (the Odama) like a giant pinball which runs over troops (both yours and the enemies) and kills them.  The ball also destroys buildings and other armaments in its path.  Add to that a microphone where you shout commands like “Push forward!” and “Rally!” to your troops, and there you have it.  A bizarre combination of genres and control interfaces that make for a pretty unique gaming experience.  It takes some practice to coordinate it all, but once you’ve learned how to control the game, it works pretty well.

For some reason, the mainstream gaming sites aren’t all over this one.  Did I answer my question of “Why?” with that statement?  Is this game so far out of the mainstream that they just don’t care?  Or am I just a die-hard for quirky, innovative games that aren’t tired rehashes of already rehashed games?

If you have a need for a quirky and unique gaming experience and own a GameCube, spend the $50 and pick up this game.

Boot Camp

Posted April 12, 2006 12:20 PM Categories: Computing

I don’t get it.  Why are all of the Microsoft folks so excited about running a Windows OS on a MacBook Pro laptop?  The laptop has only one mouse button!  Any XP user knows that it’s extremely difficult, and in some cases impossible, to drive XP without right-clicking.  Now I’m not here to praise or bury Microsoft in the world of UI and usability, I’m merely here to present the facts.

I’ve had no less than 4 Microsoft-entrenched people ask me, “So when are you getting your MacBook Pro?”, to which I reply, “Never.”  Why would I spend $3k on a laptop I’d use almost exclusively to run Windows XP with one button?  Why would they?  Sure, it’s a slick laptop.  No one’s taking that away.  But the fact remains that a MS user is spending $3000 to buy a crippled laptop.  Why not buy one of the many other Duo Core laptops out there with similar specs that provide a much better user experience?

Sure, it’s cool.  You can dual boot two very different operating systems.  But, if you’re primarily a Windows user and you want to run OS X to screw around with or do some testing, go buy a Mac Mini and save yourself $2k.  And while you’re at it, stop asking me when I’m buying my MBP.

Virtualization

Posted April 10, 2006 11:36 PM Categories: Computing
I've spent some time over the past week playing with both Virtual PC and VMWare Workstation.  I've been wanting to try out some of the beta developer bits from MS (WinFX, Atlas, etc.) but didn't want to deal with having those pieces on my main development machines.  So, VPC and VMW to the rescue.

I've used both of these products in the past and never really found much difference between them.  Apparently I hadn't looked at the latest version of VMWare (5.5) which pretty much destroys VPC in the realm of performance.

I have created a base XP install in both VPC and VMWare, and the VMWare VM gets to the logon screen in about 10 seconds, while the VPC machine takes about 20-30.  While I didn't time it, installing Visual Studio 2005 on VMWare was noticeably faster.

Both VPC and VMWare offer similar offerings in terms of differencing disks/snapshots, but VMWare gives you a spiffy tree-like view of the paths you took to create your snapshots and allows you to easily back out to a specific snapshot at any time.

My only beef with VMWare so far is that there's a known bug that the latest released Vista build, 5342, won't install.  It installs and works great in VPC (and as a native dual boot on my machine), but no go in VMware.

So what's the deal, Microsoft?  Sure, you bought VPC from Connectix, but you created Windows.  If anyone should have a speedy virtualization product under that OS, it should be you.  Hopefully the next version of VPC will attempt to rectify the huge performance difference between the two....

Is RSS for me?

Posted April 5, 2006 2:38 PM Categories: Personal
I finally jumped on the RSS bandwagon and tried out a few RSS readers last night, finally choosing Omea Pro from JetBrains.  I went through the gamut of standard Windows RSS aggregators and couldn't find one that really fit my needs.  Omea has come closest and gives me a couple bonus features, like integrating with all facets of Oulook and file searching a la Google Desktop Search.  The interface gives me a single view of all of my feeds, all of my email, my Outlook taks, indexed files, and a variety of other things.

Yes, I know NewsGator has their Outlook plugin dealie, but in my opinion, it sucked, the biggest peeve being that it shared my folder list with Outlook.  I already have several trillion sub-folders in Outlook, and adding a bunch more for feeds was unsavory and required too much scrolling.

So, I now have all of my usual daily reads setup as feeds in Omea, notifications when new articles get posted, Outlook integrated so I can view my email right along with my feed goodness....but do I like it?  I don't know yet.  I feel overwhelmed with information now  Before I'd hit a website, scroll until I found something interesting, and read.  Now, I really have to see each and every article posted on each feed (without setting up filters or searches, I guess).

It just seems there's now far more information than I ever realized was there.  Is that good or bad?  Hmmm....

My Info

  • View Brian Peek's profile on LinkedIn

Sponsored Ad

My Book

Sponsored Ad