Django Web Framework Tutorials

Import Data from Excel Sheets into Databases using Django

Last Updated: by

In the realm of web development, the ability to efficiently import data from external sources into your application’s database is a crucial skill. If you’re working with Django, a popular and powerful web framework, you’re in luck. In this blog post, we’ll take you through the process of importing data from Excel sheets into your Django application’s database, step by step.

Step 1: Prepare Your Django Project

First things first, ensure that your Django project is up and running, complete with the necessary models that will receive the imported data. For example, let’s assume you have a model named Product with fields like name, price, and quantity.

Step 2: Install Required Packages

Before diving into the data import process, make sure to install the openpyxl library, which is a robust Python package for working with Excel files:

pip install openpyxl

In this project, we will be using openpyxl library as xlwt no more maintained.

Advantages of openpyxl:

  • Supports Excel files in the .xlsx format (Excel 2007 and later).
  • Provides better compatibility with newer Excel features like charts, tables, and advanced formatting.
  • Can read and modify existing Excel files.
  • Supports cell styles, formulas, and more advanced features.
  • Actively maintained and updated, with a strong community.

Step 3: Create the Import View

Now, create a view that handles the data import. Open your views.py file and import the required modules:

from django.shortcuts import render
from openpyxl import load_workbook
from .models import Product

Then, create a view function to handle the import process:

def import_from_excel(request):
    if request.method == 'POST':
        excel_file = request.FILES['excel_file']
        wb = load_workbook(excel_file)
        ws = wb.active

        for row in ws.iter_rows(min_row=2, values_only=True):
            name, price, quantity = row
            Product.objects.create(name=name, price=price, quantity=quantity)

        return render(request, 'import_success.html')

    return render(request, 'import_form.html')

Step 4: Create the HTML Templates

You’ll need two HTML templates: one for the form to upload the Excel file (import_form.html) and another to show a success message (import_success.html).

Step 5: Create a URL Route

Add a URL route in your urls.py file to map to the import view:

from django.urls import path
from .views import import_from_excel

urlpatterns = [
    # Other URL patterns
    path('import/', import_from_excel, name='import_from_excel'),
]

Step 6: Create an Upload Form

In your import_form.html template, create a form for users to upload their Excel files:

<form method="post" enctype="multipart/form-data">
    {% csrf_token %}
    <input type="file" name="excel_file">
    <button type="submit">Import</button>
</form>

Step 7: Run the Server and Test

With all the pieces in place, run your Django developmeent server. Navigate to the URL you defined for the import view, and you’ll see the Excel file upload form. Choose an Excel file containing data that matches your Product model’s fields, and click the “Import” button. After the import is complete, you’ll be redirected to the success page.

And there you have it! You’ve successfully built a feature to import data from Excel sheets into your Django application’s database. This can greatly streamline the process of populating your database with existing data, making your application more efficient and user-friendly.

Blogs You Might Like to Read!