In today’s data-driven landscape, efficiently managing data is vital for the success of any application. Django, a high-level Python web framework, streamlines the creation of robust web applications. However, many projects require importing substantial datasets from external sources like Excel files. This is where Pandas, a powerful Python library for data manipulation, becomes invaluable.

In this blog, we’ll guide you through the process of importing Excel data into a Django application using Pandas, step by step.

Prerequisites

Before implementing, check whether you have the following installed:

• Python (version 3.6 or above)
• Django (version 3.0 or above)
• Pandas (version 1.0 or above)
• openpyxl (to read Excel files)

You can install these packages using pip:

pip install django pandas openpyxl

Step 1: Setting Up Your Django Project

First, create a new Django project and app. Open the terminal to run the following commands:

django-admin startproject excel_import_project
cd excel_import_project
django-admin startapp data_import

Next, add your new app to the INSTALLED_APPS list in settings.py:

# excel_import_project/settings.py

INSTALLED_APPS = [
    ...
    'data_import',
]

Step 2: Creating Your Data Model

Define a model in models.py that corresponds to the structure of your Excel data. For this example, let’s assume we’re importing data about books, with columns for title, author, and publication date.

# data_import/models.py

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=255)
    author = models.CharField(max_length=255)
    publication_date = models.DateField()

    def __str__(self):
        return self.title

After defining your model, run the following commands to create the necessary database tables:

python manage.py makemigrations
python manage.py migrate

Step 3: Creating the Upload Form

Create a form to handle the file upload. In your forms.py file, add the following code:

# data_import/forms.py

from django import forms

class UploadFileForm(forms.Form):
    file = forms.FileField()

Step 4: Writing the View to Handle the File Upload

Next, create a view to handle the file upload and process the data. Add the following code to your views.py file:

# data_import/views.py

import pandas as pd
from django.shortcuts import render, redirect
from django.contrib import messages
from .forms import UploadFileForm
from .models import Book
from datetime import datetime

def upload_file(request):
    if request.method == 'POST':
        form = UploadFileForm(request.POST, request.FILES)
        if form.is_valid():
            file = request.FILES['file']
            df = pd.read_excel(file)
            for _, row in df.iterrows():
                book, created = Book.objects.get_or_create(
                    title=row['Title'],
                    author=row['Author'],
                    publication_date=datetime.strptime(row['Publication Date'], '%Y-%m-%d')
                )
                if created:
                    messages.success(request, f'Successfully imported {book.title}')
                else:
                    messages.warning(request, f'{book.title} already exists')
            return redirect('upload_file')
    else:
        form = UploadFileForm()
    return render(request, 'data_import/upload.html', {'form': form})

Step 5: Creating the Template

Create a template to display the upload form. In your templates/data_import directory, create a file named upload.html and add the following code:

    


    
Upload Excel File
    {% if messages %}
        

            {% for message in messages %}
                {{ message }}
            {% endfor %}
        
    {% endif %}
    

        {% csrf_token %}
        {{ form.as_p }}
        Upload
    

Step 6: Configuring URLs

Add a URL pattern to route requests to your upload view. Add the following code to your urls.py file:

# data_import/urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('upload/', views.upload_file, name='upload_file'),
]

Also, include this URL pattern in your project’s main urls.py file:

# excel_import_project/urls.py

from django.contrib import admin
from django.urls import path, include

urlpatterns = [
    path('admin/', admin.site.urls),
    path('data_import/', include('data_import.urls')),
]

Step 7: Running the Server

Run your development server:

python manage.py runserver

Navigate to http://127.0.0.1:8000/data_import/upload/, where you should see the upload form. Upload your Excel file, and the data will be imported into the Book model.

Step 8: Displaying Imported Data in Django Admin

To make it easy to view and manage your imported data, register the Book model with Django admin. Add the following code to admin.py:

# data_import/admin.py

from django.contrib import admin
from .models import Book

admin.site.register(Book)

Now, run your development server and navigate to the Django admin interface at http://127.0.0.1:8000/admin/, log in, and you should see the Book model listed. Here, you can view, add, and edit book entries.

To read more about exporting Django model data to an Excel sheet using Pandas, refer to our blog How to Export Django Model Data to an Excel Sheet Using Pandas

Conclusion

Importing data from Excel into Django using Pandas is a powerful way to handle large datasets efficiently. By leveraging the capabilities of Pandas for data manipulation and Django’s ORM for database operations, you can streamline the process of getting data into your Django application.

This tutorial walked you through setting up a Django project, defining a model, writing an import script, and importing an Excel file into your Django application using a URL and a view. With these steps, you should be well-equipped to handle similar data import tasks in your Django projects. Remember, this is just a starting point. Depending on your use case, you might need to handle more complex data structures, perform data validation, or implement error handling. The combination of Django and Pandas provides a flexible foundation to build upon for all your data import needs.