Import csv files containing data with commas into MySQL

Question

I have more than 10GB .csv files. I m trying to import the data from these files into MySQL using python. Since csv files are comma separated files, and I have data which itself contains commas, therefore I can not use ',' here for splitting. There please suggest some other option for splitting the csv file. I want to import the data from the csv file(where the data itself contains comma) to MySQL (i am using Wamp server for the same). I have data like 'london, u.k.', '2010', 'credit, saving'

Answer 1

First of all, I am assuming that the CSV files have quotes around the string values.

Thus each value containing a comma is surrounded by a quote like this:

a, b, 2, 'foo, bar'

If so, then you can use the following:

import

 csv

quoteChar = "'"

myReader = csv.reader(open('largefile.csv', 'rb'), delimiter=',', quoteChar = quoteChar)

for row in myReader:
    print row # this prints a list. Row is a list of values per line in your
    # 'largefile.csv' file. Each row is splitted on the delimiter char.
    # (comma in this case) and strings that are quoted by quoteChar (in your case
    # a ' are treated differently.) This will work for your input that you've given
    # in your question.

# this is what you used to do:
for line in open('largefile.csv', 'rb'):
     row = line.split(quoteChar)

If otherwise it'll depend on how your CSV files look like.