1

I'm exporting and importing data to and from CSV with Python. Some of the individual segments / values are originally lists when written to CSV. The python CSV library outputs these values as CSV surrounded by square brackets.

When I read the data back, also using the CSV library, it doesn't recognize the existence of the list and instead reads it as a single string.

Is there any way I can read the list back as a list rather than a string? I'd prefer to not manipulate the string manually using split(',').

import csv

dummy_data = [['list value 1', 2, 'list value 3', '',], 'string 1', 'string 2', 3, ]
dummy_csv = 'c:\\temp\\out.csv'
with open(dummy_csv, 'w') as file:
    cw = csv.writer(file)
    cw.writerow(dummy_data)

This will write out to c:\temp\out.csv:

"['list value 1', 2, 'list value 3', '']",string 1,string 2,3

Reading it back from the file:

with open(dummy_csv) as file:
    cr = csv.reader(file)
    for row in cr:
        print(row[0])

...will print the list value as a single literal string:

"['item 1', 2, 'item 3']"

How can I cleanly and natively convert this string back to a list?

The best I've come up with is re-parsing the string using the csv library after detecting and stripping the square brackets. This is marginally better than using split(',') because it'll handle escaped characters better, but it feels hacky:

with open(temp_csv) as file:
    cr = csv.reader(file)
    for row in cr:
        for segment in row:
            if segment and segment[:1] == '[' and segment[-1:] == ']':
                for list_segment in csv.reader([segment.strip('[] ')]):
                    print([e.strip(" '""") for e in list_segment])

This will return the desired list with values:

['item 1', '2', 'item 3']

Yes, unfortunately the data must be saved to file as CSV format; if it were up to me I'd use JSON and this wouldn't be an issue.

Thanks for any assistance!

Update: I don't believe this is a duplicate question as I assumed (and hoped) there was some option or parameter within the csv library I had overlooked.

Bosco
  • 807
  • 9
  • 18
  • 2
    Use `ast.literal_eval` module. – Manish Gupta May 10 '17 at 08:47
  • @ManishGupta - you're a legend! Please post that as an answer and I'll accept. I knew it would be something simple. – Bosco May 10 '17 at 08:55
  • Possible duplicate of [Convert string representation of list to list in Python](http://stackoverflow.com/questions/1894269/convert-string-representation-of-list-to-list-in-python) – juanpa.arrivillaga May 10 '17 at 08:55
  • Although you can use `eval` or `ast.literal_eval` in these cases where you've written data as the string representation of a list, you should probably think about how you want to save your data. If you want to save data structures, maybe something like JSON is more appropriate. CSV's are meant for tabular, numerical/text data. You probably don't want a csv of list literals. – juanpa.arrivillaga May 10 '17 at 08:57
  • @juanpa.arrivillaga I agree however the data needs to be parsed by colleagues who use VBA. As far as I'm aware there's no native JSON support within VBA. – Bosco May 10 '17 at 09:08
  • @Bosco ah, that makes sense. – juanpa.arrivillaga May 10 '17 at 09:15

1 Answers1

1

You can use

import ast
your_string = "['item 1', 2, 'item 3']"
ast.literal_eval(your_string)

If all strings are quoted, than you can also use

import json
your_string = u'["item 1", "2", "item 3"]'
json.loads(your_string)

Or you can use numpy

import numpy
your_string = "['item 1', 2, 'item 3']"
np.array(your_string)
Roelant
  • 3,185
  • 1
  • 18
  • 49