Stuart Laughlin's Profile

feature-rich

Even more entertaining, there is absolutely no way to make this 100% reliable - although with some pain, you can improve the situation. Here’s what you need to do. First add the "IMEX=1" option to your connection string like this:

OleDbConnection dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""");

That tells ADO.NET to honor the following registry key when reading the spreadsheet:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

This registry key tells ADO.NET what to do when it encounters mixed types in the first 8 rows. It can either be set to the string "Majority Type" (for the default behavior) or to "Text" (which forces the column to be of type string). Note that you are still screwed if the first 8 postal codes are numeric and the 9th is Canadian. (Not to mention that the "Text" option invokes handling that fails on strings over 255 characters, but let’s skip that for now.)

There’s also a second relevant registry setting (which is honored regardless of the IMEX option):

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

That says how many rows to scan to guess the datatype. The default is 8, but you can set it anywhere from 0-16 decimal (0 meaning "scan the first 16384 rows", and all other values meaning what they say). Putting this all together, the most reliable way to read a US/Canadian postal code is to use the following registry settings:

TypeGuessRows = 0
ImportMixedTypes = Text

That’s pretty close to perfect, although it will still fail if the first 16384 postal codes are numeric and any of the subsequent ones aren’t.

This site combines several sites first created in 1996 to provide a starting point for students and enthusiasts of English Literature. Nothing replaces a quality library, but hopefully this site will help fill the needs of those who have not access to one.

LXR (formerly "the Linux Cross Referencer") is a software toolset for indexing and presenting source code repositories.

I figured out to 1) get Log4Net configured for a console app and 2) get it to write all entries to a log file and console and write just errors to the Event log on the server.

When may one legitimately copy a page from a book or a photograph of a person or a recording of a song? What ethical principles come to bear on these questions? These questions have grown in importance, and will continue to grow in importance, because the amount of available information is growing, and the ease of copying is growing.

The answers may be surprising. I would ask you to bear with me as I try to think carefully about principles of right and wrong.

links from my MonoRail / AspView talk