1

I have an excel spreadsheet which contains addresses. I'm reading the data 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?

MAW74656
  • 3,449
  • 21
  • 71
  • 118

1 Answers1

1

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.

Marc Bernier
  • 2,928
  • 27
  • 45
  • I'll certainly try your converting suggestion, but COM? I'm not a big fan of interop, but I might have to consider it eventurally. This site has a listing of ODBC functions, but I'm using OLEDB. Does that make a difference? – MAW74656 Feb 24 '11 at 19:52
  • I got it working, but I"m not sure how. Some combination of IMEX=1 and changing the original cell format did the trick, but I learned a lot from your post, hence the answer mark. – MAW74656 Feb 24 '11 at 21:27
  • I forgot to mention, the cell formatting that the user specified in Excel almost always has no effect on how you'll see the data in OleDb. What format did you use to get it working? – Marc Bernier Feb 24 '11 at 21:53