How to read hyphen zip codes using oledb

I have an excel spreadsheet which contains addresses. I'm reading the d开发者_JAVA技巧ata from the spreadsheet using OLEDB and storing it into a DataTable in C#.

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + @";Extended Properties=""Excel 8.0;HDR=1;IMEX=1""";

Here's the problem: When I use the DataSet Visualizer, I have an empty string in the zip code field.

12345-1234 --> ""

So I want to correct this behavior so that the zip code appears as it should. If i have to chop off the digits after the hyphen, that would be fine. How can I ensure the zip code gets read?

Excel often has its own ideas about how a column should be formatted. For example, if you have a column containing zip codes, some with the Plus 4, others without, it is pretty much a crap shoot as to how that column will be formatted. Maybe Excel will assume that its filled with Zip+4's, maybe it will assume 5-digit Zips, or maybe just numbers. I've worked with these files for years, and I'm convinced Microsoft uses a random number generator in making this decision.

As for your original question, according to this site, CONVERT is a valid SQL scalar function, so maybe something like

SELECT CONVERT(BadField, SQL_CHAR) AS FixedField FROM [Table$]

might work?

My first inclination was to suggest using COM (instead of OleDb) to read the data from the spreadsheet. I'm pretty sure you would be able to read each cell's format and deal with it accordingly, but I always found Excel via COM to be difficult and not terribly fast.