Upload file into a model that contains foreignkeys

I have successfully created a template, view and path that allows users to upload data from a csv file directly into a model.

I'm experiencing issues however with a model that contains a foreignkey. I was expecting this to be a problem but am sure there's a workaround.

So I have a model called VendorItem:

class VendorItem(models.Model):
    sku = models.CharField("SKU", max_length=32, blank=True, null=True)
    vendor = models.ForeignKey(Vendor, on_delete=models.CASCADE, verbose_name="Vendor name", related_name="vendor") 
    vendor_sku = models.CharField("Vendor's SKU", max_length=32)  
    model = models.CharField("Model number", max_length=32, blank=True, null=True)
    description = models.CharField("Description", max_length=64, blank=True, null=True)  
    pq = models.DecimalField('Pack quantity', max_digits=7, decimal_places=2, default=1)                                     
    price = models.DecimalField("Price", max_digits=10, decimal_places=2)  
    upc = models.IntegerField("UPC", blank=True, null=True) # 12-digit numeric barcode
    ian = models.CharField("IAN", max_length=13, blank=True, null=True)    # 13-digit alphanumeric international barcode
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default=1)
    # Model metadata
    class Meta:
        unique_together = ["sku", "vendor"]
        unique_together = ["sku", "vendor_sku"]
        verbose_name_plural = "Vendor items"
    # Display below in admin 
    def __str__(self): 
       return f"{self.vendor_sku}" 

The vendor field is retrieved from the Vendor model (which is from another app might I add):

class Vendor(models.Model):
    number = models.IntegerField("Vendor number", unique=True, blank=True, null=True)
    name = models.CharField("Vendor name", max_length=64, unique=True)   
    region = models.CharField("Vendor region", max_length=64, blank=True, null=True)   
    status = models.CharField(max_length=20, choices=VENDORSTATUS_CHOICES, default=1)      
    # Display below in admin
    def __str__(self):
        return f"{self.name}" 

In 'forms.py', this is my upload form:

class UploadCsvForm(forms.Form):
    file = forms.FileField(validators=[FileExtensionValidator(allowed_extensions=['csv'])])
    # File type validation
    def clean_file(self):
        file = self.cleaned_data.get("file")
        content_type = file.content_type
        # If file type is not csv
        if content_type != 'text/csv':
            raise forms.ValidationError("File type not supported. Please upload a CSV file.")
        return file

In my 'views.py', this is my view:

def upload_items(request):
    submitted = False       # Form is not initially posted
    # If POST request
    if request.method == 'POST':
        # Pass the form
        form = UploadCsvForm(request.POST, request.FILES)
        # If form data is valid
        if form.is_valid():
            # Save the uploaded file to a variable
            file = request.FILES['file']
            # Read the csv file using pandas
            df = pd.read_csv(file)
            # Create a temporary list to store the instances
            items_to_create = []
            # Iterate over each row
            for index, row in df.iterrows():
                sku = row['sku']
                vendor = row['vendor']
                vendor_sku = row['vendor_sku']
                model = row['model']
                description = row['description']
                pq = row['pq']
                price = row['price']
                upc = row['upc']
                ian = row['ian']
                status = row['status']
                # Append this new instance of the model to the temporary list
                items_to_create.append(VendorItem(sku=sku, vendor=vendor, vendor_sku=vendor_sku, model=model, description=description, pq=pq, price=price, upc=upc, ian=ian, status=status))
            # Try to bulk upload all instances to the database
            # For instances that violated field constraints
            except IntegrityError:
                # Loop through the temporary list
                for item in items_to_create:
                    # If the same vendor.number is found in the database, update its fields specified in the defaults brackets below
                    VendorItem.objects.update_or_create(sku=item.sku, defaults={'vendor': item.vendor, 'vendor_sku': item.vendor_sku, 'model': item.model, 'description': item.description, 'pq': item.pq, 'price': item.price, 'upc': item.upc, 'ian': item.ian, 'status': item.status})
            # Redirect to the upload_items page and set submitted=True
            return HttpResponseRedirect('upload_items?submitted=True')
        # If form isn't valid
            form = UploadCsvForm()
            error_message = 'Invalid file type. Please upload a CSV file.'
            return render(request, 'vendor_items/upload_items.html', {
                'form': form,
                'error_message': error_message
    # If GET request
        # Initialise the form
        form = UploadCsvForm()
        if 'submitted' in request.GET:
            submitted = True
    # Render the template 
    return render(request, 'vendor_items/upload_items.html', {
        'form': form,
        'submitted': submitted

Finally, this is my template:

{% extends 'vendor_items/layout.html' %} <!-- Template inheritance-->

{% block body %}
        <h1>Upload vendor items</h1>
        <!-- If upload was successful (POST request)-->
        {% if submitted %}
            The list of vendor items was successfully updated.
        <!-- If file type was incorrect (not csv)-->
        {% elif error_message %}
            <h2>Upload items from a csv file</h2>
            <p>{{ error_message }}</p>
            <form action="{% url 'upload-items' %}" method="POST" enctype="multipart/form-data">
                {% csrf_token %}

                {{ form.file }}

                <input type="submit" value="Upload">
        <!-- If just arriving at page (GET request)-->
        {% else %}
            <h2>Upload items from a csv file</h2>
            <form action="{% url 'upload-items' %}" method="POST" enctype="multipart/form-data">
                {% csrf_token %}

                {{ form.file }}

                <input type="submit" value="Upload">
        {% endif %}
        <!-- These are always displayed no matter the request-->
        <a href="{% url 'vendor-items' %}">Vendor items</a>
        <a href="{% url 'index' %}">Home</a>
{% endblock %}

My code is correct and it works really well, except if foreignkeys are involved.

The csv file that I'm uploading contains the 'name' field of the 'Vendor' model. I want the csv upload to do the following:

1- To work successfully by allocating each VendorItem to an existing Vendor using the provided 'vendor' name in the csv file (and thus continue the VendorItem upload)

And if you're a Django wizard and are kind enough to help me a step further:

2- To work successfully by automatically creating a new Vendor in the Vendor model with only the provided name (if no Vendor exists with that name yet)

Thanks for any help offered.

This is the error I'm getting:

Error message

Since the foreign key element should be an integer (id) and not a string, I recommend that you find the Foreign Key (id) that matches the name (string) component ahead of compiling the CSV and then substituting the string name with the foreign key id in the CSV.

Back to Top