Removing Null Bytes and Control Characters From a CSV

NUL, the byte 00, has no business in a text file, and when it turns up in a CSV the readers disagree about what to do with it. Python and DuckDB keep it as a character. PostgreSQL stops reading the line at the first one. A shell tool may not see it at all. This article finds NUL bytes in a large file, removes them, times five ways of doing it on 130 MB and shows which control characters are safe to remove. The wider list of what goes wrong with bytes is in the encoding guide.

Find them first

I appended one NUL byte to the note of every 5,000th row of the 130 MB test file, which makes 400 of them. These four commands count and locate them:

printf 'bytes: '; wc -c < orders-nul.csv
printf 'NUL bytes: '; tr -cd '\000' < orders-nul.csv | wc -c
printf 'lines containing a NUL: '; LC_ALL=C grep -a -c -P '\x00' orders-nul.csv
printf 'first one: '; LC_ALL=C grep -n -a -P -m1 '\x00' orders-nul.csv | cat -v
file orders-nul.csv

bytes: 129920387
NUL bytes: 400
lines containing a NUL: 400
first one: 5001:5000,124650,27409,171.25,2025-07-20 00:32:12,refunded,FR,leave at door^@
orders-nul.csv: CSV text

tr -cd '\000' deletes everything except NULs and wc -c counts what is left: 400. grep found the same 400 lines, with one important condition. Without -a, which makes grep treat a binary file as text, GNU grep decides the file is binary and my first attempt reported 0 lines for the same pattern. That is the trap of this task: the obvious check says the file is clean. And file says CSV text, so it is no help either. cat -v prints a NUL as ^@, which is how the line near the end of the output appears.

What the readers do with a NUL

A small file with one NUL inside a field shows the split:

echo "-- psql, text column"; psql -v ON_ERROR_STOP=0 <<'SQL' 2>&1
DROP TABLE IF EXISTS nultest; CREATE TABLE nultest (id int, note text);
\copy nultest FROM 'nul-small.csv' WITH (FORMAT csv, HEADER true)
SELECT id, note, length(note) FROM nultest;
SQL
echo "-- python"; python -c "
import csv
print(list(csv.reader(open('nul-small.csv', newline=''))))"
echo "-- duckdb"; duckdb -c "SELECT id, note, length(note) AS len FROM read_csv('nul-small.csv')" 2>&1 | head -6

-- psql, text column
DROP TABLE
CREATE TABLE
ERROR:  extra data after last expected column
CONTEXT:  COPY nultest, line 2: "1,abc2,plain"
 id | note | length 
----+------+--------
(0 rows)

-- python
[['id', 'note'], ['1', 'abc\x00def'], ['2', 'plain']]
-- duckdb
┌───────┬──────────┬───────┐
│  id   │   note   │  len  │
│ int64 │ varchar  │ int64 │
├───────┼──────────┼───────┤
│     1 │ abc\0def │     7 │
│     2 │ plain    │     5 │

Python's csv module returned 'abc\x00def', a string with a NUL in it, and DuckDB's length counted seven characters for the same cell. Both accepted the file. PostgreSQL, through \copy, failed with extra data after last expected column, and the context line printed 1,abc2,plain: the NUL ended the line the client had read, the rest of that line was gone, and the next line had been glued on. No error mentions NUL. If you meet that error message on a file whose width looks right, search for NUL bytes before anything else. The same effect hides in a PostgreSQL error of that kind.

Removing them

Five ways to delete the bytes, run three times each on the 130 MB file, and the output of each hashed to prove they agree:

python time_methods.py orders-nul.csv

method                    median s  sha1 of output
tr -d '\000'                  0.34  79af7d714a91
sed 's/\x0//g'                0.52  79af7d714a91
perl -pe 's/\0//g'            0.56  79af7d714a91
python bytes.replace          0.44  79af7d714a91
python in 1 MB chunks         0.17  79af7d714a91

All five produced the same SHA-1, so any is correct. The fastest was a Python loop that reads the file in 1 MiB chunks and calls bytes.replace, at 0.17 s, followed by tr -d '\000' at 0.34 s. Reading the whole file into memory and replacing took 0.44 s and needs the file to fit in RAM, sed took 0.52 s and Perl took 0.56 s. sed and Perl also work line by line, which matters if a line is enormous. The differences are small and the machine is one laptop's container, so read this as "all of them are fast enough, and tr is the one to remember".

I use tr in pipelines because it has no startup cost and streams, and the Python chunk loop in scripts that already do other work. The one thing to do before either is to keep the original. Deleting a NUL changes the data, and if the NUL was the symptom of a different problem, you will want to look at it again.

Control characters

NUL is the first of 32 control characters, 00 to 1F, and a few of them are structure: tab (09), line feed (0A) and carriage return (0D) are what separate fields and records. A range that deletes every control character deletes the line endings and turns the file into one line. The safe range skips those three:

printf 'x\x01y\x07z\tw\x0b\n' > /tmp/ctl.csv
printf 'lines with control characters other than tab, CR, LF: '; LC_ALL=C grep -a -c -P '[\x00-\x08\x0B\x0C\x0E-\x1F]' /tmp/ctl.csv
tr -d '\000-\010\013\014\016-\037' < /tmp/ctl.csv | xxd

lines with control characters other than tab, CR, LF: 1
00000000: 7879 7a09 770a                           xyz.w.

The test line contained 01, 07, a tab and 0B, the vertical tab. The grep count found one affected line, and tr -d '\000-\010\013\014\016-\037' removed 01, 07 and 0B and kept the tab, leaving xyz, a tab and w. That range is octal: \000-\010 is 00 to 08, \013\014 are vertical tab and form feed, and \016-\037 is 0E to 1F. Also leave 7F, delete, to your judgment; it is rare.

Two things to decide first. Deleting a control character from a quoted field changes the value, so if the value matters, replace it with a visible marker like a space. And the carriage return, which the range keeps, is a line ending in a CRLF file and stray noise in the middle of a field, so a file of mixed origin needs a CSV aware pass, as the line endings article explains.

Why they were there

Deletion is a repair and not a diagnosis. A NUL on every second byte means the file is UTF-16 and was read as a single byte encoding, which the encoding detection article catches; deleting the NULs gives you readable ASCII and silently destroys any non-Latin text. A NUL at the end of a field often comes from a fixed width system that pads with zero bytes. A NUL in the middle of a text column points at a binary column, a hash or an image, exported as text, and removing it corrupts the value instead of cleaning it.

When the NUL bytes are the symptom

If the count is large, a fraction of the file or a regular pattern, do not delete anything yet. Look at the first affected line with xxd, and decide whether you have a UTF-16 file, a padded field or a damaged export. If the count is a handful and the lines are free text, deletion is fine and you can document that you did it. And if the data is binary on purpose, such as a column of hex or base64 that was written raw, the right fix is in the export, which should encode the column. The validation guide has checks to put in front of a load so a NUL never reaches the database.