Summary of the issue:
How does simply opening the CSV in Excel differ from opening it using the Wizard and how can I force it to use the first method programmatically while taking advantage of the strengths of the second method?

'Tis a doozy!

OK, I am racking my brain here trying to come up with a solution that will get data that is coming to us (my company - me) as a CSV into Excel for manipulation that won't corrupt it.

Before someone suggests the import wizard - already tried it. Maybe I'm not setting it up right, but I'm not having any luck.

For some reason, when I open the file directly in Excel (without going through the data import) the text maps mostly correctly. When I say "most" there are fields that format as general that should be text - for example, the value "00" gets truncated to just "0" or "03-07" gets transformed to "March-07".

When I use the wizard to force Excel to import this data as text columns, however, it gets confused (and understandably) by the complicated combination of values it's seeing - and starts mapping things wrong.

For example, there is a field called "caption" that contains HTML. You can imagine what a nightmare that is for a CSV ....

Theoretically all the individual fields are surrounded by the character combination of ", but that does not necessarily define a field because you may see that combination within a field, if that makes any sense.

For those that understand the inner workings of Excel ... uh, lil'help?