Django Python

Import Data from Excel into Database using Django

Spreadsheets are commonly used by businesses and organizations to collect and retain data in today’s digital age. However, as the amount of data increases, these spreadsheets can become challenging to manage. That is where databases come in. Importing data from an Excel spreadsheet into a database is simple with Django, a high-level Python web framework. In this tutorial, we’ll learn how to import data from Excel into a Django database.

Table of Contents

Create Django project

We need to create a Django project Import_Excel

     Django-admin startproject  Import_Excel

Create Django App

Now then we will create a app import_excel_db to perform import data from excel into database.

python  manage.py startapp import_excel_db

Install pandas and Django-import-export Library

Pandas 
 pip install pandas
Django-import-export
 pip install Django-import-export

Configure Application Settings

Configure application settings by registering the App_name into the settings.py in a specific location at INSTALLED_APPS

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'import_excel_db',
]

Create the Model

Now we need to create an employee model.

from django.db import models

# Create your models here.

class Employee(models.Model):       
    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)     
    DOB = models.DateField(null=True, blank=True)   
    gender = models.CharField(max_length=5, default='',null=True)
    qualification = models.CharField(max_length=50,default='',null=True) 
    salary = models.FloatField(max_length=50,default='',null=True)   
    
    def __str__(self):
        return self.firstName
                
    objects = models.Manager()

Create the resource file

Import data from Excel into the database using the Django-import-export library and this library works with the concept of Resource. So we need to create a resources.py file in the app folder.

from import_export import resources
from .models import Employee

class EmployeeResource(resources.ModelResource):
    class Meta:
        model = Employee

Django File Upload

from http.client import HTTPResponse
from django.shortcuts import render 
import os
from django.core.files.storage import FileSystemStorage
 
 
# Create your views here.

def Import_Excel_pandas(request):
    
    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)    
        return render(request, 'Import_excel_db.html', {
            'uploaded_file_url': uploaded_file_url
        })   
    return render(request, 'Import_excel_db.html',{})

Create the views functions

views.py

Importing data from Excel into database using Pandas library

from http.client import HTTPResponse
from django.shortcuts import render
import pandas as pd
import os
from django.core.files.storage import FileSystemStorage
from .models import Employee

# Create your views here.

def Import_Excel_pandas(request):
    
    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)              
        empexceldata = pd.read_excel(filename)        
        dbframe = empexceldata
        for dbframe in dbframe.itertuples():
            obj = Employee.objects.create(Empcode=dbframe.Empcode,firstName=dbframe.firstName, middleName=dbframe.middleName,
                                            lastName=dbframe.lastName, email=dbframe.email, phoneNo=dbframe.phoneNo, address=dbframe.address,
                                            gender=dbframe.gender, DOB=dbframe.DOB,salary=dbframe.Salary )           
            obj.save()
        return render(request, 'Import_excel_db.html', {
            'uploaded_file_url': uploaded_file_url
        })   
    return render(request, 'Import_excel_db.html',{})

Importing data from excel into database using import-export library

from tablib import Dataset
from .Resources import EmployeeResource

def Import_excel(request):
    if request.method == 'POST' :
        Employee =EmployeeResource()
        dataset = Dataset()
        new_employee = request.FILES['myfile']
        data_import = dataset.load(new_employee.read())
        result = EmployeeResource.import_data(dataset,dry_run=True)
        if not result.has_errors():
            EmployeeResource.import_data(dataset,dry_run=False)        
    return render(request, 'Import_excel_db.html',{})

Provide Routing (URL patterns)

We need to add import_excel_db.urls to project urls.py

Urls.py

from django.contrib import admin
from django.urls import path,include
urlpatterns = [
    path('admin/', admin.site.urls),
    path('',include('import_excel_db.urls')),
]

Create urls.py file inside import_exceldb app to write the URL pattern or providing routing for the application.

urls.py

from django.urls import path
from . import views  
from Import_Excel import settings
from django.conf.urls.static import static
urlpatterns =[
path("",views.Import_Excel_pandas,name="Import_Excel_pandas"),
path('Import_Excel_pandas/', views.Import_Excel_pandas,name="Import_Excel_pandas"), 
path('Import_excel',views.Import_excel,name="Import_excel"),
] 
if settings.DEBUG:
    urlpatterns += static(settings.STATIC_URL, document_root = settings.STATIC_ROOT)

Static Root Configuring

import os
STATIC_ROOT =  os.path.join(BASE_DIR, "assets")

Create Template

Create a template folder inside import_exceldb app and create html file in the directory.

import_excel_db.html

{% block content %}
  <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>Import Excel Data into Database</h2>           
              <div class="clearfix"></div>
            </div>
            <div class="x_content">
              <div class="col-md-8 col-sm-12 col-xs-12 form-group">      
              </div>
                    <input type="file" name="myfile" class="form-control">
                             <button type="submit" class="btn btn-success" >Upload data using Pandas</button> 
                             <button type="submit" class="btn btn-success" >Upload data using Import_Export_Lib</button>   
                            </div>
                        </div>
                    </div>
                </div>
  </form>   
{% endblock %}

Now make migrations
 Python manage.py makemigrations
Python manage.py migrate
Python manage.py runserver

Document

Programming Tutorials