Importing and Exporting Data in Different Formats such as Excel, CSV, JSON in Django Applications

Django, Python  |     |  

When we develop web applications with Django, we want to transfer our data to the database or to get the data from the database in a tabular form in different formats. The django-import-export library is a package that makes this process easier for us. It supports data in different formats such as Excel, CSV, JSON.

Installation

We install the package on our computer using the pip package manager like below:

pip install django-import-export

Then we add the following codes to our settings.py file. Also, if a problem occurs while importing or exporting data, we define IMPORT_EXPORT_USE_TRANSACTIONS = True to ensure data integrity.

INSTALLED_APPS = (
    ...
    'import_export',
)
# The default value is False
IMPORT_EXPORT_USE_TRANSACTIONS = True

When using the django-import-export library, there is a Resource concept similar to our model classes that will describe how this resource can be imported or exported. For this, we create a file called resource.py in our application.

In our models.py file, we have defined two models named Category and Comment. We will import and export comments with the django-import-export library.

models.py
from django.db import models

class Category(models.Model):
    title = models.CharField(max_length = 500, unique=True) 

    def __str__(self):
        return "{0}".format(self.title)

class Comment(models.Model):
    description = models.TextField()
    category = models.ForeignKey(Category, on_delete= models.CASCADE )

We define which model to use in resources.py file.

resources.py

from import_export import resources
from label.models import Comment

class CommentResource(resources.ModelResource):
    class Meta:
        model = Comment

The file structure is as follows:

Exporting Data

We will enable the user to download the Comment table from database to the computer in the format that the user wants by creating a form at frontend. For this, we will first define urls.py path.

path('export-data/', export_data, name="export_data"),

Then we create the template named export_import_data_page.html . We can use export form that user select data format as below: 

{% block content %}
<div class="card card-secondary">
  <div class="card-header">
    <h3 class="card-title">Export Comments</h3>
  </div>
  <div class="card-body">
    <form role="form" method="POST" action="{% url 'label:export_data' %}" enctype="multipart/form-data">
      {% csrf_token %}
        <div class="form-group">
          <label>Choose Format Type</label>
          <select class="custom-select" name="file-format">
            <option selected>Choose format...</option>
            <option>CSV</option>
            <option>JSON</option>
            <option>XLS (Excel)</option>
          </select>
        </div> <br><br><br>
      <button type="submit" class="btn btn-info btn-block">Export</button>
    </form>
  </div>
</div>
{% endblock %}

views.py

In the views.py file, we define the function that will run when the form is posted.

from django.http import HttpResponse
from .resources import CommentResource
def export_data(request):
    if request.method == 'POST':
        # Get selected option from form
        file_format = request.POST['file-format']
        comment_resource = CommentResource()
        dataset = comment_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, 'label/export_import_data_page.html')

Importing Data

By creating a form in Frontend, we will upload the data in CSV or JSON format to the database. First, we define the path that will work when the form is posted.

path('import-data/', import_data, name="import_data"),

Below is the code of the form to be used in our template.

{% block content %}
<div class="card card-secondary">
  <div class="card-header">
    <h3 class="card-title">Import Comments</h3>
  </div>
  <div class="card-body">
    <form role="form" method="POST" action="{% url 'label:import_data' %}" enctype="multipart/form-data">
      {% csrf_token %}
      <div class="form-group">
        <label>Choose Format Type</label><br>           
        <input class="mb-2" type="file" name="importData">
        <select class="custom-select" name="file-format1"> 
          <option selected>Choose format...</option>
          <option>CSV</option>
          <option>JSON</option>
        </select>
      </div> <br><br><br>
      <button type="submit" class="btn btn-info btn-block">Import</button>
    </form>
  </div>
</div>
{% endblock %}

views.py

from tablib import Dataset
def import_data(request):
    if request.method == 'POST':
        file_format = request.POST['file-format1']
        comment_resource = CommentResource()
        dataset = Dataset()
        new_comments = request.FILES['importData']

        if file_format == 'CSV':
            imported_data = dataset.load(new_comments.read().decode('utf-8'),format='csv')
            result = comment_resource.import_data(dataset, dry_run=True)                                                                 
        elif file_format == 'JSON':
            imported_data = dataset.load(new_comments.read().decode('utf-8'),format='json')
            # Testing data import
            result = comment_resource.import_data(dataset, dry_run=True) 

        if not result.has_errors():
            # Import now
            comment_resource.import_data(dataset, dry_run=False)

    return render(request, 'label/export_import_data_page.html')')

Category model has a one-to-many relationship to Comment model. We need to change our resource.py file so that we can manage this relationship.

resources.py

from import_export import resources, widgets, fields
from label.models import Comment, Category

class CharRequiredWidget(widgets.CharWidget):
    def clean(self, value, row=None, *args, **kwargs):
        val = super().clean(value)
        if val:
            return val
        else:
            raise ValueError('this field is required')

class ForeignkeyRequiredWidget(widgets.ForeignKeyWidget):
    def clean(self, value, row=None, *args, **kwargs):
        if value:
            print(self.field, value)
            return self.get_queryset(value, row, *args, **kwargs).get(**{self.field: value})
        else:
            raise ValueError(self.field+ " required")

class CommentResource(resources.ModelResource):
    category = fields.Field(column_name='category', attribute='category', widget=ForeignkeyRequiredWidget(Category, 'title'),
                        saves_null_values=False) # title Category modelindeki kolon ismi
    description = fields.Field(saves_null_values=False, column_name='description', attribute='description',
                          widget=CharRequiredWidget())

    class Meta:
        model = Comment
        fields = ('id', 'description', 'category')
        clean_model_instances = True

# class CommentResource(resources.ModelResource):
#     class Meta:
#         model = Comment

We can try our application with the following CSV file structure.

id,category,description
1,Computer,Lorem ipsum dolor sit amet, consectetur 
2,Computer,adipiscing elit, sed do eiusmod tempor incididunt ut 
3,TV,labore et dolore magna aliqua. Ut enim ad minim veniam, quis nost  
4,TV,Sed ut perspiciatis unde omnis iste natus error sit voluptatem 
5,TV,accusantium doloremque laudantium, totam rem aperiam, eaque ipsa

Django Admin

We can easily import and export our data by adding the codes below to the admin.py file.

from django.contrib import admin
from label.models import  Comment
from import_export.admin import ImportExportModelAdmin

@admin.register(Comment)
class CommentAdmin(ImportExportModelAdmin):
    pass

When we enter the Comment model in the Admin panel, we will notice IMPORT and EXPORT buttons at the top right. By pressing the EXPORT button, we can export our Comment table in many formats as seen in the picture below.

When we click export button, we will see the form in which we can define data format in the screen that will appear.

If we want to customize the Export and Import forms in the Admin panel, we can edit admin.py file in as follows.

from django.contrib import admin
from label.models import  Comment, Category
from import_export.admin import ImportExportModelAdmin
# METHOD 1
# @admin.register(Comment)
# class CommentAdmin(ImportExportModelAdmin):
#     pass

# METHOD 2
from .resources import CommentResource
class CommentAdmin(ImportExportModelAdmin):
    resource_class = CommentResource
    list_display = ('description', 'category')

admin.site.register(Comment, CommentAdmin)

It is a very useful library that you can use in Django projects. In this post, I tried to explain all the situations that can be used in a simple and clear way. Good luck.

Sources
  1. https://django-import-export.readthedocs.io/en/latest/index.html