{"id":1008,"date":"2021-01-25T22:42:41","date_gmt":"2021-01-25T19:42:41","guid":{"rendered":"https:\/\/www.etemkeskin.com\/?p=1008"},"modified":"2021-01-25T22:49:13","modified_gmt":"2021-01-25T19:49:13","slug":"importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications","status":"publish","type":"post","link":"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/","title":{"rendered":"Importing and Exporting Data in Different Formats such as Excel, CSV, JSON in Django Applications"},"content":{"rendered":"\n

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<\/strong> library is a package that makes this process easier for us. It supports data in different formats such as Excel, CSV, JSON.<\/p>\n\n\n\n

Installation<\/h4>\n\n\n\n

We install the package on our computer using the pip<\/strong> package manager like below:<\/p>\n\n\n\n

pip install django-import-export<\/pre><\/div>\n\n\n\n
Then we add the following codes to our settings.py<\/strong> file. Also, if a problem occurs while importing or exporting data, we define IMPORT_EXPORT_USE_TRANSACTIONS = True<\/code> to ensure data integrity.<\/p>\n\n\n\n
INSTALLED_APPS = (\n    ...\n    'import_export',\n)\n# The default value is False\nIMPORT_EXPORT_USE_TRANSACTIONS = True<\/pre><\/div>\n\n\n\n
When using the django-import-export<\/strong> 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<\/strong> in our application.<\/p>\n\n\n\n
In our models.py<\/strong> file, we have defined two models named Category and Comment. We will import and export comments with the django-import-export library.<\/strong><\/p>\n\n\n\n
models.py<\/h5>\n\n\n\n
from django.db import models\n\nclass Category(models.Model):\n    title = models.CharField(max_length = 500, unique=True) \n\n    def __str__(self):\n        return "{0}".format(self.title)\n\nclass Comment(models.Model):\n    description = models.TextField()\n    category = models.ForeignKey(Category, on_delete= models.CASCADE )<\/pre><\/div>\n\n\n\n
We define which model to use in resources.py<\/strong> file.<\/p>\n\n\n\n
resources.py<\/strong><\/p>\n\n\n\n
from import_export import resources\nfrom label.models import Comment\n\nclass CommentResource(resources.ModelResource):\n    class Meta:\n        model = Comment<\/pre><\/div>\n\n\n\n
The file structure is as follows:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Exporting Data<\/h4>\n\n\n\n
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<\/strong> path.<\/p>\n\n\n\n
path('export-data\/', export_data, name="export_data"),<\/pre><\/div>\n\n\n\n
Then we create the template named export_import_data_page.html<\/strong> . We can use export form that user select data format as below: <\/p>\n\n\n\n
{% block content %}\n<div class="card card-secondary">\n  <div class="card-header">\n    <h3 class="card-title">Export Comments<\/h3>\n  <\/div>\n  <div class="card-body">\n    <form role="form" method="POST" action="{% url 'label:export_data' %}" enctype="multipart\/form-data">\n      {% csrf_token %}\n        <div class="form-group">\n          <label>Choose Format Type<\/label>\n          <select class="custom-select" name="file-format">\n            <option selected>Choose format...<\/option>\n            <option>CSV<\/option>\n            <option>JSON<\/option>\n            <option>XLS (Excel)<\/option>\n          <\/select>\n        <\/div> <br><br><br>\n      <button type="submit" class="btn btn-info btn-block">Export<\/button>\n    <\/form>\n  <\/div>\n<\/div>\n{% endblock %} <\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
views.py<\/strong><\/p>\n\n\n\n
In the views.py<\/strong> file, we define the function that will run when the form is posted.<\/p>\n\n\n\n
from django.http import HttpResponse\nfrom .resources import CommentResource\ndef export_data(request):\n    if request.method == 'POST':\n        # Get selected option from form\n        file_format = request.POST['file-format']\n        comment_resource = CommentResource()\n        dataset = comment_resource.export()\n        if file_format == 'CSV':\n            response = HttpResponse(dataset.csv, content_type='text\/csv')\n            response['Content-Disposition'] = 'attachment; filename="exported_data.csv"'\n            return response        \n        elif file_format == 'JSON':\n            response = HttpResponse(dataset.json, content_type='application\/json')\n            response['Content-Disposition'] = 'attachment; filename="exported_data.json"'\n            return response\n        elif file_format == 'XLS (Excel)':\n            response = HttpResponse(dataset.xls, content_type='application\/vnd.ms-excel')\n            response['Content-Disposition'] = 'attachment; filename="exported_data.xls"'\n            return response   \n    return render(request, 'label\/export_import_data_page.html') <\/pre><\/div>\n\n\n\n
Importing Data<\/h4>\n\n\n\n
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.<\/p>\n\n\n\n
path('import-data\/', import_data, name="import_data"),<\/pre><\/div>\n\n\n\n
Below is the code of the form to be used in our template.<\/p>\n\n\n\n
{% block content %}\n<div class="card card-secondary">\n  <div class="card-header">\n    <h3 class="card-title">Import Comments<\/h3>\n  <\/div>\n  <div class="card-body">\n    <form role="form" method="POST" action="{% url 'label:import_data' %}" enctype="multipart\/form-data">\n      {% csrf_token %}\n      <div class="form-group">\n        <label>Choose Format Type<\/label><br>           \n        <input class="mb-2" type="file" name="importData">\n        <select class="custom-select" name="file-format1"> \n          <option selected>Choose format...<\/option>\n          <option>CSV<\/option>\n          <option>JSON<\/option>\n        <\/select>\n      <\/div> <br><br><br>\n      <button type="submit" class="btn btn-info btn-block">Import<\/button>\n    <\/form>\n  <\/div>\n<\/div>\n{% endblock %} <\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
views.py<\/strong><\/p>\n\n\n\n
from tablib import Dataset\ndef import_data(request):\n    if request.method == 'POST':\n        file_format = request.POST['file-format1']\n        comment_resource = CommentResource()\n        dataset = Dataset()\n        new_comments = request.FILES['importData']\n\n        if file_format == 'CSV':\n            imported_data = dataset.load(new_comments.read().decode('utf-8'),format='csv')\n            result = comment_resource.import_data(dataset, dry_run=True)                                                                 \n        elif file_format == 'JSON':\n            imported_data = dataset.load(new_comments.read().decode('utf-8'),format='json')\n            # Testing data import\n            result = comment_resource.import_data(dataset, dry_run=True) \n\n        if not result.has_errors():\n            # Import now\n            comment_resource.import_data(dataset, dry_run=False)\n\n    return render(request, 'label\/export_import_data_page.html')')<\/pre><\/div>\n\n\n\n
Category<\/strong> model has a one-to-many relationship to Comment<\/strong> model. We need to change our resource.py<\/strong> file so that we can manage this relationship.<\/p>\n\n\n\n
resources.py<\/strong><\/p>\n\n\n\n
from import_export import resources, widgets, fields\nfrom label.models import Comment, Category\n\nclass CharRequiredWidget(widgets.CharWidget):\n    def clean(self, value, row=None, *args, **kwargs):\n        val = super().clean(value)\n        if val:\n            return val\n        else:\n            raise ValueError('this field is required')\n\nclass ForeignkeyRequiredWidget(widgets.ForeignKeyWidget):\n    def clean(self, value, row=None, *args, **kwargs):\n        if value:\n            print(self.field, value)\n            return self.get_queryset(value, row, *args, **kwargs).get(**{self.field: value})\n        else:\n            raise ValueError(self.field+ " required")\n\nclass CommentResource(resources.ModelResource):\n    category = fields.Field(column_name='category', attribute='category', widget=ForeignkeyRequiredWidget(Category, 'title'),\n                        saves_null_values=False) # title Category modelindeki kolon ismi\n    description = fields.Field(saves_null_values=False, column_name='description', attribute='description',\n                          widget=CharRequiredWidget())\n\n    class Meta:\n        model = Comment\n        fields = ('id', 'description', 'category')\n        clean_model_instances = True\n\n# class CommentResource(resources.ModelResource):\n#     class Meta:\n#         model = Comment<\/pre><\/div>\n\n\n\n
We can try our application with the following CSV file structure.<\/p>\n\n\n\n
id,category,description\n1,Computer,Lorem ipsum dolor sit amet, consectetur \n2,Computer,adipiscing elit, sed do eiusmod tempor incididunt ut \n3,TV,labore et dolore magna aliqua. Ut enim ad minim veniam, quis nost  \n4,TV,Sed ut perspiciatis unde omnis iste natus error sit voluptatem \n5,TV,accusantium doloremque laudantium, totam rem aperiam, eaque ipsa<\/pre><\/div>\n\n\n\n
Django Admin<\/h4>\n\n\n\n
We can easily import and export our data by adding the codes below to the admin.py<\/strong> file.<\/p>\n\n\n\n
from django.contrib import admin\nfrom label.models import  Comment\nfrom import_export.admin import ImportExportModelAdmin\n\n@admin.register(Comment)\nclass CommentAdmin(ImportExportModelAdmin):\n    pass\n<\/pre><\/div>\n\n\n\n
When we enter the Comment model in the Admin panel, we will notice IMPORT<\/strong> and EXPORT<\/strong> 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.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
When we click export button<\/strong>, we will see the form in which we can define data format in the screen that will appear.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If we want to customize the Export and Import forms in the Admin panel, we can edit admin.py<\/strong> file in  as follows.<\/p>\n\n\n\n
from django.contrib import admin\nfrom label.models import  Comment, Category\nfrom import_export.admin import ImportExportModelAdmin\n# METHOD 1\n# @admin.register(Comment)\n# class CommentAdmin(ImportExportModelAdmin):\n#     pass\n\n# METHOD 2\nfrom .resources import CommentResource\nclass CommentAdmin(ImportExportModelAdmin):\n    resource_class = CommentResource\n    list_display = ('description', 'category')\n\nadmin.site.register(Comment, CommentAdmin)<\/pre><\/div>\n\n\n\n
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.<\/p>\n\n\n\n
Sources<\/h6>\n\n\n\n
  1. https:\/\/django-import-export.readthedocs.io\/en\/latest\/index.html<\/li><\/ol>\n\n\n\n
    <\/p>\n","protected":false},"excerpt":{"rendered":"
    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. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[30,4],"tags":[],"yoast_head":"\nImporting and Exporting Data in Different Formats such as Excel, CSV, JSON in Django Applications - blog website<\/title>\n<meta name=\"description\" content=\"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.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/\" \/>\n<meta property=\"og:locale\" content=\"tr_TR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Importing and Exporting Data in Different Formats such as Excel, CSV, JSON in Django Applications - blog website\" \/>\n<meta property=\"og:description\" content=\"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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/\" \/>\n<meta property=\"og:site_name\" content=\"blog website\" \/>\n<meta property=\"article:published_time\" content=\"2021-01-25T19:42:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-25T19:49:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.etemkeskin.com\/wp-content\/uploads\/2021\/01\/image-5.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Tahmini okuma s\u00fcresi\" \/>\n\t<meta name=\"twitter:data1\" content=\"7 dakika\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.etemkeskin.com\/#website\",\"url\":\"https:\/\/www.etemkeskin.com\/\",\"name\":\"blog website\",\"description\":\"Etem KESK\\u0130N\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/www.etemkeskin.com\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"tr\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/#primaryimage\",\"inLanguage\":\"tr\",\"url\":\"https:\/\/www.etemkeskin.com\/wp-content\/uploads\/2021\/01\/image-5.png\",\"contentUrl\":\"https:\/\/www.etemkeskin.com\/wp-content\/uploads\/2021\/01\/image-5.png\",\"width\":128,\"height\":241},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/#webpage\",\"url\":\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/\",\"name\":\"Importing and Exporting Data in Different Formats such as Excel, CSV, JSON in Django Applications - blog website\",\"isPartOf\":{\"@id\":\"https:\/\/www.etemkeskin.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/#primaryimage\"},\"datePublished\":\"2021-01-25T19:42:41+00:00\",\"dateModified\":\"2021-01-25T19:49:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.etemkeskin.com\/#\/schema\/person\/dcbc30282861ce578b96a79ce8789629\"},\"description\":\"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.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/#breadcrumb\"},\"inLanguage\":\"tr\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.etemkeskin.com\/index.php\/2021\/01\/25\/importing-and-exporting-data-in-different-formats-such-as-excel-csv-json-in-django-applications\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Ana sayfa\",\"item\":\"https:\/\/www.etemkeskin.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Importing and Exporting Data in Different Formats such as Excel, CSV, JSON in Django Applications\"}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.etemkeskin.com\/#\/schema\/person\/dcbc30282861ce578b96a79ce8789629\",\"name\":\"etemkeskin\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.etemkeskin.com\/#personlogo\",\"inLanguage\":\"tr\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6af0148b790691ed24ae245fb3dc773b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6af0148b790691ed24ae245fb3dc773b?s=96&d=mm&r=g\",\"caption\":\"etemkeskin\"},\"url\":\"https:\/\/www.etemkeskin.com\/index.php\/author\/etemkeskinyahoo-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","_links":{"self":[{"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/posts\/1008"}],"collection":[{"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/comments?post=1008"}],"version-history":[{"count":43,"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/posts\/1008\/revisions"}],"predecessor-version":[{"id":1051,"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/posts\/1008\/revisions\/1051"}],"wp:attachment":[{"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/media?parent=1008"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/categories?post=1008"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.etemkeskin.com\/index.php\/wp-json\/wp\/v2\/tags?post=1008"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}