[Django]-How do I transfer data in .csv file into my sqlite database in django?

by

8👍

✅

I created a complete script using this data as a test:

"http://www.graychase.com/aabbas","Gray & Chase LLP","Amr A","Abbas","The George Washington University Law School","2005"
"http://www.graychase.com/kadam","Gray & Chase LLP","Karin","Adam","Ernst Moritz Arndt University Greifswald","2004"

Please mind that your CSV file as you exemplify above IS WRONG. The csv file reader will read the entire line as an entry because the entire line is in quotes. Either remove the beginning and trailing quotes from every line in the csv file or – like I did – enclose each distinct value in the line in quotes.

Here are your models that will work with the script below:

from django.db import models

class School(models.Model):    
    name = models.CharField(max_length=300, unique=True)

    def __unicode__(self):
        return self.name

class Lawyer(models.Model):
    firm_url = models.URLField('Bio', max_length=200, unique=True)
    firm_name = models.CharField('Firm', max_length=100)
    first = models.CharField('First Name', max_length=50)
    last = models.CharField('Last Name', max_length=50)
    year_graduated = models.IntegerField('Year graduated')
    school = models.ForeignKey(School)

    def __unicode__(self):
        return self.first

Here is the script that will read your CSV file (unless I got the name of you project sw2 and application wkw2 wrong, then fix those references):

############ All you need to modify is below ############
# Full path and name to your csv file
csv_filepathname="C:/Users/A/Documents/Projects/Django/sw2/wkw2/fixtures/data.csv"
# Full path to the directory immediately above your django project directory
your_djangoproject_home="C:.../Documents/PROJECTS/Django/"
############ All you need to modify is above ############

import sys,os
sys.path.append(your_djangoproject_home)
os.environ['DJANGO_SETTINGS_MODULE'] ='sw2.settings'

from sw2.wkw2.models import School, Lawyer

import csv
dataReader = csv.reader(open(csv_filepathname), delimiter=',', quotechar='"')

old_school = None
for row in dataReader:
    if old_school != row[4]:
        old_school = row[4]
        school = School()
        school.name = old_school
        school.save()

dataReader = csv.reader(open(csv_filepathname), delimiter=',', quotechar='"')

for row in dataReader:
    lawyer=Lawyer()
    lawyer.firm_url=row[0]
    lawyer.firm_name=row[1]
    lawyer.first=row[2]
    lawyer.last=row[3]

    lawyer_school=School.objects.get(name=row[4])
    lawyer.school=lawyer_school

    lawyer.year_graduated=row[5]
    lawyer.save()

The script is first creating every single possible school from the available schools in the CSV file. Then it runs through the CSV again and create every single lawyer.

I ran this script with the test data. It works just fine and loads all the CSV data.

👤cethegeek

Leave a comment