Home > Apple, unix > Excel 2008 for Mac’s CSV export bug

Excel 2008 for Mac’s CSV export bug

I ran into this at work a few weeks ago and thought I’d share.

Excel 2008′s CSV export feature is broken.  For instance, enter the following fake data into Excel:

Row Name Age
0 Nick 23
1 Bill 48
Save as -> CSV file

Full list of choices

When you use standard unix commands to view the output, the results are all garbled.

[Documents]$ cat Workbook1.csv
$ wc -l Workbook1.csv
0 Workbook1.csv
What is the issue?  The file command reveals the problem:
$ file Workbook1.csv
Workbook1.csv: ASCII text, with CR line terminators
CR stands for Carriage return, the ‘\r’ control sequence which, along with the newline character (‘\n’), is used to break up lines on Windows.  Unix OSes like Mac OS expect a single ‘\n’ new line character to terminate lines.
How can we fix this?


# convert the Workbook1.csv file into a Unix appropriate file
dos2unix Workbook1.csv WithUnixLineEndings.csv
If you don’t have dos2unix on your Mac, and you don’t want to install it, you can fake it with the tr command:
tr '\15' '\n' < Workbook1.csv # remove the carriage returns, replace with a newline
Very annoying that the Mac Excel doesn’t respect Unix line terminators.  Interestingly, I found a post that talks about ensuring that you choose a CSV file encoded for Mac, but that option seems missing from the Mac version itself.
If I’m missing something obvious, please correct me.
About these ads
  1. Jonas Mortin
    January 14, 2011 at 3:04 am | #1

    Oh my oh my. This really saved my day. You are a hero.

    • i82much
      January 14, 2011 at 8:20 am | #2

      Awesome, it was a shot in the dark that someone else would need this info. Glad you found it.

  2. Mike
    December 11, 2011 at 9:39 am | #3

    You could also just choose to save in “Format: Windows Separated Values (.csv)” which produces CRLF. Default Format: Comma Separated Values (.csv) produces CR.

  3. test
    May 21, 2013 at 2:03 pm | #4

    that (resave as windows csv file) doesn’t seem to work with mac excel 2008, but it does with mac excel 2011. ARGH. probably better just to use open office.

  4. September 30, 2013 at 11:15 am | #5

    Thanks for writing the article. There’s a potential additional place for problems: character set. My Excel-produced CSV was in “Western (Mac OS Roman)”. As a result, the tr command suggested here failed with “tr: Illegal byte sequence”. I opened the csv in TextWrangler and saved using UTF-8. After that your tr command worked perfectly. Perhaps there’s a way to tell tr to handle Mac OS Roman… but I don’t know.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: