Django-importing excel file into django models

Question

I need user to upload an excel file via the form provided and i need to process that uploaded excel file to save the data in my model.

models.py

class Patient_Record(models.Model):

    Patient_id=models.IntegerField(unique=True)
    Patient_sex=models.CharField(max_length=1,choices=Gender)
    Patient_name=models.CharField(max_length=20)
    Patient_sugar=models.DecimalField(decimal_places=3,max_digits=6)
    Patient_chlorestrol=models.DecimalField(decimal_places=3,max_digits=6)
    Patient_iron=models.DecimalField(decimal_places=3,max_digits=6)
    Patient_haemoglobin=models.DecimalField(decimal_places=3,max_digits=6)

    def __str__(self):
        return self.pat_name

I have simple form to upload the file.

<form method="POST" class="post-form" action="../Uploaded_file" enctype="multipart/form-data" name="myfile">{% csrf_token %}
                    {{ upload_form.as_p }}
<input type="submit" value="Upload" name="Upload" class="btn  btn-primary">
</form>

Can someone help me with the code to parse a excel file using POST to this model.

I tried using many different methods but couldn't succeed in it.

trigo · Answer 1 · 2018-02-13T09:22:32.770

0

You require xlrd library to extract the data from excel sheet. Check out following link https://pypi.python.org/pypi/xlrd

This is the example snippet. You may modify it according to your code.

In Forms.py create a model form with following field.

class UpdateDetailsForm(forms.Form):
 excel_file = forms.FileField(label='Excel File',required=False,validators=[validate_file_extension])

Then in corresponding views.py

def update_details(request):
  message=''
 if request.method == 'POST':
  form = UpdateDetailsForm(request.POST,request.FILES)
  if form.is_valid():
   #import your django model here like from django.appname.models import model_name
   excel_file = request.FILES['excel_file']
   i=0
   try:
    import os
    import tempfile
    import xlrd
    fd, tmp = tempfile.mkstemp()
    with os.fdopen(fd, 'w') as out:
      out.write(excel_file.read())
    book=xlrd.open_workbook(fd)
    sh = book.sheet_by_index(0)          
    for rx in range(1,sh.nrows):
        obj=Patient_Record(Patient_id=str(sh.row(rx)[0].value),Patient_sex=str(sh.row(rx)[1].value)) # similiary populate according to your  model
        obj.save()
        i=i+1;
   finally:
    os.unlink(tmp)
  else:
    message='Invalid Entries'
 else:
    form = UpdateDetailsForm()
 return render_to_response('admin/import_data.html', {'form':form,'message':message},context_instance=RequestContext(request))

edited Feb 13 '18 at 09:22

answered Feb 12 '18 at 18:55

trigo

387
1
7

What is UpdateDetailsForm ? – Vineet Bhikonde Feb 13 '18 at 05:55
It will be the class name in forms.py. Check the edited version. You can create the class Updatedetailsform in forms.py in your app as shown above and use it to upload the excel sheet. You then require xlrd library to extract data from the sheet. – trigo Feb 13 '18 at 07:02
thanks i got that. But why are you converting everything to string ? Since Patient_id is a integer wouldn't it raise an error ? – Vineet Bhikonde Feb 13 '18 at 07:37
got a typeError write() argument must be str, not bytes – Vineet Bhikonde Feb 13 '18 at 08:29
That is just a sample code . I have just used a small portion from my working code. You will have to update depending on your field type. – trigo Feb 13 '18 at 08:42
thanks . Could you tell me the solution to typeError write() argument must be str, not bytes . when i type converted it o str i shows a syntax error. – Vineet Bhikonde Feb 13 '18 at 09:05
What python version are you using ? Mine in python 2.7.x . If you are using python 3.x you will have to modiy the line containing code """ os.fdopen(fd, 'w') as out: out.write(excel_file.read()) book=xlrd.open_workbook(fd) """ check out the following link : https://stackoverflow.com/questions/38524770/write-argument-must-be-str-not-bytes – trigo Feb 13 '18 at 09:25

score 0 · Answer 2 · answered Jun 25 '18 at 14:27

In Python 3.6 and Django 2.0 the following rises the error Exception Type: PermissionError Exception Value:[WinError 32]

def upload_file(request):
    message=''
    if request.method == 'POST':
        form = FormUploadFileData(request.POST, request.FILES)
        if form.is_valid():
            from projects.models import Project
            excel_file = request.FILES['excel_file']
            try:
                import os
                import tempfile
                import xlrd
                fd, tmp = tempfile.mkstemp() # create two temporary file
                with os.open(fd, 'wb') as out: # create new file objects
                    out.write(excel_file.read())
                book = xlrd.open_workbook(fd)
                sheet = book.sheet_by_index(0)
                obj=Project(
                    project_title = sheet.cell_value(rowx=1, colx=1),
                    project_sector = sheet.cell_value(rowx=2, colx=1),
                    project_location = sheet.cell_value(rowx=3, colx=1),
                    project_tot_cost = sheet.cell_value(rowx=4, colx=1),
                    project_descr = sheet.cell_value(rowx=5, colx=1),
                    project_fund = sheet.cell_value(rowx=6, colx=1),
                    project_cofin = sheet.cell_value(rowx=7, colx=1),
                    project_applicant = sheet.cell_value(rowx=8, colx=1)
                )
                obj.save()
            finally:
                os.unlink(tmp)
        else:
            message='Invalid Entries'
    else:
        form = FormUploadFileData()
    return render(request,'upload.html', {'form':form,'message':message})

score 0 · Answer 3 · edited Jun 26 '18 at 13:22

There is an issue in the solution proposed:

book=xlrd.open_workbook(fd) should be book=xlrd.open_workbook(tmp) as open_workhook search for a file path

[Worked in Python 3.6, Django 2.0]

excel_file = request.FILES['excel_file']
        import os
        import tempfile
        import xlrd
        fd, path = tempfile.mkstemp() #  mkstemp returns a tuple: an integer (index) called file descriptor used by OS to refer to a file and its path
        try:
            with os.fdopen(fd, 'wb') as tmp:
                tmp.write(excel_file.read())
            book = xlrd.open_workbook(path)
            sheet = book.sheet_by_index(0)
            obj=Project(
                project_title = sheet.cell_value(rowx=1, colx=1),
                project_sector = sheet.cell_value(rowx=2, colx=1),
                project_location = sheet.cell_value(rowx=3, colx=1),
                project_tot_cost = sheet.cell_value(rowx=4, colx=1),
                project_descr = sheet.cell_value(rowx=5, colx=1),
                project_fund = sheet.cell_value(rowx=6, colx=1),
                project_cofin = sheet.cell_value(rowx=7, colx=1),
                project_applicant = sheet.cell_value(rowx=8, colx=1)
            )
            obj.save()
        finally:
            os.remove(path)

Django-importing excel file into django models

3 Answers3