CSV variable content to JSON variable content with Python

Question

I'm new to Python and I am trying to convert a variable which contains CSV data in JSON format. I tried this but, as you see, the result is not the expected one:

>>> data = "a1;b1;c1\na2;b2;c2\na3;b3;c3"
>>>
>>> print data
a1;b1;c1
a2;b2;c2
a3;b3;c3
>>>
>>> fieldnames = ["col1","col2","col3"]
>>>
>>> csv_reader = csv.DictReader(data,fieldnames)
>>>
>>> json.dumps([r for r in csv_reader])
[{"col2": null, "col3": null, "col1": "a"}, {"col2": null, "col3": null, "col1": "1"}, {"col2": null, "col3": null, "col1": ";"}, {"col2": null, "col3": null, "col1": "b"}, {"col2": null, "col3": null, "col1": "1"}, {"col2": null, "col3": null, "col1": ";"}, {"col2": null, "col3": null, "col1": "c"}, {"col2": null, "col3": null, "col1": "1"}, {"col2": null, "col3": null, "col1": "a"}, {"col2": null, "col3": null, "col1": "2"}, {"col2": null, "col3": null, "col1": ";"}, {"col2": null, "col3": null, "col1": "b"}, {"col2": null, "col3": null, "col1": "2"}, {"col2": null, "col3": null, "col1": ";"}, {"col2": null, "col3": null, "col1": "c"}, {"col2": null, "col3": null, "col1": "2"}, {"col2": null, "col3": null, "col1": "a"}, {"col2": null, "col3": null, "col1": "3"}, {"col2": null, "col3": null, "col1": ";"}, {"col2": null, "col3": null, "col1": "b"}, {"col2": null, "col3": null, "col1": "3"}, {"col2": null, "col3": null, "col1": ";"}, {"col2": null, "col3": null, "col1": "c"}, {"col2": null, "col3": null, "col1": "3"}]'

What can I do to make my simple program read the variable line to line?

Thomas Orozco · Accepted Answer · 2014-05-28 09:13:15Z

2

You have two issues here.

First, csv.DictReader does not operate on string. Instead, you should pass an iterator.

You have two options here:

Convert your string to a file-like object (one that implements methods such as .read, etc.).
Convert your string to a list (which is an iterator). You can do that using .splitlines(). Credit for this suggestion goes to Martijn Pieters down in the comments.

You can turn a string into a file-like object using the StringIO.StringIO class (note that in Python 3, this is io.StringIO):

import StringIO
import csv

data = "a1;b1;c1\na2;b2;c2\na3;b3;c3"
fobj = StringIO.StringIO(data)

To turn you string into a list, simply do: data.splitlines().

Second, your csv file is ; delimited so parsing it is not going to work unless you tell the csv module to expect it:

csv.register_dialect('semi', delimiter=';')

Then, you can do your thing:

json.dumps(list(csv.DictReader(fobj, fieldnames, dialect='semi')))

You'll get:

[
    {
        "col2": "b1", 
        "col3": "c1", 
        "col1": "a1"
    }, 
    {
        "col2": "b2", 
        "col3": "c2", 
        "col1": "a2"
    }, 
    {
        "col2": "b3", 
        "col3": "c3", 
        "col1": "a3"
    }
]

Note, you cannot read multiple times from a StringIO object, so if you try and pass it multiple times to your csv.DictReader, you won't get what you expect. You can "rewind" the StringIO object using: fobj.seek(0).

edited May 28, 2014 at 9:13

answered May 28, 2014 at 8:58

Thomas Orozco

55.6k12 gold badges120 silver badges120 bronze badges

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

4 Comments

Martijn Pieters Over a year ago

csv.DictReader() operates on iterables; you can pass in a list if you really want to. data.splitlines() would do just fine, for example.

Martijn Pieters Over a year ago

Next, you don't have to register a dialect either. csv.reader() and csv.DictReader() accept the delimiter argument as well. csv.DictReader(data.splitlines(), fieldnames, delimiter=';') is a lot more convenient for one-offs.

Thomas Orozco Over a year ago

@MartijnPieters That's a good observation, indeed. Updating to note that.

gr0bz Over a year ago

Thank you very much! I have many things to learn with Python, hopefully, there are good teachers there ;)

Collectives™ on Stack Overflow

CSV variable content to JSON variable content with Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related