CSV Line Endings and the CR LF Trap in Shells and Loaders

The bug report reads: "The import works, but filtering on the last column returns nothing." The cause is two bytes that nobody can see. A CSV written by a Windows tool ends each record with a carriage return and a line feed, and a program that only knows about the line feed leaves the carriage return stuck to the last field. This article shows it happening in the shell, in MySQL and in PostgreSQL, explains the history behind three line endings, and ends with the safe way to convert a file. The wider context is in the CSV format guide.

Three conventions, one file extension

Unix and macOS end a line with LF, the byte 0A. Windows ends it with CRLF, 0D 0A. Classic Mac OS before version X used a bare CR, 0D. RFC 4180 says CSV records end in CRLF, which is why many libraries still write it, and the article on the RFC notes that most files in the wild do not follow it.

I made the same two records in all three styles. file tells them apart, and wc -l shows the first trap, because a file with only carriage returns has zero line feeds and counts as zero lines:

file lf.csv crlf.csv cr.csv
wc -l lf.csv crlf.csv cr.csv
xxd crlf.csv | head -2

lf.csv:   CSV text
crlf.csv: CSV text
cr.csv:   ASCII text, with CR line terminators
 3 lf.csv
 3 crlf.csv
 0 cr.csv
 6 total
00000000: 6964 2c6e 616d 650d 0a31 2c41 6e61 0d0a  id,name..1,Ana..
00000010: 322c 426f 0d0a                           2,Bo..

The hex dump of the CRLF file shows the cause of most trouble: after name comes 0D 0A, so the last field of the header is name followed by an invisible carriage return, if the reader only splits on 0A.

What the carriage return does to a shell script

Here is a loop that reads the CRLF file and prints the second field in brackets, piped through cat -A so that control characters show up. It prints ^M, which is how cat -A draws a carriage return:

while IFS=, read -r id name; do printf '[%s]\n' "$name"; done < crlf.csv | cat -A | head -3
echo ---
while IFS=, read -r id name; do [ "$name" = "Ana" ] && echo "matched Ana"; done < crlf.csv; echo "(nothing printed means no match)"

[name^M]$
[Ana^M]$
[Bo^M]$
---
(nothing printed means no match)

The brackets enclose Ana^M. The comparison [ "$name" = "Ana" ] therefore fails for every row, and the second half of the output shows that nothing matched. The script contains no bug that a reader could find by reading it, which is why this one eats an afternoon. awk does the same: it measures the last field as three characters in the LF file and four in the CRLF file.

lf.csv: length of the last field = 3
crlf.csv: length of the last field = 4

The same bytes cause the "I can't find that customer" tickets that follow a hand-edited file. Anything that compares the last column, joins on it or uses it as a key sees a different string from what is on screen.

What the loaders do

A real CSV parser knows about all three endings. Python's csv module read the LF, CRLF and CR files into identical records. The trouble with Python is subtler, and it is about the file object rather than the parser. The csv documentation tells you to open files with newline="", and this is why. With the default newline handling, Python translates \r\n to \n before the parser sees it, including inside a quoted field. A record with a Windows line break inside its note came back from the same file as two different strings:

lf.csv [['id', 'name'], ['1', 'Ana'], ['2', 'Bo']]
crlf.csv [['id', 'name'], ['1', 'Ana'], ['2', 'Bo']]
cr.csv [['id', 'name'], ['1', 'Ana'], ['2', 'Bo']]
---
open(..., {'newline': ''})               ['1', 'line one\r\nline two']
open(..., default newline handling)      ['1', 'line one\nline two']

With newline="" the embedded \r\n survived exactly as written, and without it the cell silently became \n. For most files you would call that an improvement. For a file whose bytes you must reproduce, such as a signed export or one whose hash is checked, it is data loss. I pass newline="" every time so that the decision is mine.

PostgreSQL's COPY in CSV mode handles CRLF without any flags. The loaded name values have lengths of 3 and 2, not 4 and 3:

DROP TABLE
CREATE TABLE
COPY 2
 id | name | len 
----+------+-----
  1 | Ana  |   3
  2 | Bo   |   2
(2 rows)

It is strict about mixing. A file whose header ends in CRLF and whose first record ends in a bare LF is rejected, with a hint that tells you what to do:

DROP TABLE
CREATE TABLE
ERROR:  unquoted newline found in data
HINT:  Use quoted CSV field to represent newline.
CONTEXT:  COPY pair, line 2
 a | b 
---+---
(0 rows)

MySQL's LOAD DATA is where the trap bites hardest. Its default LINES TERMINATED BY is '\n', so on a CRLF file it leaves the carriage return in the last column. The first result below shows the hex 416E610D for Ana, a length of 4, and a comparison with the string Ana that is false. After adding LINES TERMINATED BY '\r\n' the length is 3 and the comparison is true:

id	name	hex	len	matches
1	Ana
	416E610D	4	0
2	Bo
	426F0D	3	0
id	name	len	matches
1	Ana	3	1
2	Bo	2	0

The load reported no warning in either case, since a four character string fits in a VARCHAR(20). I look at the hex once, early, instead of trusting that a load worked.

Converting safely

The obvious fix is sed 's/\r$//' file.csv, or dos2unix. Both strip a carriage return at the end of every line. That is correct for line endings, and it is wrong for any quoted field that contains a line break written as CRLF, because the carriage return before that break is also at the end of a line. I made a file with a Windows line break inside one note and ran sed over it, then parsed the before and after with Python:

before: [['id', 'note'], ['1', 'line one\r\nline two']]
after sed: [['id', 'note'], ['1', 'line one\nline two']]

The note went from line one\r\nline two to line one\nline two. Whether you consider that damage depends on the use, and it is a change to the data without any warning. The reliable way is to let a CSV parser read the file and write it again with the ending you want. In Python that is a reader opened with newline="" and a writer with lineterminator="\n" (or "\r\n"), and the quoted text is carried across as data. Cleaning a messy file in a repeatable way puts that into a script.

Git can change your data

A version control system is the other place where line endings are rewritten without asking. If you commit CSV files and your configuration has core.autocrlf set to true or input, Git converts CRLF to LF on the way into the repository. For source code that is reasonable. For a data file it reaches inside quoted fields.

With core.autocrlf=input, I added the file with the embedded CRLF note. git ls-files --eol reports i/lf w/crlf, meaning the index copy has LF only while the working copy kept CRLF, and the stored bytes show 0A where the note had 0D 0A. Adding *.csv -text to .gitattributes, as the gitattributes reference describes, marks the files as not text, and the same commit stores i/crlf, byte for byte:

rm -rf /tmp/gitdemo && mkdir /tmp/gitdemo && cd /tmp/gitdemo && git init -q . && git config user.email [email protected] && git config user.name lab
git config core.autocrlf input
cp crlf-embedded.csv . && git add crlf-embedded.csv
echo "--- stored with core.autocrlf=input"; git ls-files --eol crlf-embedded.csv
git show :crlf-embedded.csv | xxd | sed -n 1,4p
echo "--- same file with -text in .gitattributes"
git rm -q --cached crlf-embedded.csv; echo '*.csv -text' > .gitattributes; git add .gitattributes crlf-embedded.csv
git ls-files --eol crlf-embedded.csv
git show :crlf-embedded.csv | xxd | sed -n 1,4p

warning: in the working copy of 'crlf-embedded.csv', CRLF will be replaced by LF the next time Git touches it
--- stored with core.autocrlf=input
i/lf    w/crlf  attr/                 	crlf-embedded.csv
00000000: 6964 2c6e 6f74 650a 312c 226c 696e 6520  id,note.1,"line 
00000010: 6f6e 650a 6c69 6e65 2074 776f 220a       one.line two".
--- same file with -text in .gitattributes
i/crlf  w/crlf  attr/-text            	crlf-embedded.csv
00000000: 6964 2c6e 6f74 650d 0a31 2c22 6c69 6e65  id,note..1,"line
00000010: 206f 6e65 0d0a 6c69 6e65 2074 776f 220d   one..line two".
00000020: 0a                                       .

Test fixtures are where this bites. A parser test that passes on one developer's Windows machine and fails on another's Linux CI is often a CSV fixture that Git normalized differently on each side. The edge case cheat sheet suggests keeping those fixtures as -text for that reason.

Which ending to write

For a file you create, pick one and use it throughout, because mixing is the one choice that some readers reject outright. I write LF for files that stay on Unix pipelines and CRLF for files whose consumer is Excel or a Windows tool that expects the RFC form. Nearly every modern reader accepts both. A file with a header ended in CRLF and records ended in LF is the one that comes from concatenating two sources with cat, so check the seam after a merge; merging files with the same columns covers doing that properly.

When line endings are not your problem

If every consumer of the file is a real CSV library and you never check the file by eye, the line ending rarely matters, and spending time on it is wasted. It starts to matter when something outside the parser touches the bytes: a shell script, a database loader with a LINES TERMINATED BY default, a diff tool, a checksum, or Git. It also matters when a field contains a line break, since that is where the quote rules and the line ending rules meet, as the quoting article explains.

And if the symptom is a count that is off by one, look at the final line instead. A file that lacks a trailing newline makes wc -l one lower than the record count, and that is a separate matter from CRLF versus LF.