pyexcel-webwares
Urgent Help: Trying to Import data from two sheets(xlsx) into two different models(Foreign Key Related) - Not able to lead the Data
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.
I think it could be simply the case. Try lower case in your data, i.e.: Supplier_x -> supplier_x
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
https://github.com/pyexcel-webwares/django-excel/issues/62#issuecomment-630912706
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
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.