Django How to Read From a Csv Into a Table
django-excel - Let yous focus on data, instead of file formats¶
| Writer: | C.W. |
|---|---|
| Source code: | http://github.com/pyexcel-webwares/django-excel.git |
| Issues: | http://github.com/pyexcel-webwares/django-excel/issues |
| License: | New BSD License |
| Released: | 0.0.ten |
| Generated: | Nov 10, 2020 |
Here is a typical conversation betwixt the developer and the user:
User : "I take uploaded an excel file" "merely your application says un-supported file format" Developer : "Did you upload an xlsx file or a csv file?" User : "Well, I am not sure. I saved the data using " "Microsoft Excel. Surely, it must be in an excel format." Developer : "OK. Here is the affair. I were not told to support" "all bachelor excel formats in twenty-four hour period 1. Live with information technology" "or delay the projection 10 number of days." django-excel is based on pyexcel and makes it easy to consume/produce data stored in excel files over HTTP protocol equally well every bit on file arrangement. This library can turn the excel information into a listing of lists, a list of records(dictionaries), dictionaries of lists. And vice versa. Hence it lets yous focus on data in Django based web evolution, instead of file formats.
The thought originated from the common usability trouble: when an excel file driven spider web application is delivered for not-developer users (ie: squad assistant, human resource administrator etc). The fact is that not anybody knows (or cares) nigh the differences between various excel formats: csv, xls, xlsx are notwithstanding to them. Instead of preparation those users about file formats, this library helps spider web developers to handle most of the excel file formats past providing a common programming interface. To add together a specific excel file format type to you awarding, all you need is to install an extra pyexcel plugin. Hence no lawmaking changes to your application and no bug with excel file formats whatsoever more. Looking at the customs, this library and its associated ones try to become a small and easy to install culling to Pandas.
The highlighted features are:
- excel data import into and consign from databases
- turn uploaded excel file directly into Python information construction
- pass Python data structures every bit an excel file download
- provide information persistence as an excel file in server side
- supports csv, tsv, csvz, tsvz by default and other formats are supported via the post-obit plugins:
| Package name | Supported file formats | Dependencies |
|---|---|---|
| pyexcel-io | csv, csvz [one], tsv, tsvz [2] | |
| pyexcel-xls | xls, xlsx(read just), xlsm(read only) | xlrd, xlwt |
| pyexcel-xlsx | xlsx | openpyxl |
| pyexcel-ods3 | ods | pyexcel-ezodf, lxml |
| pyexcel-ods | ods | odfpy |
| Package proper noun | Supported file formats | Dependencies |
|---|---|---|
| pyexcel-xlsxw | xlsx(write only) | XlsxWriter |
| pyexcel-libxlsxw | xlsx(write only) | libxlsxwriter |
| pyexcel-xlsxr | xlsx(read only) | lxml |
| pyexcel-xlsbr | xlsb(read merely) | pyxlsb |
| pyexcel-odsr | read only for ods, fods | lxml |
| pyexcel-odsw | write only for ods | loxun |
| pyexcel-htmlr | html(read only) | lxml,html5lib |
| pyexcel-pdfr | pdf(read but) | camelot |
Installation¶
Yous can install django-excel via pip:
$ pip install django-excel or clone information technology and install information technology:
$ git clone https://github.com/pyexcel-webwares/django-excel.git $ cd django-excel $ python setup.py install Installation of individual plugins , please refer to private plugin page. For example, if y'all demand xlsx file support, delight install pyexcel-xlsx:
$ pip install pyexcel-xlsx Opposite to Django's philosophy of 'battery included', django-excel does not come with all batteries due to the size of the dependency(xlwt, openpyxl, odfpy). Hence, Django developer is left with the choice to install and load the excel file formats.
Setup¶
Yous volition need to update your settings.py:
FILE_UPLOAD_HANDLERS = ( "django_excel.ExcelMemoryFileUploadHandler" , "django_excel.TemporaryExcelFileUploadHandler" ) Tested Django Versions¶
ii.1, 2.08, 1.11.fifteen, i.10.eight, 1.ix.13, 1.8.xviii, 1.vii.11, 1.6.11
Since xv March 2015, python 2.vi are no longer tested via travis-ci.
Back up the project¶
If your company has embedded pyexcel and its components into a revenue generating product, please support me on github, patreon or compensation source to maintain the project and develop it farther.
If you are an private, you are welcome to support me besides and for all the same long y'all feel like. As my backer, you will receive early access to pyexcel related contents.
And your issues will get prioritized if you would like to become my patreon as pyexcel pro user.
With your financial support, I will exist able to invest a piddling fleck more than time in coding, documentation and writing interesting posts.
More excel file formats¶
The example application understands csv, tsv and its zipped variants: csvz and tsvz. If you would like to expand the list of supported excel file formats (see A list of file formats supported by external plugins) for your own application, you could install one or all of the following:
pip install pyexcel - xls pip install pyexcel - xlsx pip install pyexcel - ods Alert
If you are using pyexcel <=0.two.1, you all the same need to import each plugin manually, eastward.g. import pyexcel.ext.xls and Your IDE or pyflakes may highlight information technology as un-used just it is used. The registration of the extra file format support happens when the import action is performed
Tutorial¶
In order to dive in django-excel and get hands-on feel quickly, the examination awarding for django-excel volition be introduced here. Then, it is advisable that you should check out the lawmaking from github
git clone https : // github . com / pyexcel / django - excel . git The exam awarding is written according to Part 1, Part 2 and Role 3 of django tutorial. If you should wonder how the test application was written, please visit django documentation and come back.
Once you have the lawmaking, please change to django-excel directory so install all dependencies:
$ cd django-excel $ pip install -r requirements.txt $ pip install -r tests/requirements.txt And so run the exam application:
$ python manage.py runserver Performing system checks... Arrangement check identified no problems (0 silenced). You have 9 unapplied migration(south). Your projection may not piece of work properly until you apply the migrations for app(s): admin, auth, contenttypes. Run 'python manage.py drift' to apply them. July 06, 2017 - 08:29:10 Django version 1.xi.3, using settings 'mysite.settings' Starting evolution server at http://127.0.0.1:8000/ Quit the server with Control-C. Notation
The 9 unapplied migration(due south) were ignored because migrations are out of scope in this tutorial.
Handle excel file upload and download¶
This example shows how to process uploaded excel file and how to make data download as an excel file. Open your browser and visit http://localhost:8000/polls/, you shall see this upload form:
Choose an excel canvas, for example an xls file, and press "Submit". You lot will become a csv file for download.
Please open the file polls/views.py and focus on the following code section:
# Create your views here. def upload ( asking ): if request . method == "POST" : form = UploadFileForm ( asking . POST , request . FILES ) if form . is_valid (): filehandle = request . FILES [ "file" ] render excel . make_response ( filehandle . get_sheet (), "csv" , file_name = "download" ) else : form = UploadFileForm () render render ( asking , "upload_form.html" , { "form" : form , "title" : "Excel file upload and download example" , "header" : ( "Please choose any excel file " + "from your cloned repository:" ), }, UploadFileForm is html widget for file upload class in the html page. Then expect down at filehandle. It is an instance of either ExcelInMemoryUploadedFile or TemporaryUploadedExcelFile, which inherit ExcelMixin and hence have a list of conversion methods to phone call, such equally get_sheet, get_array, etc.
For the response, make_response() converts pyexcel.Canvas instance obtained via get_sheet() into a csv file for download.
Please feel free to change those functions according to the mapping table.
Handle data import¶
This example shows how to import uploaded excel file into django models. We are going to import sample-data.xls
- question
- choice
into the following data models:
class Question ( models . Model ): question_text = models . CharField ( max_length = 200 ) pub_date = models . DateTimeField ( 'date published' ) slug = models . CharField ( max_length = ten , unique = True , default = "question" ) def __str__ ( cocky ): return self . question_text form Selection ( models . Model ): question = models . ForeignKey ( Question , on_delete = models . Pour ) choice_text = models . CharField ( max_length = 200 ) votes = models . IntegerField ( default = 0 ) def __str__ ( self ): return cocky . choice_text Note
Except the added "slug" field, Question and Option are copied from Django tutorial part ane.
Notation
Please also pay attention to 'choice' sheet. At that place exists an capricious column: "Noise", which exists to show instance skipping column characteristic using mapdicts later.
Please visit this link http://localhost:8000/polls/import/, you shall come across this upload form:
Please and then select sample-data.xls and upload. And you get the following excel-alike tabular array in response to confirm all were imported.
Note
pyexcel-handsontable forth with pyexcel v0.v.0 brings excel-alie table rendering feature. Permit me explain how this view is washed a few paragraphs afterwards.
Then visit the admin page http://localhost:8000/admin/polls/question, you lot shall see questions accept been populated:
Note
The admin user credentials are: user name: admin, password: admin
And choices also:
Yous may employ admin interface to delete all those objects and try once again.
Now please open polls/views.py and focus on this part of code:
def import_data ( request ): if request . method == "Postal service" : form = UploadFileForm ( request . POST , asking . FILES ) def choice_func ( row ): q = Question . objects . filter ( slug = row [ 0 ])[ 0 ] row [ 0 ] = q return row if course . is_valid (): request . FILES [ "file" ] . save_book_to_database ( models = [ Question , Selection ], initializers = [ None , choice_func ], mapdicts = [ [ "question_text" , "pub_date" , "slug" ], { "Question" : "question" , "Choice" : "choice_text" , "Votes" : "votes" }, ], ) return redirect ( "handson_view" ) else : render HttpResponseBadRequest () The star is save_book_to_database() . The parameter models should be a list of django models. initializers is a listing of initialization functions for each model. In the example, nosotros do not take init office for Question so 'None' is given and choice_func is given to Choice. mapdicts is a list of column names for each model and the member of the mapdicts tin can be a dictionary:
{ "Question Text" : "question_text" , "Publish Date" : "pub_date" , "Unique Identifier" : "slug" } As a dictionary, it tin can exist used to skip columns in the incoming sheets. For example, 'Noise' column is skipped considering it was not mentioned in the mapdict.
The custom initialization role is needed when the data from the excel sheet needs translation before data import. For example, Choice has a strange key to Question. When choice data are to be imported, "Question" column needs to be translated to a question case. In our instance, "Question" column in "Canvass ii" contains the values appeared in "Unique Identifier" cavalcade in "Sheet 1".
Handle data export¶
This section shows how to export the information in your models as an excel file. After yous have completed the previous section, you lot can visit http://localhost:8000/polls/export/volume and you shall go a file download dialog:
Delight save and open up it. You shall run across these data in your window:
At present let's examine the lawmaking behind this in polls/views.py:
def export_data ( request , atype ): if atype == "canvas" : return excel . make_response_from_a_table ( Question , "xls" , file_name = "canvass" ) elif atype == "book" : return excel . make_response_from_tables ( [ Question , Choice ], "xls" , file_name = "book" make_response_from_tables() does all what is needed: read out the data, convert them into xls and requite it the browser. And what you need to do is to give a listing of models to be exported and a file type. Every bit y'all accept noticed, you lot can visit http://localhost:8000/polls/export/canvas and will get Question exported as a unmarried sheet file.
Render an excel-akin html in a browser¶
In previous section, you have seen the rendering of the excel-akin table. First of all, the credits goes to handsontable developers. pyexcel-handsontable equally renderer plugin to pyexcel v0.five.0 bring it to pyexcel developers.
Hither is how it is done. Simply put in 'handsontable.html' instead of 'xls' as file blazon.
return excel . make_response_from_tables ( [ Question , Pick ], "handsontable.html" ) It is understood that you will desire to embed information technology into your django templates. Here are the sample embedding lawmaking:
def embed_handson_table ( asking ): """ Renders ii tabular array in a handsontable """ content = excel . pe . save_book_as ( models = [ Question , Selection ], dest_file_type = "handsontable.html" , dest_embed = True , ) content . seek ( 0 ) render render ( request , "custom-handson-table.html" , { "handsontable_content" : content . read ()}, ) def embed_handson_table_from_a_single_table ( request ): """ Renders one table in a handsontable """ content = excel . pe . save_as ( model = Question , dest_file_type = "handsontable.html" , dest_embed = True ) content . seek ( 0 ) return render ( request , "custom-handson-table.html" , { "handsontable_content" : content . read ()}, ) Those views can exist accessed as http://localhost:8000/polls/embedded_handson_view/ and http://localhost:8000/polls/embedded_handson_view_single/.
How to import one canvass instead of multi-sheet book¶
Previous case shows how to import a multi-sheet book. However, what exactly is needed to import just one sheet instead? Before you proceed, please empty question and choice data using django admin.
Let's visit this url beginning http://localhost:8000/polls/imports_sheet/, where y'all run across a like file upload form. This time please cull sample-sheet.xls instead. And then await at django admin and see if the question data have been imported or non.
Now let's wait at the code:
def import_sheet ( request ): if request . method == "Mail" : form = UploadFileForm ( request . POST , request . FILES ) if class . is_valid (): asking . FILES [ "file" ] . save_to_database ( name_columns_by_row = 2 , model = Question , mapdict = [ "question_text" , "pub_date" , "slug" ], ) return HttpResponse ( "OK" ) else : Because it is a single sail, the office to call is save_to_database() where you specify a model and its mapping dictionary.
Take you noticed the extra parameter 'name_columns_by_row'? Why is this needed? Well, commonly you volition non need that if y'all have column names in the first row. In this example, the cavalcade names appears in the 2nd row. Please open sample-sheet.xls and accept a look. The straight answer is because the column names in the data appears in the 2nd row of the data matrix.
Note
If you accept imported before excel canvas "sample-data.xls", you will get the following warning in your panel output:
Warning: Bulk insertion got below exception. Trying to do it one by one slowly. column slug is not unique <- reason I row is ignored <- action column slug is non unique What is your favourite programming linguistic communication? Ane row is ignored column slug is not unique What is your favourite IDE? This is considering question data take been imported before. Django is raising IntegrityError. For more details please read this function of code in pyexcel-io, and django-excel result ii
In order to remove those warnings, what you can practise is to empty all data using django admin and redo this unmarried canvas import again.
What to practise if import data overlaps existing data in the database¶
With new version pyexcel-io v0.1.0, you could provide the row initialization function that returns None in guild to skip a row in your import data. Inside the initialization function, you could also do database update. As long every bit it returns None, django-excel will try to do bulk create the import data.
Handle custom information export¶
Information technology is also quite common to download a portion of the data in a database table, for example the result of a search query. With version 0.0.2, you lot tin can pass on a query sets to to make_response_from_query_sets() and generate an excel canvass from information technology:
def export_data ( request , atype ): [ Question , Choice ], "xls" , file_name = "book" ) elif atype == "custom" : question = Question . objects . get ( slug = "ide" ) query_sets = Choice . objects . filter ( question = question ) column_names = [ "choice_text" , "id" , "votes" ] render excel . make_response_from_query_sets ( query_sets , column_names , "xls" , file_name = "custom" ) else : Yous tin visit http://localhost:8000/polls/export/custom and will go the query set exported every bit a single sheet file as:
Visualize your data¶
Let'due south get to the admin page and update some votes for the choices.
In my case, I have updated all of them and have gotten something like this:
Now, let'due south look at the survey result(http://localhost:8000/polls/survey_result/) for "What'due south your favorite IDE?":
pyexcel-pygal provide you the common data visualization capability to show your data intuitively. Here is the code to achieve that:
query_sets = Choice . objects . filter ( question = question ) column_names = [ "choice_text" , "votes" ] # Obtain a pyexcel sail from the query sets sheet = excel . pe . get_sheet ( query_sets = query_sets , column_names = column_names ) sheet . name_columns_by_row ( 0 ) sheet . cavalcade . format ( "votes" , int ) # Transform the sheet into an svg chart svg = excel . pe . save_as ( assortment = [ sheet . column [ "choice_text" ], canvass . column [ "votes" ]], dest_file_type = "svg" , dest_chart_type = "pie" , dest_title = question . question_text , dest_width = 600 , dest_height = 400 , ) render render ( request , "survey_result.html" , dict ( svg = svg . read ())) def import_sheet_using_isave_to_database ( request ): if request . method == "POST" : course = UploadFileForm ( request . POST , request . FILES ) All supported data types¶
The example application likes to have array simply it is not merely about arrays. Here is table of functions for all supported data types:
| information structure | from file to data structures | from information structures to response |
|---|---|---|
| dict | get_dict() | make_response_from_dict() |
| records | get_records() | make_response_from_records() |
| a list of lists | get_array() | make_response_from_array() |
| dict of a list of lists | get_book_dict() | make_response_from_book_dict() |
pyexcel.Sail | get_sheet() | make_response() |
pyexcel.Book | get_book() | make_response() |
| database table | save_to_database() isave_to_database() | make_response_from_a_table() |
| a list of database tables | save_book_to_database() isave_book_to_database() | make_response_from_tables() |
| a database query sets | make_response_from_query_sets() | |
| a generator for records | iget_records() | |
| a generator of lists | iget_array() |
Meet more than examples of the data structures in pyexcel documentation
API Reference¶
django-excel attaches pyexcel functions to InMemoryUploadedFile and TemporaryUploadedFile. Hence, the post-obit functions are available for the uploaded files, eastward.grand. request.FILES['your_uploaded_file'].
-
django_excel.ExcelMixin.get_sheet( sheet_name=None, **keywords )¶ -
Parameters: - sheet_name – For an excel book, there could be multiple sheets. If it is left unspecified, the sheet at index 0 is loaded. For 'csv', 'tsv' file, sheet_name should be None anyway.
- keywords – additional keywords to
pyexcel.get_sheet()
Returns: A sail object
-
django_excel.ExcelMixin.get_array( sheet_name=None, **keywords )¶ -
Parameters: - sheet_name – same equally
get_sheet() - keywords – boosted keywords to
pyexcel.get_array()
Returns: a two dimensional array, a listing of lists
- sheet_name – same equally
-
django_excel.ExcelMixin.iget_array( sheet_name=None, **keywords )¶ -
Parameters: - sheet_name – same equally
get_sheet() - keywords – additional keywords to
pyexcel.iget_array()
Returns: a generator for a two dimensional array, a list of lists
- sheet_name – same equally
-
django_excel.ExcelMixin.get_dict( sheet_name=None, name_columns_by_row=0, **keywords )¶ -
Parameters: - sheet_name – same as
get_sheet() - name_columns_by_row – uses the first row of the canvas to exist column headers past default.
- keywords – boosted keywords to
pyexcel.get_dict()
Returns: a dictionary of the file content
- sheet_name – same as
-
django_excel.ExcelMixin.get_records( sheet_name=None, name_columns_by_row=0, **keywords )¶ -
Parameters: - sheet_name – same as
get_sheet() - name_columns_by_row – uses the first row of the sheet to be record field names by default.
- keywords – boosted keywords to
pyexcel.get_records()
Returns: a list of lexicon of the file content
- sheet_name – same as
-
django_excel.ExcelMixin.iget_records( sheet_name=None, name_columns_by_row=0, **keywords )¶ -
Parameters: - sheet_name – same equally
get_sheet() - name_columns_by_row – uses the first row of the canvas to be record field names by default.
- keywords – additional keywords to
pyexcel.iget_records()
Returns: a generator for a list of lexicon of the file content
- sheet_name – same equally
-
django_excel.ExcelMixin.get_book( **keywords )¶ -
Parameters: keywords – additional keywords to pyexcel.get_book()Returns: a 2 dimensional array, a listing of lists
-
django_excel.ExcelMixin.get_book_dict( **keywords )¶ -
Parameters: keywords – additional keywords to pyexcel.get_book_dict()Returns: a 2 dimensional array, a list of lists
-
django_excel.ExcelMixin.save_to_database( model=None, initializer=None, mapdict=None, **keywords )¶ -
Parameters: - model – a django model
- initializer – a custom table initialization part if y'all have one
- mapdict – the explicit table column names if your excel data practise non take the exact column names
- keywords – additional keywords to
pyexcel.Sail.save_to_django_model()
-
django_excel.ExcelMixin.isave_to_database( model=None, initializer=None, mapdict=None, **keywords )¶ -
similar to
save_to_database(). Simply it requires less memory.This requires column names must be at the start row.
-
django_excel.ExcelMixin.save_book_to_database( models=None, initializers=None, mapdicts=None, **keywords )¶ -
Parameters: - models – a listing of django models
- initializers – a list of model initialization functions.
- mapdicts – a list of explicit tabular array column names if your excel data sheets do not have the exact column names
- keywords – boosted keywords to
pyexcel.Book.save_to_django_models()
-
django_excel.ExcelMixin.isave_book_to_database( models=None, initializers=None, mapdicts=None, **keywords )¶ -
like to
save_book_to_database(). But it requires less retentivity.This requires column names must exist at the starting time row in each sheets.
-
django_excel.ExcelMixin.free_resources( )¶ -
It should be chosen later on iget_array and iget_records were used
Response methods¶
-
django_excel.make_response( pyexcel_instance, file_type, status=200 )¶ -
Parameters: - pyexcel_instance –
pyexcel.Canvasorpyexcel.Book - file_type –
i of the following strings:
- 'csv'
- 'tsv'
- 'csvz'
- 'tsvz'
- 'xls'
- 'xlsx'
- 'xlsm'
- 'ods'
- status – unless a dissimilar condition is to be returned.
- pyexcel_instance –
-
django_excel.make_response_from_array( assortment, file_type, status=200 )¶ -
Parameters: - array – a list of lists
- file_type – same every bit
make_response() - status – same as
make_response()
-
django_excel.make_response_from_dict( dict, file_type, status=200 )¶ -
Parameters: - dict – a dictionary of lists
- file_type – same as
make_response() - condition – aforementioned as
make_response()
-
django_excel.make_response_from_records( records, file_type, status=200 )¶ -
Parameters: - records – a list of dictionaries
- file_type – same every bit
make_response() - condition – same as
make_response()
-
django_excel.make_response_from_book_dict( book_dict, file_type, status=200 )¶ -
Parameters: - book_dict – a dictionary of 2 dimensional arrays
- file_type – same every bit
make_response() - condition – same every bit
make_response()
-
django_excel.make_response_from_a_table( model, file_type status=200 )¶ -
Produce a single sheet Excel book of *file_type* -
Parameters: - model – a Django model
- file_type – aforementioned as
make_response() - status – aforementioned every bit
make_response()
-
django_excel.make_response_from_query_sets( query_sets, column_names, file_type condition=200 )¶ -
Produce a single sheet Excel book of file_type from your custom database queries
Parameters: - query_sets – a query set
- column_names – a nominated column names. It could not be None, otherwise no information is returned.
- file_type – same equally
make_response() - status – same every bit
make_response()
-
django_excel.make_response_from_tables( models, file_type status=200 )¶ -
Produce a multiple sheet Excel volume of file_type. It becomes the aforementioned as
make_response_from_a_table()if you pass tables with an array that has a single tableParameters: - models – a list of Django models
- file_type – same as
make_response() - status – aforementioned as
make_response()
Django How to Read From a Csv Into a Table
Source: http://django.pyexcel.org/en/latest/