Excel (and all of Microsoft really) can Bite Me!

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.

  1. Yes, Excel exports tab-delimited text files.

  2. BUT, it will put up two or three annoying dialog boxes about it designed to scare the end-user.

  3. Oh, and also it will put quotes around any cell that contains a comma.

  4. What about a cell that already had quotes you ask? Oh, well then it will put three quotes around it.

  5. And quotes in the interior of the cell will get doubled.

  6. Windows Excel will export the rows with a carriage-return/newline pair ("\r\n"), just like you'd expect.

  7. 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.

  8. 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:, ,

Blogged with Flock