Load Excel Data into MongoDB with Python

A product catalogue maintained in Excel needs to feed an application that stores its data in MongoDB. The obvious one-liner — collection.insert_many(df.to_dict("records")) — works the first time and causes trouble from then on: blank cells arrive as NaN values that the application cannot handle, dates are stored as strings or timestamps of the wrong type, a column like "Supplier city" stays flat when the application expects a nested supplier object, and running the import again duplicates every product. A good import shapes each row into the document the application expects, writes real types, leaves blanks out, and upserts on a business key so that reloading the same or an updated file is safe. This page builds that import with pandas and PyMongo. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install pandas openpyxl pymongo

catalogue.xlsx lists five products with SKU, name, price, stock, launch date, semicolon-separated tags and supplier details in columns named supplier.name and supplier.city; some cells are blank. The code uses PyMongo against a MongoDB server (mongodb://localhost:27017 here). The outputs on this page were produced against a throwaway local MongoDB 8.0 server started for the test; the code is identical against any server or Atlas cluster.

Step 1 — Read the sheet with the right types

Start by reading the sheet so that each column already has the type it should have in MongoDB:

import pandas as pd

df = pd.read_excel("catalogue.xlsx", dtype={"sku": str})
print(df.dtypes.to_dict())
print(df.head(2).to_dict("records"))

SKUs are read as text so a code such as 00123 would keep its leading zeros. Prices are floats, the launch date column is a pandas datetime and the stock column, which has a blank, is a float — pandas has no integer type with missing values by default. Each of these needs a decision before it becomes a document: MongoDB stores dates as BSON dates, integers as int or long, and has no use for NaN in fields the application treats as optional.

Step 2 — Shape each row into a document

Write one function that turns a row into exactly the document the application expects. Dotted column names become nested objects, blanks are left out, numbers and dates get proper types, and the tag string becomes an array:

import math
from datetime import datetime

def to_document(row):
    doc = {}
    for col, value in row.items():
        if value is None or (isinstance(value, float) and math.isnan(value)) or value is pd.NaT or value == "":
            continue                                   # blank cell: leave the field out
        if isinstance(value, pd.Timestamp):
            value = value.to_pydatetime()
        elif col == "stock":
            value = int(value)
        elif col == "tags":
            value = [t.strip() for t in value.split(";") if t.strip()]
        target = doc
        *parents, leaf = col.split(".")
        for p in parents:
            target = target.setdefault(p, {})
        target[leaf] = value
    return doc

docs = [to_document(r) for r in df.to_dict("records")]
print(docs[0])
print(docs[3])
print(docs[4])

supplier.name and supplier.city become a nested supplier object — the dotted-name convention mirrors how MongoDB addresses nested fields in queries, so the spreadsheet column names read naturally. The rake has no stock figure, so its document has no stock field at all, instead of stock: NaN; the ladder has no launch date and no supplier city, so those fields are absent too. Leaving fields out is the MongoDB-idiomatic way to say "unknown", and queries such as {"stock": {"$exists": False}} find such documents easily. Converting pandas Timestamp values to Python datetime objects makes PyMongo store them as BSON dates.

Step 3 — Upsert on the business key

Inserting every time duplicates products on the second run. Instead, update the document with the same SKU if it exists and insert it otherwise. bulk_write sends all the operations in one round trip, and a unique index on the SKU guards against duplicates from any other source:

from pymongo import MongoClient, UpdateOne

client = MongoClient("mongodb://localhost:27017")
products = client["shop"]["products"]
products.create_index("sku", unique=True)

def load(docs):
    ops = [UpdateOne({"sku": d["sku"]}, {"$set": d, "$currentDate": {"updated_at": True}}, upsert=True) for d in docs]
    result = products.bulk_write(ops, ordered=False)
    return {"inserted": result.upserted_count, "updated": result.modified_count, "matched": result.matched_count}

print("first load: ", load(docs))
print("second load:", load(docs))
print("documents:", products.count_documents({}))

The first load inserts five documents; loading the same file again matches all five and inserts nothing, so the collection still holds five products. That is what makes the import safe to schedule or rerun after a failure. $set replaces only the fields present in the document, which matters for the next step; $currentDate stamps each document with the time it was last loaded. ordered=False lets MongoDB carry on with the remaining operations if one fails, and the result reports how many were upserted, matched and actually modified. Note that the second load reports five modified documents even though no product data changed: $currentDate sets a new timestamp, so every matched document really is modified. Drop the timestamp if you want modified_count to show only genuine data changes.

Step 4 — Reload an edited spreadsheet

The real test is the next version of the file: a price changes, a stock figure is filled in, and a new product is added. Simulate that and load again:

df2 = df.copy()
df2.loc[df2["sku"] == "SAW-02", "price"] = 26.0
df2.loc[df2["sku"] == "RAK-04", "stock"] = 30
df2.loc[len(df2)] = ["GLV-06", "Work gloves", 6.5, 120, pd.Timestamp("2026-09-01"), "safety", "SafeHands", "Leeds"]
print("edited load:", load([to_document(r) for r in df2.to_dict("records")]))
print(products.find_one({"sku": "RAK-04"}, {"_id": 0, "sku": 1, "stock": 1}))
print(products.find_one({"sku": "SAW-02"}, {"_id": 0, "price": 1}), "| total:", products.count_documents({}))

The new gloves are inserted, the saw's price and the rake's stock are updated, and nothing is duplicated — six products in total. One subtlety: $set never removes a field, so if someone clears a cell in the spreadsheet, the old value stays in MongoDB. If cleared cells should delete fields, add an $unset for every expected field the row is missing, or replace whole documents with ReplaceOne instead — the right choice depends on whether other systems also write to these documents.

Step 5 — Check what landed

Finish with a summary that compares the collection against the spreadsheet, so a broken import is noticed:

expected = set(df2["sku"])
stored = {d["sku"] for d in products.find({}, {"sku": 1})}
print("missing from MongoDB:", sorted(expected - stored), "| not in sheet:", sorted(stored - expected))
print("no stock figure:", [d["sku"] for d in products.find({"stock": {"$exists": False}}, {"sku": 1})])
print("launched before 2023:", [d["sku"] for d in products.find({"launched": {"$lt": datetime(2023, 1, 1)}}, {"sku": 1})])

Every SKU in the sheet is in the collection and vice versa; the query for documents without a stock field finds none now that the rake's stock was filled in; and a date-range query works because the launch dates were stored as real dates — with strings, $lt would compare text. Documents in MongoDB that are not in the sheet any more are reported rather than deleted: whether a product dropped from the spreadsheet should disappear from the application is a business decision, not something an import should do silently. Exporting the collection back to Excel follows the reverse path, flattening nested fields into dotted columns — see Export SQL Query Results to Excel with Python for the writing side.

Common pitfalls

• insert_many(df.to_dict("records")). NaN values, wrong types and duplicates on every rerun.
• Storing dates as strings. Range queries then compare text; convert to datetime.
• Flat columns for nested data. Use dotted column names and build nested objects.
• No unique index on the key. Duplicates can still arrive from other writers.
• Expecting $set to clear fields. Use $unset or ReplaceOne if blanks should remove values.

Conclusion

To load Excel data into MongoDB, read the sheet with deliberate types, shape each row into the document the application expects — nested objects from dotted column names, lists from delimited text, real dates and integers, blank cells left out — and write with bulk_write of UpdateOne(..., upsert=True) keyed on a business identifier with a unique index. The import is then safe to rerun, picks up edits and new rows, and can be checked by comparing the keys in the sheet and the collection.

Frequently asked questions

How do I import an Excel file into MongoDB with Python? Read the sheet with pandas, convert each row into a dictionary shaped like the target document, and write the documents with PyMongo, ideally with bulk_write and UpdateOne upserts on a key field.

How do I stop NaN values being stored in MongoDB? Skip blank cells when building each document so the field is left out, rather than passing NaN from pandas into the document.

How do I avoid duplicate documents when re-importing an Excel file? Use UpdateOne with upsert=True filtered on a business key such as the SKU, and create a unique index on that key.

How do I create nested MongoDB fields from Excel columns? Name the columns with dots, such as supplier.name and supplier.city, and split each name on the dot to build nested dictionaries.

Related

• Up one level: Moving Data Between Excel and Databases — getting data in and out.
• Upsert Excel Rows into a Database Table — the same idea for SQL databases.
• Validate Excel Rows with Pydantic — checking rows before they are loaded.
• Convert an Excel Sheet to JSON with Python — documents without a database.