Import Data from Excel into Database using Django

In this tutorial, we will learn how to import data from excel into an SQL database using Django python. In this Django application, I used the pandas library.

Let’s start

Install  pandas library

First create a django project, then create models

models.py

from django.db import models
from django import forms 

class tbl_Employee(models.Model):    
  #  Id = models.IntegerField()
    Empcode = models.CharField(max_length=10, default='')
    firstName = models.CharField(max_length=150,null=True)
    middleName = models.CharField(max_length=100,null=True)    
    lastName = models.CharField(max_length=100,null=True)
    email = models.CharField(max_length=30,null=True)
    phoneNo = models.CharField(max_length=12, default='',null=True)
    address = models.CharField(max_length=500, default='',null=True) 
    exprience = models.CharField(max_length=50, default='',null=True)        
    DOB = models.DateField(null=True, blank=True)   
    gender = models.CharField(max_length=10, default='',null=True)
    qualification = models.CharField(max_length=50,default='',null=True)   
    
     

    def __str__(self):
        return self.firstName
                
    objects = models.Manager()


lets migrate model

python manage.py makemigrations

python manage.py migrate

forms.py

from django import forms

from crispy_forms.helper import FormHelper
from crispy_forms.layout import Layout, Div, Submit, Row, Column, Field

from .models import tbl_Employee

class EmployeeRegistration(forms.ModelForm):
    class Meta:
        model = tbl_Employee
        fields =[ 'Empcode','firstName','middleName','lastName','email','phoneNo' ,'address','exprience',
                  'DOB','gender','qualification'
        ] 

Then let’s write a code in views.py to create function importing data from excel into sqlite database.

Views.py

from .models import tbl_Employee
import datetime as dt
import pandas as pd
import os
from django.conf import settings
from django.core.files.storage import FileSystemStorage

def Import_csv(request):
    print('s')               
    try:
        if request.method == 'POST' and request.FILES['myfile']:
         
            myfile = request.FILES['myfile']        
            fs = FileSystemStorage()
            filename = fs.save(myfile.name, myfile)
            uploaded_file_url = fs.url(filename)
            excel_file = uploaded_file_url
            print(excel_file) 
            empexceldata = pd.read_csv("."+excel_file,encoding='utf-8')
            print(type(empexceldata))
            dbframe = empexceldata
            for dbframe in dbframe.itertuples():
                
                fromdate_time_obj = dt.datetime.strptime(dbframe.DOB, '%d-%m-%Y')
                obj = tbl_Employee.objects.create(Empcode=dbframe.Empcode,firstName=dbframe.firstName, middleName=dbframe.middleName,
                                                lastName=dbframe.lastName, email=dbframe.email, phoneNo=dbframe.phoneNo, address=dbframe.address, 
                                                exprience=dbframe.exprience, gender=dbframe.gender, DOB=fromdate_time_obj,
                                                qualification=dbframe.qualification)
                print(type(obj))
                obj.save()

            return render(request, 'importexcel.html', {
                'uploaded_file_url': uploaded_file_url
            })    
    except Exception as identifier:            
        print(identifier)
    
    return render(request, 'importexcel.html',{})

 then add the path to  the urls file

urls.py

from django.urls import path
from . import views

urlpatterns = [
    path("",views.base,name="base"),
    path("user_login/",views.user_login,name="user_login"),

 

    path('export_users_csv/', views.export_users_csv,name="export_users_csv"),  
    path('Import_csv/', views.Import_csv,name="Import_csv"),  
    
]

Create a template folder in the root directory or app directory and a create html file named Importexcel.html

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous"> 
{% block content %}
<div class="shadow-lg continer">
  <form method="post" enctype="multipart/form-data">
    {% csrf_token %}

    <div class="row">
        <div class="col-md-6 col-xs-12">
          <div class="x_panel">
            <div class="x_title">
              <h2>Excel Data Upload</h2>
           
              <div class="clearfix"></div>
            </div>
            <div class="x_content">

              <div class="col-md-8 col-sm-12 col-xs-12 form-group">
                
                </label>               
              </div>
              
                    <input type="file" name="myfile" class="form-control">
                             <button type="submit" class="btn btn-success" >Upload</button>                            
                            <br/>
                            </div>
                        </div>
                    </div>
                </div>
  </form>
  </div>
   
{% endblock %}

Then execute command 
Python manage.py runserver



4.3 14 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments