Unknown column in mySQL CSV import

Question

Hey everyone.

I am trying to import a small CSV file into my django program. I am using SQL, and here is what I have so far. The CSV file itself has Column1 which is named Customer and I am trying to take that information and assign it to the model field name 'client_name'. Problem I get when I run this query is that I keep getting the unknown column 'customer' in field list error, but not sure how to fix it.

LOAD DATA LOCAL INFILE 'home/steve/Desktop/ClientListing_2.csv' INTO TABLE clients_clients
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
SET Customer = client_name;

Any suggestions?

Thanks everyone.

Answer 1

I'm guessing a little bit here, but it sounds like you have a CSV with a header row, and you want to load the first column of that CSV into the client_name column of a table names clients_clients.

Is that right?

If so, this should work:

LOAD DATA LOCAL INFILE 'home/steve/Desktop/ClientListing_2.csv' 
INTO TABLE clients_clients 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(client_name);

Answer 2

I keep an empty (i.e. terminated with a "pass") management around it a zip file that I can unravel whenever I need a one-off. If you're struggling with MySQL, do it The Django Way:

from django.core.management import BaseCommand
import csv
from clients.models import Client
class Command(BaseCommand):
    def handle(self,*args,**options):
        for row in csv.reader(open('path/to/ClientListing_2.csv', 'rb'), delimiter=',', quotechar='"'):
            if row[0] == 'Customer': 
                continue
            Client.objects.get_or_create(client_name = row[0])

Save it, run it, delete it. This method guarantees that Django IDs are generated correctly. There are smarter ways to do this (guaranteeing uniqueness would be a good idea!), but this is the core idea.

Answer 3

It seems like you might be trying to load columns in a different order and perhaps just a subset of the columns. If so, the correct syntax is:

LOAD DATA LOCAL INFILE 'home/steve/Desktop/ClientListing_2.csv'
INTO TABLE clients_clients
(client_name, ...)
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

In other words, use a column list to fill just some of the columns or to change the order from your csv file.