Python CSV error: line contains NULL byte

Question

I'm working with some CSV files, with the following code:

reader = csv.reader(open(filepath, "rU"))
try:
    for row in reader:
        print 'Row read successfully!', row
except csv.Error, e:
    sys.exit('file %s, line %d: %s' % (filename, reader.line_num, e))

And one file is throwing this error:

file my.csv, line 1: line contains NULL byte

What can I do? Google seems to suggest that it may be an Excel file that's been saved as a .csv improperly. Is there any way I can get round this problem in Python?

== UPDATE ==

Following @JohnMachin's comment below, I tried adding these lines to my script:

print repr(open(filepath, 'rb').read(200)) # dump 1st 200 bytes of file
data = open(filepath, 'rb').read()
print data.find('\x00')
print data.count('\x00')

And this is the output I got:

'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1\x00\x00\x00\x00\x00\x00\x00\x00\ .... <snip>
8
13834

So the file does indeed contain NUL bytes.

Answer 1

Reading it as UTF-16 was also my problem.

Here's my code that ended up working:

f=codecs.open(location,"rb","utf-16")
csvread=csv.reader(f,delimiter='\t')
csvread.next()
for row in csvread:
    print row

Where location is the directory of your csv file.

Answer 2

Why are you doing this?

 reader = csv.reader(open(filepath, "rU"))

The docs are pretty clear that you must do this:

with open(filepath, "rb") as src:
    reader= csv.reader( src )

The mode must be "rb" to read.

http://docs.python.org/library/csv.html#csv.reader

If csvfile is a file object, it must be opened with the ‘b’ flag on platforms where that makes a difference.

Answer 3

This happened to me when I created a CSV file with OpenOffice Calc. It didn't happen when I created the CSV file in my text editor, even if I later edited it with Calc.

I solved my problem by copy-pasting in my text editor the data from my Calc-created file to a new editor-created file.

Answer 4

I encountered this when using scrapy and fetching a zipped csvfile without having a correct middleware to unzip the response body before handing it to the csvreader. Hence the file was not really a csv file and threw the line contains NULL byte error accordingly.

Answer 5

data_initial = open("staff.csv", "rb")
data = csv.reader((line.replace('\0','') for line in data_initial), delimiter=",")

This works for me.

Answer 6

You could just inline a generator to filter out the null values if you want to pretend they don't exist. Of course this is assuming the null bytes are not really part of the encoding and really are some kind of erroneous artifact or bug.

with open(filepath, "rb") as f:
    reader = csv.reader( (line.replace('\0','') for line in f) )

    try:
        for row in reader:
            print 'Row read successfully!', row
    except csv.Error, e:
        sys.exit('file %s, line %d: %s' % (filename, reader.line_num, e))