CSV file has commas in data, which python interprets as extra columns

Question

I have a CSV with commas in two of the cells. The cells are marked with quotations.

e.g "1","METHOD - Standard","NGSG 01 MIDLAND FAB","GALINDO-REG, MARISOL",

When I try to import the CSV file using the line:

csv = np.genfromtxt(filename, delimiter=',',dtype=str)

Python gives an error, saying that the rows have different numbers of columns.

The data in cell has to remain the same, and I have to remove the quotations, since the array will later be imported into a different program.

I need a way to keep the comma in the data, without starting a new column. How can I achieve this?

Edit: This question was marked as a possible duplicate. The answer to the other question was:

lines = '''"AAA", "BBB", "Test, Test", "CCC"
           "111", "222, 333", "XXX", "YYY, ZZZ"'''.splitlines()
for l in  csv.reader(lines, quotechar='"', delimiter=',',
                     quoting=csv.QUOTE_ALL, skipinitialspace=True):
    print l

This looks like it would work, but -

1, How do I read from a file, instead of a variable?

2, How do I make an array out of the reader object?

Possible duplicate of Python parse CSV ignoring comma with double-quotes — pmuntima

Valentin Coudert Valentin Coudert · Accepted Answer · 2017-06-14T16:04:29

From the python doc

>>> import csv
>>> with open('eggs.csv', 'rb') as csvfile:
...     spamreader = csv.reader(csvfile, delimiter=' ', quotechar='|')
...     for row in spamreader:
...         print ', '.join(row)
Spam, Spam, Spam, Spam, Spam, Baked Beans
Spam, Lovely Spam, Wonderful Spam

So if you want to convert your file into a list of lists:

 import csv

 myFileAsArray = []
 with open('eggs.csv', 'rb') as csvfile:
   spamreader = csv.reader(csvfile, delimiter=' ', quotechar='|')
   for row in spamreader:
     myFileAsArray.append(row)

CSV file has commas in data, which python interprets as extra columns

1 Answers