[RESOLVED] Excel and null data - my ongoing saga

[MMock]

This is my previous thread where this topic was discussed. I am really understanding this a lot better, but I have a question.

The spreadsheet I am now reading has a SSN column. We get this Excel file from our client so they are in control of what they give us, and it is a bit screwy. Sometimes the record is for a trust, sometimes it is for person. When it is a trust, the SSN does not have colons separating it, that is, 123456789. When it is a person, it does: 123:45:6789. This column is type General in Excel. So I am figuring that when I use OLE DB to select the records from this spreadsheet, it returns null data to me for the trust SSN's, as they look like pure numerics. I understand that is how it works (from jmcilhinney's explanation in my previous thread).

So shouldn't I be able to modify the Excel spreadsheet before my program reads it and set the format of the column? I changed it to Text, but still had the problem. Then I typed over the SSN. I typed 123456789 into the cell already containing this value, and it put that little green triangle up in the corner to signify it was Text. But this doesn't happen unless I retype. Additionally, if I click on the cell I get a yellow caution symbol and when I hover over it, it says "The number in this cell is formatted as text or preceeded by an apostrophe." Fine, that is what I want, but why didn't it do it to the existing data as well?

I know this is a boring problem, but I only have until Friday left to my contract here, and I'd hate to leave some unknown issue in my code for someone else to figure out. I don't want to give up on Excel and OLE DB (like I did in the previous thread, but that's because often the data were null so I thought I had no choice) but I will if it's a lost cause.

Thanks!