On a Mac, Excel produces csv files with the wrong line endings, whichcauses problems for git (amongst other things).
Most users won't ever need to enter a line break in an Excel spreadsheet cell - but if you do - it's not as easy as you might think! Simply hitting the enter key makes Excel jump to the next cell. So how is it done? Using Shift-Enter doesn't work, but fortunately there is another key combination that does work. I know that ALT-Enter in Excel for Windows puts a break in a line in a cell, but what is the keystroke for Mac? Thanks for your help! Aeschines May 29, 2005, 4:57am.
- Insert line breaks manually with Alt + Enter Inserting a line break in Excel is quite easy: Just press Alt + Enter to add a line break inside a cell. This keyboard shortcut works the same way on Windows and the Office 2016 for Mac. Insert line breaks with a formula.
- It is really easy to insert line breaks into Excel cells, all you need to do is hit ALT+ENTER where ever you want one. I know that I normally use them to make text easier to read in cells, longer pieces of text or bullet points for example. It is though, slightly harder to remove them if you need to.
This issue plagues at leastExcel 2008and 2011, and possibly other versions.
Basically, saving a file as comma separated values (csv) uses acarriage return r rather than a line feed n as a newline. Wayback before OS X, this was actually the correct Mac file ending, butafter the move to be more unix-y, the correct line ending should ben.
Given that nothing has used this as the proper line endings for over adecade, this is a bug. It's a real pity that Microsoft does not seefit to fix it.
Why this is a problem
This breaks a number of scripts that require specific line endings.
This also causes problems when version controlling your data. Inparticular, tools like git diff basically stop working as they workline-by-line and see only one long line(e.g. here).Not having diff work properly makes it really hard to see wherechanges have occurred in your data.
Git has really nice facilities for translating between different lineendings – in particular between Windows and Unix/(new) Mac endings.However, they do basically nothing with old-style Mac endings becauseno sane application should create them. Seehere, forexample.
A solution
Manual Line Breaks Excel Machining
There are at leat two stack overflow questions that deal with this (1and(2).
The solution is to edit .git/config (within your repository) to addlines saying:
and then create a file .gitattributes that contains the line
This translates the line endings on import and back again on export(so you never change your working file). Things like git diff usethe 'clean' version, and so magically start working again.
While the .gitattributes file can be (and should be) put underversion control, the .git/config file needs to be set up separatelyon every clone. There are good reasons for this (seehere.It would be possible to automate this to some degree with the--config argument to git clone, but that's still basically manual.
Issues
This seems to generally work, but twice in use large numbers of fileshave been marked as changed when the filter got out-of-sync. We neverworked out what caused this, but one possible culprit seems to beDropbox (but you probably should not keeprepositories on dropbox anyway).
Alternative solutions
Most users won't ever need to enter a line break in an Excel spreadsheet cell - but if you do - it's not as easy as you might think! Simply hitting the enter key makes Excel jump to the next cell. So how is it done? Using Shift-Enter doesn't work, but fortunately there is another key combination that does work. I know that ALT-Enter in Excel for Windows puts a break in a line in a cell, but what is the keystroke for Mac? Thanks for your help! Aeschines May 29, 2005, 4:57am.
- Insert line breaks manually with Alt + Enter Inserting a line break in Excel is quite easy: Just press Alt + Enter to add a line break inside a cell. This keyboard shortcut works the same way on Windows and the Office 2016 for Mac. Insert line breaks with a formula.
- It is really easy to insert line breaks into Excel cells, all you need to do is hit ALT+ENTER where ever you want one. I know that I normally use them to make text easier to read in cells, longer pieces of text or bullet points for example. It is though, slightly harder to remove them if you need to.
This issue plagues at leastExcel 2008and 2011, and possibly other versions.
Basically, saving a file as comma separated values (csv) uses acarriage return r rather than a line feed n as a newline. Wayback before OS X, this was actually the correct Mac file ending, butafter the move to be more unix-y, the correct line ending should ben.
Given that nothing has used this as the proper line endings for over adecade, this is a bug. It's a real pity that Microsoft does not seefit to fix it.
Why this is a problem
This breaks a number of scripts that require specific line endings.
This also causes problems when version controlling your data. Inparticular, tools like git diff basically stop working as they workline-by-line and see only one long line(e.g. here).Not having diff work properly makes it really hard to see wherechanges have occurred in your data.
Git has really nice facilities for translating between different lineendings – in particular between Windows and Unix/(new) Mac endings.However, they do basically nothing with old-style Mac endings becauseno sane application should create them. Seehere, forexample.
A solution
Manual Line Breaks Excel Machining
There are at leat two stack overflow questions that deal with this (1and(2).
The solution is to edit .git/config (within your repository) to addlines saying:
and then create a file .gitattributes that contains the line
This translates the line endings on import and back again on export(so you never change your working file). Things like git diff usethe 'clean' version, and so magically start working again.
While the .gitattributes file can be (and should be) put underversion control, the .git/config file needs to be set up separatelyon every clone. There are good reasons for this (seehere.It would be possible to automate this to some degree with the--config argument to git clone, but that's still basically manual.
Issues
This seems to generally work, but twice in use large numbers of fileshave been marked as changed when the filter got out-of-sync. We neverworked out what caused this, but one possible culprit seems to beDropbox (but you probably should not keeprepositories on dropbox anyway).
Alternative solutions
Manual Line Breaks Excel Macros
The nice thing about the clean/smudge solution is that it leaves filesin the working directory unmodified. An alternative approach would beto set up a pre-commit-hook that ran csv files through a similarfilter. This will modify the contents of the working directory (andmay require reloading the files in Excel) but from that point on thefile will have proper line endings.
More manually, if files are saved as 'Windows comma separated (.csv)'you will get windows-style line endings (rn) which are at leasttreated properly by git and are in common usage this century.However, this requires more remembering and makes saving csv filesfrom Excel even more tricky than normal.
I recently saw a question regarding line breaks in the table of contents – OpenOffice does not allow you to edit a table of contents except through styles. Sometimes the way that long headings are formatted might not be what you desire.
Manual Line Breaks Excel Macro
One thing to note about the TOC is that the line breaks that you insert in the heading itself will be reflected in the TOC.
Example:
Putting in a manual line break (Shift + Enter) in this heading:
will result in a line break appearing in the Table of Contents:
Related content:?
