Ye Gods I hate most near everything Microsoft.
If you ever need to import data from an Excel file, here are things you should know.
- Yes, Excel exports tab-delimited text files.
- BUT, it will put up two or three annoying dialog boxes about it designed to scare the end-user.
- Oh, and also it will put quotes around any cell that contains a comma.
- What about a cell that already had quotes you ask? Oh, well then it will put three quotes around it.
- And quotes in the interior of the cell will get doubled.
- Windows Excel will export the rows with a carriage-return/newline pair ("\r\n"), just like you'd expect.
- Mac Excel will export the rows with a carriage-return ("\r"), unlike pretty much anything else on the planet (excepting devices that actually connected to real physical teletypes). Not a newline ("\n"), like everything else on Mac/Unix/Linux/BSD, but a carriage-return.
- On Mac Excel you have to export as "Text (Windows)" to get a carriage-return/newline pair, which is at least reasonable to expect your importer to handle.
An example might be in order. If your cell contains the text:
"The quick brown fox nipped in for a drink at the "Lazy Dog". But it was closed."
Excel will make that into:
"""The quick brown fox nipped for a drink at the ""LazyDog"".But it was closed."""
Basically you have to strip an opening/closing quote pair, and then replace any occurrence of "" with " to get your original data.
technorati tags:programming, Excel, text
Blogged with Flock