Urgent Help: Trying to Import data from two sheets(xlsx) into two different models(Foreign Key Related) - Not able to lead the Data

[REMALAMANOJ]

Kindly Suggest, where exactly i am doing the mistake.

Best Regards
Manoj R

Details:

Models Used:

		class Supplier(models.Model):
			Supplier_Number = models.IntegerField(unique=True)
			Supplier_Name = models.CharField(max_length=150)
			Supplier_Contracting_Entity =  models.CharField(max_length=150)
			Applicable_Brands =  models.CharField(max_length=150)
			Currency_To_Be_Paid =  models.CharField(max_length=3)
			Other_Remarks =  models.CharField(max_length=150)
			Term =  models.CharField(max_length=15)
			Notes = models.CharField(max_length=150)
			Fee_Calculation_Basis = models.CharField(max_length=25)

			def __str__(self):
				return self.Supplier_Name

		class Tax_Vat(models.Model):
			Supplier = models.ForeignKey(Supplier, on_delete=models.CASCADE)
			#Supplier_Number = models.IntegerField()
			#-- Above will store the Supplier ID, which is generated automatically
			COUNTRY =models.CharField(max_length=25)
			DEDUCTION_TYPE =models.CharField(max_length=5)
			NGR_FLAG =models.CharField(max_length=5)
			TAX_VAT_RATE = models.FloatField()

			def __str__(self):
				return self.COUNTRY

Views.py:

class UploadFileForm(forms.Form):
    file = forms.FileField()

def sup_import(request):
    if request.method == "POST":
        form = UploadFileForm(request.POST,request.FILES)

        def tax_func(row):
            q = Supplier.objects.filter(Supplier_Number=row[0])[0]
            row[0] = q
            return row
        #if form.is_valid():
        request.FILES['myfile'].save_book_to_database(
                models=[Supplier, Tax_Vat],
                initializers=[None, tax_func],
                mapdicts=[
                            {   "Supplier_Number": "Supplier_Number",
                                "Supplier_Name": "Supplier_Name",
                                "Supplier_Contracting_Entity":"Supplier_Contracting_Entity",
                                "Applicable_Brands":"Applicable_Brands",
                                "Currency_To_Be_Paid":"Currency_To_Be_Paid",
                                "Other_Remarks":"Other_Remarks",
                                "Term":"Term",
                                "Notes":"Notes",
                                "Fee_Calculation_Basis":"Fee_Calculation_Basis"
                            },
                            {
                                "Supplier_Number":"Supplier",
                                "COUNTRY":"COUNTRY",
                                "DEDUCTION_TYPE":"DEDUCTION_TYPE",
                                "NGR_FLAG":"NGR_FLAG",
                                "TAX_VAT_RATE":"TAX_VAT_RATE"

                            }
                        ]
            )
        return redirect('Royalty_upload.html')
       # else:
            #return HttpResponseBadRequest()
    else:
        form = UploadFileForm()
    return render(
        request,
        'Royalty_upload.html',
        {
            'form': form,
            'title': 'Import excel data into database example',
            'header': 'Please upload Royalty_Input_Import_Data_All.xlsx:'
        })

Error Details:

Exception at /royalty_supplier
				Sheet: Supplier does not match any given models.Please be aware of case sensitivity.
				Request Method: | POST
				Request URL: | http://127.0.0.1:8000/royalty_supplier
				Django Version: | 3.0.3
				Exception Type: | Exception
				Exception Value: | Sheet: Supplier does not match any given models.Please be aware of case sensitivity
				Exception Location: | C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel_io\database\importers\django.py in create_sheet, line 85

				Environment:

				Request Method: POST
				Request URL: http://127.0.0.1:8000/royalty_supplier

				Django Version: 3.0.3
				Python Version: 3.7.1
				Installed Applications:
				['django.contrib.admin',
				'django.contrib.auth',
				'django.contrib.contenttypes',
				'django.contrib.sessions',
				'django.contrib.messages',
				'django.contrib.staticfiles',
				'import_export',
				'RECON.apps.ReconConfig']
				Installed Middleware:
				['django.middleware.security.SecurityMiddleware',
				'django.contrib.sessions.middleware.SessionMiddleware',
				'django.middleware.common.CommonMiddleware',
				'django.middleware.csrf.CsrfViewMiddleware',
				'django.contrib.auth.middleware.AuthenticationMiddleware',
				'django.contrib.messages.middleware.MessageMiddleware',
				'django.middleware.clickjacking.XFrameOptionsMiddleware']

				Traceback (most recent call last):
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\core\handlers\exception.py", line 34, in inner
				response = get_response(request)
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\core\handlers\base.py", line 115, in _get_response
				response = self.process_exception_by_middleware(e, request)
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\core\handlers\base.py", line 113, in get_response
				response = wrapped_callback(request, *callback_args, **callback_kwargs)
				File "C:\Users\manoj.remala\PycharmProjects\MANOJ_IVY\IREC_WEB\RECON\views.py", line 121, in sup_import
				"TAX_VAT_RATE":"TAX_VAT_RATE"
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django_excel_init.py", line 63, in save_book_to_database
				pe.save_book_as(**params)
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel\core.py", line 112, in save_book_as
				return sources.save_book(book, **dest_keywords)
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel\internal\core.py", line 56, in save_book
				return _save_any(a_source, book)
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel\internal\core.py", line 60, in _save_any
				a_source.write_data(instance)
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel\plugins\sources\db_sources.py", line 111, in write_data
				**self._keywords
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel\plugins\renderers\django.py", line 75, in render_book_to_stream
				**keywords
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel_io\io.py", line 131, in save_data
				**keywords
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel_io\io.py", line 148, in store_data
				writer.write(data)
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel_io\book.py", line 218, in write
				sheet_writer = self.create_sheet(sheet_name)
				File "C:\Users\manoj.remala\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pyexcel_io\database\importers\django.py", line 85, in create_sheet
				+ "Please be aware of case sensitivity."

				Exception Type: Exception at /royalty_supplier
				Exception Value: Sheet: Supplier does not match any given models.Please be aware of case sensitivity.

[REMALAMANOJ]

Please find the Input Sheets:

[chfw]

I think it could be simply the case. Try lower case in your data, i.e.: Supplier_x -> supplier_x

[REMALAMANOJ]

Hi Jaska,

Thanks for response.
But, I have used same Data Model for importing the Single sheet and used the same in 'views.py' and its worked fine, data got imported. If case is the problem this should cause error one to one importing.

Please suggest.

Will try the lower case also and update you.

Regards
Manoj R

[REMALAMANOJ]

#62 (comment)

Hi Jaska,

Modified the models as below with lower case.
Still getting the same result.

Could you please suggest.

class Supplier(models.Model):
sp_supplier_number = models.IntegerField(unique=True,default=100)
sp_supplier_name = models.CharField(max_length=150)
sp_supplier_contracting_entity = models.CharField(max_length=150)
sp_applicable_brands = models.CharField(max_length=150)
sp_currency_to_be_paid = models.CharField(max_length=3)
sp_other_remarks = models.CharField(max_length=150)
sp_term = models.CharField(max_length=15)
sp_notes = models.CharField(max_length=150)
sp_fee_calculation_basis = models.CharField(max_length=25)

def __str__(self):
    return self.sp_supplier_name

class Tax_Vat(models.Model):
tx_supplier = models.ForeignKey(Supplier, on_delete=models.CASCADE)
tx_country = models.CharField(max_length=25)
tx_deduction_type = models.CharField(max_length=5)
tx_ngr_flag = models.CharField(max_length=5)
tx_tax_vat_rate = models.FloatField()

def __str__(self):
    return self.tx_country

Thanks
Manoj R

[chfw]

Hi Manoj

I am sorry that I was half-minded in providing the assistance. Please rewind back to the original code.

After analysing your code and your intention, I think you fail to understand how django-excel handles foreign key. Please refer to the tutorial or the below code.

So you will have to link up Tax_Vat and Supplier:

class Tax_Vat(models.Model):
			Supplier = models.ForeignKey(Supplier, on_delete=models.CASCADE)

Here is the example where question links with its choices:

def import_data(request):
    if request.method == "POST":
        form = UploadFileForm(request.POST,
                              request.FILES)

        def choice_func(row):
            q = Question.objects.filter(slug=row[0])[0]
            row[0] = q
            return row
        if form.is_valid():
            request.FILES['file'].save_book_to_database(
                models=[Question, Choice],
                initializers=[None, choice_func],
                mapdicts=[
                    ['question_text', 'pub_date', 'slug'],
                    ['question', 'choice_text', 'votes']]
            )
            return redirect('handson_view')
        else:
            return HttpResponseBadRequest()
    else:

And just an observation, it seems your code sometimes use 8 spaces instead of 4. Please use 4 spaces or use python's 'black' tool.