Import an Excel File into Django Models

Sooner or later every Django application gets the request: "can we upload the product list from Excel instead of typing it in?" The first version reads the rows and calls Product.objects.create in a loop, and it works until the spreadsheet contains a price with a comma, an empty name, a negative stock count or the same SKU twice — and then it fails half-way, leaving half the rows imported. A good import validates every row with the model's own rules before touching the database, reports problems by the row numbers the uploader sees in Excel, and writes all or nothing. This page, part of Serving Excel Files from Python Web Apps, builds that import against a real Django app.

Prerequisites

pip install django pandas openpyxl

The examples use a small Django app, catalog, with a Category model and a Product model — SKU (unique), name, category (foreign key), price (decimal) and stock (positive integer) — on SQLite. products_upload.xlsx is the uploaded file: eight rows, with a price typed as 129,00, a blank name, a negative stock figure and one SKU listed twice.

import django
from django.core.management import call_command

django.setup()
call_command("migrate", run_syncdb=True, verbosity=0)
from catalog.models import Category, Product
print([f.name for f in Product._meta.get_fields()])

Step 1 — Read every cell as text

Let Django decide what a valid value is, not pandas. Reading with dtype=str and keep_default_na=False delivers each cell as the text the uploader typed, blanks as empty strings:

import pandas as pd

df = pd.read_excel("products_upload.xlsx", dtype=str, keep_default_na=False)
df = df.apply(lambda col: col.str.strip())
df.insert(0, "excel_row", df.index + 2)          # header is row 1
print(df.to_string(index=False))

If pandas guessed the types, 129,00 would make the whole price column text anyway while the other prices became floats, and an empty cell would become NaN, which Django treats as a value rather than as "blank". The excel_row column records each record's row number in the spreadsheet — the index plus two, for the header and because Excel counts from one — so every error message can point at the row the uploader will look at.

Step 2 — Validate each row with the model's rules

Model.full_clean() runs the same validation a Django form or the admin would: field types, lengths, blank, choices, validators like PositiveIntegerField's minimum. Build an unsaved instance per row and collect the errors instead of stopping at the first:

from django.core.exceptions import ValidationError

def validate_rows(frame):
    valid, errors = [], []
    for row in frame.itertuples(index=False):
        product = Product(sku=row.SKU, name=row.Name, price=row.Price, stock=row.Stock)
        try:
            product.full_clean(exclude=["category"], validate_unique=False)
            valid.append((row.excel_row, row.Category, product))
        except ValidationError as err:
            for field, messages in err.message_dict.items():
                errors.append({"row": row.excel_row, "sku": row.SKU, "field": field, "problem": "; ".join(messages)})
    return valid, errors

valid, errors = validate_rows(df)
print(len(valid), "valid rows")
for e in errors:
    print(e)

Three rows fail with Django's own messages: the price 129,00 is not a decimal number, the name on row 6 cannot be blank, and the stock on row 7 must be at least 0. exclude=["category"] skips the foreign key, which is resolved in a later step, and validate_unique=False skips the per-row database query for the SKU — uniqueness is handled for the whole file at once next. Validation that lives on the model is reused here for free; a custom clean() method on the model runs too.

Step 3 — Reject duplicate keys within the file

The unique constraint on sku protects the database, but a SKU listed twice in the upload is ambiguous — which row is right? Find those before writing and treat them as errors:

dupes = df[df.duplicated("SKU", keep=False)]
for row in dupes.itertuples(index=False):
    errors.append({"row": row.excel_row, "sku": row.SKU, "field": "sku",
                   "problem": f"SKU appears more than once (rows {', '.join(map(str, dupes.loc[dupes['SKU'] == row.SKU, 'excel_row']))})"})
print(sorted({e["row"] for e in errors}))

Both occurrences of SKU-110 are reported, each naming the other's row, because the uploader has to choose between them. Guessing — "the last one wins" — silently discards data someone typed.

Step 4 — All or nothing

If any row failed, import nothing and send the errors back; a half-imported product list is worse than none. When everything is valid, create missing categories and upsert the products in one transaction:

from django.db import transaction

def import_products(valid):
    with transaction.atomic():
        names = {cat for _, cat, _ in valid}
        categories = {c.name: c for c in Category.objects.filter(name__in=names)}
        for name in names - set(categories):
            categories[name] = Category.objects.create(name=name)
        products = []
        for _, cat, product in valid:
            product.category = categories[cat]
            products.append(product)
        Product.objects.bulk_create(products, update_conflicts=True, unique_fields=["sku"],
                                    update_fields=["name", "category", "price", "stock"])
    return len(products)

if errors:
    print(f"{len({e['row'] for e in errors})} rows have problems — nothing imported")
print("products in database:", Product.objects.count())

transaction.atomic() makes the whole import one unit: if anything inside raises, every change is rolled back. bulk_create with update_conflicts=True is an upsert — insert new SKUs, update the named fields of existing ones — in a single statement on databases that support it, including PostgreSQL, SQLite and MySQL/MariaDB. Categories are looked up once for all names and created only if missing, instead of one query per row.

Step 5 — Import the corrected file

The uploader fixes the five rows — the decimal point, the name, the stock, and keeps one of the two SKU-110 rows — and uploads again. This time every row is valid and the import runs:

fixed = df[df["excel_row"] != 3].copy()
fixed.loc[fixed["SKU"] == "SKU-210", "Price"] = "129.00"
fixed.loc[fixed["SKU"] == "SKU-300", "Name"] = "Brass floor lamp"
fixed.loc[fixed["SKU"] == "SKU-310", "Stock"] = "2"
valid, errors = validate_rows(fixed)
assert not errors and not fixed["SKU"].duplicated().any()
print(import_products(valid), "products imported")
print(list(Product.objects.order_by("sku").values_list("sku", "category__name", "price", "stock")))

again = fixed.assign(Stock="0")
print(import_products(validate_rows(again)[0]), "rows upserted a second time;", Product.objects.count(), "products,",
      Product.objects.filter(stock=0).count(), "now with stock 0")

Seven products are created along with the four categories. Importing the same file again with different stock figures updates the existing products instead of failing on the unique constraint or creating duplicates — the count stays at seven. That is what makes the import safe to run every week with the latest spreadsheet. In production, wrap the same functions in a management command — python manage.py import_products products.xlsx — so scheduled imports and uploads through a view share one tested code path.

Step 6 — Send the errors back as a workbook

People fix spreadsheets in Excel, so return the problems in Excel: the original rows with an error column, sorted by row number:

valid, errors = validate_rows(df)
dupes = df[df.duplicated("SKU", keep=False)]
errors += [{"row": r.excel_row, "sku": r.SKU, "field": "sku", "problem": "SKU appears more than once"}
           for r in dupes.itertuples(index=False)]
problems = (pd.DataFrame(errors).groupby("row")["problem"].apply("; ".join).rename("Problem"))
report = df.set_index("excel_row").join(problems, how="inner").reset_index()
report.to_excel("products_upload_errors.xlsx", index=False)
print(report[["excel_row", "SKU", "Problem"]].to_string(index=False))

In a web view this workbook is returned as a download right after the failed upload — Return an Excel File from a Flask Download Endpoint shows the response side, and the same HttpResponse pattern works in Django.

Common pitfalls

• Creating rows one by one without a transaction. A failure half-way leaves half an import; wrap it in transaction.atomic().
• Letting pandas guess types. Read as text and let the model validate.
• Error messages with pandas index numbers. Report the Excel row number the uploader sees.
• "Last one wins" for duplicate keys. Report duplicates and let a person choose.
• Trusting uploaded files blindly. Check size and type before reading; see Validate an Uploaded Excel File in a Web App.

Conclusion

Read the upload with dtype=str and keep_default_na=False, number each record by its Excel row, validate each row with the model's full_clean() and collect every error, and reject duplicate keys inside the file. Import only when every row is valid — related objects looked up or created in bulk, products upserted with bulk_create(update_conflicts=True) inside transaction.atomic() — and otherwise return a workbook of the problems keyed by row number.

Frequently asked questions

How do I import an Excel file into a Django model? Read the file with pandas, build an unsaved model instance per row, validate it with full_clean(), and save the valid instances inside transaction.atomic(), for example with bulk_create. Report invalid rows back to the user.

How do I update existing records instead of creating duplicates? Use bulk_create with update_conflicts=True, unique_fields set to the natural key such as sku, and update_fields listing the columns to overwrite. Existing rows are updated and new ones inserted in one statement.

How do I validate Excel rows with Django's rules? Create a model instance from each row and call full_clean(). It runs field validation, blank and length checks, validators and the model's clean method, raising ValidationError with a message per field.

How do I show users which rows failed? Keep each record's spreadsheet row number — the pandas index plus two — and include it in every error. Return the failing rows with an error column as an Excel file they can fix and re-upload.

Related

• Up one level: Serving Excel Files from Python Web Apps — Excel in and out of web applications.
• Export a Django Queryset to Excel — the opposite direction.
• Handle an Uploaded Excel File in Flask and FastAPI — receiving the upload.
• Validate Excel Rows with Pydantic — the same row-by-row validation outside Django.