Treat an Excel Workbook like a database with Visual Studio

I've been doing a lot of work with MS Excel 2003 lately. Specifically, I'm using it as a data source for an application. I recently learned Visual Studio 2005 can connect to an Excel workbook and treat it like a database. I thought I would share my findings. Here's how it's done:

1. Open Visual Studio 2005, and bring up the "Server Explorer" (View > Server Explorer)
2. Next, click the icon to "Connect to Database"
   
3. Choose "Microsoft Access Database File" as your data source
   1. We choose this because we want the .NET OLE DB Data Provider
   
4. Click "OK" to get to the "Add Connection" dialog
5. Browse to your Excel file
   1. You'll need to change your "Files of type" from "*.mdb" to "*.*"
6. Next click the "Advanced..." button in the "Add Connection" dialog
7. Locate "Advanced > Extended Properties"
8. Paste in "Excel 8.0;HDR=Yes;IMEX=1"
   
   According to www.ConnectionStrings.com:

   "HDR=Yes;" indicates that the first row contains column names, not data. "HDR=No;" indicates the opposite.

   "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

9. Click "OK" for the "Advanced Properties" dialog
10. Click “Test Connection” to verify if you have everything set up correctly.
11. Click "OK" for the "Add Connection" dialog
12. You're all set, your Excel file should now appear in your database list.

You can now run queries on your Workbook.

I am the "Cool Nerd King"

Links Added

News I've added new sections to my blog Blog Roll (Developers) Blog Roll (Resources) I hope they prove useful to some .NET developers out there. (I know they've helped me out.) Random Thoughts

• Don't you find it odd C# enums don't implement IEnumerable?
• Some day I'll use my uber web skillz and get around to actually skinning this blog.
• But for now, my time is spent on other things
• Triune Group (Day job)
• Kohnen Solutions (Night job)
• Dayton .NET Developers Group (Volunteer)
• Other user groups (Volunteer)

Cheers, Justin

My Second, "Getting the Most from Version Control" Presentation

Introduction This time I gave a presentation at The Dayton .Net Developers Group. For those that attended, my materials are now available for download. Compared to the first presentation, this one focused on Subversion less, and more on general practices. That was something I feel needed to be improved from the first meeting. For those that attended the first presentation you might want to look at my new materials, I completely overhauled my slide deck. My demos were much less this time, and my last one bombed. But I promised to list the corrected steps here, so stay tuned. What did I learn? A big difference between this demo and last is I didn't use svnserve. TortoiseSVN (TSVN) can use the "file:" protocol to connect to local or UNC repositories. (I found it in "4.1.3. Local Access to the Repository" of the TSVN documentation.) That's great news for those people out there what want to get started quickly, TSVN can do everything you need. There was a request for a demo that involved VS and .NET projects, which I'll be sure to add if I do the presentation again. Final Thought All in all, the feedback was very positive. Thanks to all of you for your kind words, and if you have any comments please list them here, or on the forum at the group's website.

"In Plain English"

I was surfing the other day, and came across these tutorials. So simple, even a caveman would understand. (My apologies to all cave dwelling people.) Social Networking in Plain English RSS in Plain English Wikis in Plain English

What the web is becoming, and should be...

This video holds a special place in my heart. I <3 the Web!!!

Microsoft Certified Technical Specialist

Since June 08 2007, I'm a : Microsoft Certified Technical Specialist: .NET Framework 2.0, Web Applications I'm one step closer to becoming a MCPD: Web Developer Now, the next trick is figuring out how to fit my new title on a business card.