125

the following code worked until today when I imported from a Windows machine and got this error:

new-line character seen in unquoted field - do you need to open the file in universal-newline mode?

import csv

class CSV:


    def __init__(self, file=None):
        self.file = file

    def read_file(self):
        data = []
        file_read = csv.reader(self.file)
        for row in file_read:
            data.append(row)
        return data

    def get_row_count(self):
        return len(self.read_file())

    def get_column_count(self):
        new_data = self.read_file()
        return len(new_data[0])

    def get_data(self, rows=1):
        data = self.read_file()

        return data[:rows]

How can I fix this issue?

def upload_configurator(request, id=None):
    """
    A view that allows the user to configurator the uploaded CSV.
    """
    upload = Upload.objects.get(id=id)
    csvobject = CSV(upload.filepath)

    upload.num_records = csvobject.get_row_count()
    upload.num_columns = csvobject.get_column_count()
    upload.save()

    form = ConfiguratorForm()

    row_count = csvobject.get_row_count()
    colum_count = csvobject.get_column_count()
    first_row = csvobject.get_data(rows=1)
    first_two_rows = csvobject.get_data(rows=5)
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
GrantU
  • 6,325
  • 16
  • 59
  • 89
  • rectummelancolique's answer below is what solved my similar issue. http://stackoverflow.com/a/17315726/3131666 – kmantel Jan 09 '15 at 00:10

9 Answers9

186

It'll be good to see the csv file itself, but this might work for you, give it a try, replace:

file_read = csv.reader(self.file)

with:

file_read = csv.reader(self.file, dialect=csv.excel_tab)

Or, open a file with universal newline mode and pass it to csv.reader, like:

reader = csv.reader(open(self.file, 'rU'), dialect=csv.excel_tab)

Or, use splitlines(), like this:

def read_file(self):
    with open(self.file, 'r') as f:
        data = [row for row in csv.reader(f.read().splitlines())]
    return data
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • This now gives the same error, but on line starting upload.num_records = csvobject.get_row_count() now – GrantU Jun 26 '13 at 09:19
  • 1
    and when I try the split lines version (which is very cools thanks) I get coercing to Unicode: need string or buffer, S3BotoStorageFile found – GrantU Jun 26 '13 at 09:21
  • 4
    What option eventually worked? Btw, you are reading the file twice: in `get_row_count()` and in `get_column_count()` - consider reading the file in `__init__` and remember `data` in `self.data`, then use it in other methods. – alecxe Jun 26 '13 at 09:47
  • 1
    +1 for splitlines() which avoids messing around with different formatting options on OSX. Hope it works across other platforms too... – pythonjsgeo Aug 02 '17 at 04:36
  • Great answer. Using - "dialect=csv.excel_tab" however, screws up the output when used with csv.DictReader. Just the 'rU' options works magically though – Murphy Oct 19 '17 at 00:06
  • 'U' mode is deprecated('U' ------> universal newline mode (deprecated)). I use Python 3.7.6 – aof Feb 23 '20 at 09:45
  • I just faced an issue with UTF-16 encoded file - "_csv.Error: line contains NULL byte" by fixing universal newline mode issue, so for future, use `True` argument to keep the `"\n"` at the end of each line, use `str.splitlines(True)`. – Ivan Sidaruk Apr 06 '22 at 14:14
56

I realize this is an old post, but I ran into the same problem and don't see the correct answer so I will give it a try

Python Error:

_csv.Error: new-line character seen in unquoted field

Caused by trying to read Macintosh (pre OS X formatted) CSV files. These are text files that use CR for end of line. If using MS Office make sure you select either plain CSV format or CSV (MS-DOS). Do not use CSV (Macintosh) as save-as type.

My preferred EOL version would be LF (Unix/Linux/Apple), but I don't think MS Office provides the option to save in this format.

g.kovatchev
  • 681
  • 5
  • 4
33

For Mac OS X, save your CSV file in "Windows Comma Separated (.csv)" format.

BoltzmannBrain
  • 5,082
  • 11
  • 46
  • 79
19

If this happens to you on mac (as it did to me):

  1. Save the file as CSV (MS-DOS Comma-Separated)
  2. Run the following script

    with open(csv_filename, 'rU') as csvfile:
        csvreader = csv.reader(csvfile)
        for row in csvreader:
            print ', '.join(row)
    
Nimo
  • 7,984
  • 5
  • 39
  • 41
5

Try to run dos2unix on your windows imported files first

rectummelancolique
  • 2,247
  • 17
  • 13
  • no really an option I need to allow user to upload csv from both Windows and Macs without any special modification. The import was saved from Excel (Windows) as a CSV so maybe there is something extra that needs to be done in Python to read these? – GrantU Jun 26 '13 at 09:02
  • @GrantU You are referring to Mac OS X 10.0 or later, not Mac OS 9 or earlier, correct? Between 9 and 10, Mac OS switched from `\x0d` (ProDOS) line endings to `\x0a` (UNIX) line endings. – Damian Yerrick Nov 28 '16 at 18:03
2

This is an error that I faced. I had saved .csv file in MAC OSX.

While saving, save it as "Windows Comma Separated Values (.csv)" which resolved the issue.

Suraj
  • 407
  • 1
  • 8
  • 12
1

This worked for me on OSX.

# allow variable to opened as files
from io import StringIO

# library to map other strange (accented) characters back into UTF-8
from unidecode import unidecode

# cleanse input file with Windows formating to plain UTF-8 string
with open(filename, 'rb') as fID:
    uncleansedBytes = fID.read()
    # decode the file using the correct encoding scheme
    # (probably this old windows one) 
    uncleansedText = uncleansedBytes.decode('Windows-1252')

    # replace carriage-returns with new-lines
    cleansedText = uncleansedText.replace('\r', '\n')

    # map any other non UTF-8 characters into UTF-8
    asciiText = unidecode(cleansedText)

# read each line of the csv file and store as an array of dicts, 
# use first line as field names for each dict. 
reader = csv.DictReader(StringIO(cleansedText))
for line_entry in reader:
    # do something with your read data 
Resonance
  • 3,359
  • 2
  • 16
  • 20
1

I know this has been answered for quite some time but not solve my problem. I am using DictReader and StringIO for my csv reading due to some other complications. I was able to solve problem more simply by replacing delimiters explicitly:

with urllib.request.urlopen(q) as response:
    raw_data = response.read()
    encoding = response.info().get_content_charset('utf8') 
    data = raw_data.decode(encoding)
    if '\r\n' not in data:
        # proably a windows delimited thing...try to update it
        data = data.replace('\r', '\r\n')

Might not be reasonable for enormous CSV files, but worked well for my use case.

Dougyfresh
  • 586
  • 3
  • 15
  • That solved my problem,Thanks!Look [here](https://stackoverflow.com/questions/15433188/r-n-r-and-n-what-is-the-difference-between-them) – aof Feb 23 '20 at 13:11
0

Alternative and fast solution : I faced the same error. I reopened the "wierd" csv file in GNUMERIC on my lubuntu machine and exported the file as csv file. This corrected the issue.

p699
  • 145
  • 1
  • 7