How to read Sage 100 data in Python with hundred-odbc and pandas

What you need

• Windows, on a machine that can reach the Sage 100 data folder (MAS90) and has the "MAS 90 4.0 ODBC Driver" from the Sage 100 workstation setup. The driver is a Windows DLL, so this does not run on Linux or macOS.
• Python 3.9 or newer, in the same bitness as the driver. For Sage 100 2026 that means 64-bit Python (section 7).
• A Sage 100 user with access to the company you read. A user of its own for scripts makes its activity easy to tell apart.

1. Install

hundred-odbc is MIT-licensed and is not on PyPI yet. Install it from GitHub together with pandas. The [odbc] extra pulls in pyodbc 5 or newer:

py -m pip install "hundred-odbc[odbc] @ git+https://github.com/tessaherself/sage100-odbc" pandas

No Git on the machine? pip also installs from the zip archive GitHub serves:

py -m pip install "hundred-odbc[odbc] @ https://github.com/tessaherself/sage100-odbc/archive/refs/heads/main.zip" pandas

2. Connect

For a script you run by hand, the DSN Sage installs, SOTAMAS90, is enough. connect() upper-cases the company code and the user ID for you and opens the connection with autocommit on:

import os
from hundred_odbc import SageReader, connect

conn = connect(company="ABC", user="APIUSER", password=os.environ["SAGE100_PASSWORD"])  # DSN=SOTAMAS90
sage = SageReader(conn)

For a scheduled job, use a DSN-less connection string instead, because Sage 100 recreates SOTAMAS90 each time it starts. The ODBC guide, section 1 explains each part of the string:

conn = connect(connection_string=(
    "Driver={MAS 90 4.0 ODBC Driver};Company=ABC;UID=APIUSER;"
    "PWD=" + os.environ["SAGE100_PASSWORD"] + ";"
    r"Directory=\\sage-server\Sage\Sage 100 Standard\MAS90;StripTrailingSpaces=1"
))

Keep the password out of the script, for example in an environment variable as here.

3. Customers into a DataFrame

Each method returns a list of frozen dataclasses. Field names are the Sage 100 column names in snake_case (CurrentBalance becomes current_balance), money and quantities are Decimal, dates are datetime.date, and fixed-width text comes back trimmed. This helper builds the DataFrame with every column in place even when Sage returns no rows, so later code does not fail with a KeyError on an empty result:

from dataclasses import asdict, fields
import pandas as pd
from hundred_odbc import Customer, Item, SalesOrderLine

def frame(records, cls):
    """A DataFrame with one column per field, even when Sage returns no rows."""
    return pd.DataFrame([asdict(r) for r in records], columns=[f.name for f in fields(cls)])

customers = frame(sage.customers(), Customer)
customers["customer_id"] = customers["ar_division_no"] + "-" + customers["customer_no"]

money = ["credit_limit", "current_balance"]
customers[money] = customers[money].astype(float)

over_limit = customers[customers["current_balance"] > customers["credit_limit"]]

Pandas stores Decimal values in a column of dtype object. Convert to float, as above, for analysis and charts; keep the Decimal values when you export amounts to another accounting system, where rounding matters. To read one division only, pass sage.customers(division="01").

4. Open sales orders, one row per line

open_sales_orders() returns orders with status new or open (N, O) and type standard (S), each with its lines. The package reads headers and lines in two queries on the key columns instead of one join, because the JOIN keyword fails on some versions of the driver (ODBC guide, section 3). Flatten them into one row per line:

orders = sage.open_sales_orders()

header_cols = ["sales_order_no", "order_date", "customer_id", "status"]
line_cols = [f.name for f in fields(SalesOrderLine)]
lines = pd.DataFrame(
    [
        {"sales_order_no": o.sales_order_no, "order_date": o.order_date,
         "customer_id": o.customer_id, "status": o.status, **asdict(line)}
        for o in orders
        for line in o.lines
    ],
    columns=header_cols + line_cols,
)
lines["order_date"] = pd.to_datetime(lines["order_date"])
lines["open_qty"] = (lines["quantity_ordered"] - lines["quantity_shipped"]).astype(float)

lines.to_csv("open_order_lines.csv", index=False)

Orders on hold are left out by default. To include them: sage.open_sales_orders(statuses=("N", "O", "H")). Other order types need order_types, for example order_types=("S", "B") to add back orders; the ODBC guide, section 4 lists the status and type codes.

5. Items: which open orders stock does not cover

items() reads active items from CI_Item, including each item's total_quantity_on_hand. Compared with the open lines from section 4, it gives a rough list of items whose open order quantity exceeds what is on hand:

items = frame(sage.items(), Item)
on_hand = items.set_index("item_code")["total_quantity_on_hand"].astype(float)

demand = lines.groupby("item_code")["open_qty"].sum()
short = (demand - on_hand.reindex(demand.index).fillna(0)).clip(lower=0)
print(short[short > 0].sort_values(ascending=False))

It is rough on purpose: it ignores warehouses, purchase orders on the way and stock already allocated elsewhere. Pass include_inactive=True to read inactive items too.

6. Only what changed since the last run

customers() and items() take updated_since, a datetime.date, and filter on Sage's DateUpdated column:

import datetime as dt

changed = frame(sage.customers(updated_since=dt.date(2026, 9, 1)), Customer)

This cuts how many rows come back, not necessarily how long the read takes: DateUpdated is not a key column, and the driver reads the whole file for a filter on a non-key column (ODBC guide, section 3). open_sales_orders() has no updated_since; it always returns every open order.

7. The 64-bit note

Your Python and the Sage ODBC driver must have the same bitness. Sage 100 2026 is 64-bit only, so on 2026 you need a 64-bit Python; older versions can have a 32-bit driver, a 64-bit one, or both. Put this check at the top of every script; sys.maxsize > 2**32 is true in a 64-bit interpreter (Python docs), and pyodbc.drivers() lists the drivers the running process can load (pyodbc wiki):

import sys
import pyodbc

print("64-bit Python" if sys.maxsize > 2**32 else "32-bit Python")
print([d for d in pyodbc.drivers() if "MAS 90" in d])  # empty: this Python cannot load the Sage driver

What else changes in 2026, for scheduled jobs and BOI too: Guide 05: Sage 100 2026 is 64-bit only.

8. Common errors

9. What the package does not do

• It does not write. It has no method that writes, and every query it sends must start with SELECT. Sage's ODBC driver is for reading; to create or change records with Sage 100's own validation, use the Business Object Interface (BOI guide).
• It runs only on Windows, next to the Sage 100 data, with a driver of matching bitness.
• It reads three things: customers, open sales orders with their lines, and items. For any other table (invoices, vendors, purchase orders, general ledger), use pyodbc with the SQL from the ODBC guide.
• Our tests use a stand-in connection. We checked every snippet on this page against the package's code with the fake database connection from its tests, under pandas 2 and 3. We have no Sage 100 company of our own to run it against, so your first run is the real test. If something breaks, open an issue on GitHub.

Sources: hundred-odbc on GitHub, Microsoft: SQLDriverConnect (IM002, IM014), Stack Overflow: Python and the MAS 90 driver, Python docs: platform, pyodbc wiki, pandas: DataFrame.