This post cross-published with OnePublish
Welcome to 3rd post of Django Package Series. In this post we are going to learn one of the best Django package which allows to import and export data easily.
Youtube Channel with video tutorials - Reverse Python Youtube
Before we go on, please visit Reverse Python to find more cool stuff with Python.
Article No Longer Available
django-import-export
The django-import-export library supports multiple formats, including xls, csv, json, yaml, and all other formats supported by tablib. It also have a Django admin integration, which is really convenient to use.
Installation & Configuration
Let's build a project to understand it better. Create a new Django project named dashboard and inside it create a new app named reports.
To install the package, run the following command:
pip install django-import-export
Then, in your settings.py add your app name (reports) and import-export library into INSTALLED_APPS:
INSTALLED_APPS = (
...
'import_export',
'reports'
)
I also recommend to add optional configuration to end of settings.py:
IMPORT_EXPORT_USE_TRANSACTIONS = True
The default value is False. It determines if the library will use database transactions on data import.
After this section I will show you main parts of the project. If you don't know how to configure urls.py or templates, I will put GitHub repo link end of this post, so you can clone and run the project.
Create Model and Resources
In your models.py create a model named Employee:
from django.db import models
class Employee(models.Model):
first_name = models.CharField(max_length=30)
last_name = models.CharField(max_length=60)
email = models.EmailField(blank=True)
day_started = models.DateField()
location = models.CharField(max_length=100, blank=True)
def __str__(self):
return self.first_name
The django-import-export library works with the concept of Resource, which is class definition very similar to how Django handles model forms and admin classes.
If you want to handle data directly from Django admin then you should put the code inside the admin.py file.
However, our implementation for this project is not related to the Django admin, so let's create resources.py file inside app folder.
from import_export import resources
from .models import Employee
class EmployeeResource(resources.ModelResource):
class Meta:
model = Employee
Export Data
Well, we are going to create form and let the user to select format of file. Update your views.py:
from django.shortcuts import render
from django.http import HttpResponse
from tablib import Dataset
from .resources import EmployeeResource
from .models import Employee
def export_data(request):
if request.method == 'POST':
# Get selected option from form
file_format = request.POST['file-format']
employee_resource = EmployeeResource()
dataset = employee_resource.export()
if file_format == 'CSV':
response = HttpResponse(dataset.csv, content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename="exported_data.csv"'
return response
elif file_format == 'JSON':
response = HttpResponse(dataset.json, content_type='application/json')
response['Content-Disposition'] = 'attachment; filename="exported_data.json"'
return response
elif file_format == 'XLS (Excel)':
response = HttpResponse(dataset.xls, content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename="exported_data.xls"'
return response
return render(request, 'export.html')
It is very simple, when user selects JSON, file will exported as JSON or if user selects CSV then file will exported as CSV.
export.html
{% extends 'base.html' %}
{% block content %}
<h3>Export Data</h3>
<p>exporting from database</p>
<form method="post" enctype="multipart/form-data">
{% csrf_token %}
<p>Please select format of file.</p>
<select name="file-format" class="form-control my-3">
<option selected>Choose format...</option>
<option>CSV</option>
<option>JSON</option>
<option>XLS (Excel)</option>
</select>
<button class="btn btn-primary" type="submit">Export</button>
</form>
<a href="{% url 'home' %}">Return Home View</a>
{% endblock %}
Import Data
Assume that we have file named employees.csv:
first_name,last_name,email,day_started,location,id
Peter,Parker,peter@parker.com,2015-05-18,New York,
James,Bond,james007@bond.com,2014-08-11,London,
The id must be present because it is the primary key. It will be generated automatically.
So, we need to import CSV or JSON file to database. Add following function to your views.py:
def import_data(request):
if request.method == 'POST':
file_format = request.POST['file-format']
employee_resource = EmployeeResource()
dataset = Dataset()
new_employees = request.FILES['importData']
if file_format == 'CSV':
imported_data = dataset.load(new_employees.read().decode('utf-8'),format='csv')
result = employee_resource.import_data(dataset, dry_run=True)
elif file_format == 'JSON':
imported_data = dataset.load(new_employees.read().decode('utf-8'),format='json')
# Testing data import
result = employee_resource.import_data(dataset, dry_run=True)
if not result.has_errors():
# Import now
employee_resource.import_data(dataset, dry_run=False)
return render(request, 'import.html')
Let the user to select format of file which will imported.
import.html
{% extends 'base.html' %}
{% block content %}
<h3>Import Data</h3>
<p>importing to database</p>
<form method="post" enctype="multipart/form-data">
{% csrf_token %}
<input type="file" name="importData">
<p>Please select format of file.</p>
<select name="file-format" class="form-control my-3">
<option selected>Choose format...</option>
<option>CSV</option>
<option>JSON</option>
</select>
<button class="btn btn-primary" type="submit">Import</button>
</form>
<a href="{% url 'home' %}">Return Home View</a>
{% endblock %}
Once you imported your file go and check admin and you will see imported data in your model.
Django Admin
If you want to use import export in admin then simply add following code in your admin.py:
from import_export.admin import ImportExportModelAdmin
from django.contrib import admin
from .models import Employee
@admin.register(Employee)
class EmployeeAdmin(ImportExportModelAdmin):
pass
Here's how it looks like:
GitHub
Some of you can be confused so it is better to clone the project from my GitHub and do stuff with it. Here is my GitHub link for this project:
thepylot / Django-Import-Export
Report import export app with django-import-export package (tutorial)
Django-Import-Export
Report app using django-import-export package (tutorial)
Getting Started
This tutorial works on Python 3+ and Django 2+.
Install dependencies:
python3 -m pip3 install -r requirements.txt
and run following commands:
python3 manage.py makemigrations reports
python3 manage.py migrate
python3 manage.py runserver
Mission Accomplished!
Great job! You learned really useful information today. For more information about django-import-export take look Documentation
If you liked the tutorial please please visit Reverse Python to find more cool stuff with Python.
Top comments (6)
I keep trying to import data but get the error
"Field 'id' expected a number but got 'Plastic Consumables'."
Plastic Consumables is my 1st data column after the id header
I'm importing via admin.
Looks like Plastic Consumables is a foreign key field. If so, you need to use foreign key widget (see more info in docs). Don`t forget to specify a resource_class under admin model inherited from ImportExportModelAdmin!
Thank you! But I got a little bit confused, in your input_data view, when you say:
Shouldn't dataset be replaced by imported_data? Otherwise, where is imported_data being used?
def str(self):
return self.first_name
doesnt work well when importing
change to
def str(self):
return '%s' % self.first_name
Thank you for the article! Tell me how to make it so that you can import xlsx? or xls?
Can you add the code to export it as PDF too please?