Python Parse CSV Correctly

Question

I am very new to Python. I want to parse a csv file such that it will recognize quoted values - for example

1997,Ford,E350,"Super, luxurious truck"

should be split as

('1997', 'Ford', 'E350', 'Super, luxurious truck')

and NOT

('1997', 'Ford', 'E350', '"Super', ' luxurious truck"')

the above is what I get if I use something like str.split(,).

How do I do this? Also would it be best to store these values in an array or some other data structure? because after I get these values from the csv I want to be able to easily choose, lets say any two of the columns and store it as another array or some other data structure.

I have edited the question. If I use just the delimiter ',' it does not recognize the ',' within the quotes — cornerstone
– cornerstone, Commented Sep 6, 2012 at 9:20

Pierre GM · Accepted Answer · 2012-09-06 09:36:50Z

29

You should use the csv module:

import csv
reader = csv.reader(['1997,Ford,E350,"Super, luxurious truck"'], skipinitialspace=True)
for r in reader:
    print r

output:

['1997', 'Ford', 'E350', 'Super, luxurious truck']

edited Sep 6, 2012 at 9:36

Pierre GM

20.5k3 gold badges58 silver badges67 bronze badges

answered Sep 6, 2012 at 9:21

akhter wahab

4,0951 gold badge28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

cornerstone Over a year ago

thanks. But when I try reading from the file I get the following error- csv.Error: line contains NULL byte my file contains probably a million lines such as the following - 1,,"Warn, unknown error","car-8554.gif","car.gif","crs_04","change rand str, cut pos, 35289, add size, 9242"

Wooble Over a year ago

@cornerstone: how are you creating this file? You're not going to get the NULL byte to display here via normal means, but if it's in the file it's going to be a problem to read it via nearly any means if you treat it as text.

cornerstone Over a year ago

@Wooble it was created by dumping SQL data values to csv file. I figured the null value is due to the successive ",," present in the lines.. I have figured a solution for that.

with open(r'car.csv') as csv_file: ...     reader = csv.reader((line.replace('\0','') for line in csv_file), delimiter=',', quotechar='"') ...     print(reader.next())

Ja͢ck · Accepted Answer · 2013-06-18 02:39:41Z

22

The following method worked perfectly

d = {}
d['column1name'] = []
d['column2name'] = []
d['column3name'] = []

dictReader = csv.DictReader(open('filename.csv', 'rb'), fieldnames = ['column1name', 'column2name', 'column3name'], delimiter = ',', quotechar = '"')

for row in dictReader:
    for key in row:
        d[key].append(row[key])

The columns are stored in dictionary with the column names as the key.

edited Jun 18, 2013 at 2:39

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

answered Sep 10, 2012 at 16:45

cornerstone

6591 gold badge9 silver badges19 bronze badges

2 Comments

codingknob Over a year ago

there is a missing begin single quote in front of col3name.

Semnodime Over a year ago

What happens to the opened file handle?

tponthieux · Accepted Answer · 2013-03-20 00:14:14Z

5

You have to define the doublequote as the quotechar whithin the csv.reader() statement:

>>> with open(r'<path_to_csv_test_file>') as csv_file:
...     reader = csv.reader(csv_file, delimiter=',', quotechar='"')
...     print(reader.next())
... 
['1997', 'Ford', 'E350', 'Super, luxurious truck']
>>>

edited Mar 20, 2013 at 0:14

tponthieux

1,5525 gold badges18 silver badges30 bronze badges

answered Sep 6, 2012 at 9:51

Colin O'Coal

1,43711 silver badges10 bronze badges

Comments

MarLeo · Accepted Answer · 2019-12-10 09:59:48Z

3

If you don't want to use the CSV module you need to use a regular expression. Try this:

import re
regex = ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)"
string = '1997,Ford,E350,"Super, luxurious truck"'
array = re.split(regex, string)

print(array[3])
"Super, luxurious truck"

edited Dec 10, 2019 at 9:59

answered Nov 12, 2014 at 13:18

MarLeo

2,6962 gold badges20 silver badges16 bronze badges

Comments

Vernon Crabtree · Accepted Answer · 2018-10-08 09:19:33Z

The csv.py module is probably fine - but if you want to see and/or control how it works, here is a small python only solution based on a coroutine:

def csv_parser(delimiter=','):
    field = []
    while True:
        char = (yield(''.join(field)))
        field = []

        leading_whitespace = []    
        while char and char == ' ':
            leading_whitespace.append(char)
            char = (yield)

        if char == '"' or char == "'":
            suround = char
            char = (yield)
            while True:
                if char == suround:
                    char = (yield)
                    if not char == suround:
                        break

                field.append(char)
                char = (yield)

            while not char == delimiter:
                if char == None:
                    (yield(''.join(field)))
                char = (yield)
        else:
            field = leading_whitespace
            while not char == delimiter:
                if char == None:
                    (yield(''.join(field)))
                field.append(char)
                char = (yield)

def parse_csv(csv_text):
    processor = csv_parser()
    processor.next() # start the processor coroutine

    split_result = []
    for c in list(csv_text) + [None]:
        emit = processor.send(c)
        if emit:
            split_result.append(emit)

    return split_result

print parse_csv('1997,Ford,E350,"Super, luxurious truck"')

Tested on python 2.7

Collectives™ on Stack Overflow

Python Parse CSV Correctly

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related