Extract Invoice and Order Data from PDFs, Scans and Photos into Excel with OCR and a Local LLM

Operations and finance teams spend hours retyping invoices, purchase orders, delivery notes and supplier quotes into spreadsheets. With OCR to read scans and photos, and a local language model that returns exactly the fields you define, most of that typing can be automated. This guide combines Tesseract and OCRmyPDF with Ollama's structured output: every document becomes a JSON file with the extracted fields, and a summary spreadsheet flags documents whose line items do not add up, so people only check the exceptions. Everything runs on your own server.

Step 1: Install the Tools

# Debian / Ubuntu: PDF text, OCR engine with English and Simplified Chinese, OCR for PDFs
apt install -y python3-venv poppler-utils tesseract-ocr tesseract-ocr-eng tesseract-ocr-chi-sim ocrmypdf
python3 -m venv /opt/extract
/opt/extract/bin/pip install ollama
ollama pull qwen2.5:7b

Ollama setup is covered in running a local LLM with Ollama. For other languages, install the matching Tesseract package, for example tesseract-ocr-jpn, tesseract-ocr-tha or tesseract-ocr-rus.

Step 2: Check How Text Comes Out of Your Documents

# Digital PDF (created by software): the text can be read directly
pdftotext -layout invoice.pdf - | head -40

# Scanned PDF: add a text layer with OCR first, then read it
ocrmypdf -l eng+chi_sim --skip-text scan.pdf scan-ocr.pdf
pdftotext -layout scan-ocr.pdf - | head -40

# Photo or screenshot
tesseract photo.jpg - -l eng+chi_sim | head -40

If the text here is readable, the model can extract fields from it. If it is garbage, improve the input first: scan at 300 dpi, photograph documents flat and well lit.

Step 3: Create the Extraction Script

The SCHEMA defines which fields you want. Ollama forces the model's answer into exactly this structure, so the output is always valid JSON.

cat > /opt/extract/extract.py <<'EOF2'
import csv, json, os, subprocess, sys, tempfile
import ollama

MODEL = os.environ.get("MODEL", "qwen2.5:7b")
LANG = os.environ.get("OCR_LANG", "eng+chi_sim")
SCHEMA = {
    "type": "object",
    "properties": {
        "supplier": {"type": "string"},
        "document_number": {"type": "string"},
        "date": {"type": "string", "description": "YYYY-MM-DD"},
        "currency": {"type": "string"},
        "total": {"type": "number"},
        "tax": {"type": "number"},
        "items": {"type": "array", "items": {"type": "object", "properties": {
            "description": {"type": "string"}, "quantity": {"type": "number"},
            "unit_price": {"type": "number"}, "amount": {"type": "number"}},
            "required": ["description", "quantity", "amount"]}},
    },
    "required": ["supplier", "document_number", "date", "currency", "total", "tax", "items"],
}

def text_of(path):
    run = lambda cmd: subprocess.run(cmd, capture_output=True, text=True).stdout
    if not path.lower().endswith(".pdf"):
        return run(["tesseract", path, "-", "-l", LANG])
    text = run(["pdftotext", "-layout", path, "-"])
    if len(text.strip()) > 50:
        return text
    with tempfile.TemporaryDirectory() as tmp:           # scanned PDF: OCR it first
        ocr = os.path.join(tmp, "ocr.pdf")
        subprocess.run(["ocrmypdf", "-q", "-l", LANG, "--skip-text", path, ocr], check=True)
        return run(["pdftotext", "-layout", ocr, "-"])

rows = []
for path in sys.argv[1:]:
    if not os.path.isfile(path):
        continue
    r = ollama.chat(model=MODEL, format=SCHEMA, options={"temperature": 0, "num_ctx": 8192}, messages=[
        {"role": "system", "content": "Extract the requested fields from the document text. "
         "Copy names, numbers and IDs exactly as written. Use an empty string or 0 "
         "for anything that is not in the text. Do not guess."},
        {"role": "user", "content": text_of(path)[:15000]}])
    d = json.loads(r.message.content)
    items = round(sum(i.get("amount", 0) for i in d["items"]), 2)
    ok = d["total"] > 0 and min(abs(items - d["total"]), abs(items + d["tax"] - d["total"])) < 0.02
    with open(os.path.splitext(path)[0] + ".json", "w", encoding="utf-8") as f:
        json.dump(d, f, ensure_ascii=False, indent=2)
    rows.append([os.path.basename(path), d["supplier"], d["document_number"], d["date"],
                 d["currency"], d["total"], d["tax"], len(d["items"]), "OK" if ok else "CHECK"])
    print(path, "OK" if ok else "CHECK", flush=True)

with open("summary.csv", "w", newline="", encoding="utf-8-sig") as f:
    w = csv.writer(f)
    w.writerow(["file", "supplier", "document_number", "date", "currency", "total", "tax", "lines", "check"])
    w.writerows(rows)
EOF2

Step 4: Process a Folder of Documents

cd /data/invoices/2026-10
/opt/extract/bin/python /opt/extract/extract.py *.pdf *.jpg *.png
# For a large folder, run it in the background:
nohup /opt/extract/bin/python /opt/extract/extract.py *.pdf > extract.log 2>&1 &

Each document gets a .json file with all fields and line items next to it, and summary.csv lists one row per document. Open it in Excel and filter the check column: OK means the line items add up to the total (with or without tax), CHECK means a person should look at that document.

Step 5: Adapt the Fields to Your Documents

• Purchase orders: add buyer, delivery_date and SKU fields on the line items.
• Shipping documents: use fields such as tracking_number, consignee, weight_kg and packages.
• Supplier quotes: extract valid_until, payment_terms and unit prices to compare suppliers in one sheet.

A short description on a field, as on date above, tells the model the format you expect. If you process several document types, keep one schema per type in separate scripts or folders.

Step 6: Improve Accuracy

• Digital PDFs give the best results; scans and photos depend on image quality.
• Keep the temperature at 0 so the same document always gives the same result.
• Larger models such as qwen2.5:14b handle complex layouts and long tables better, at the cost of speed.
• Keep the original file next to its JSON so every figure can be traced back during an audit.

FAQ

How long does one document take?

On a CPU server, typically from a few tens of seconds for a one-page invoice to a few minutes for long multi-page documents, depending on the model. Run large batches overnight.

Can I send the documents to a cloud AI instead?

Many AI APIs also support structured output, but the documents then leave your server. Check your privacy obligations, the provider's terms and its supported regions first. For invoices, contracts and customer data, the local setup is usually the safer choice.

Can the results go straight into our accounting or ERP system?

Most systems can import CSV files; map the columns of summary.csv or convert the JSON files to the import format. Import only rows marked OK automatically and keep CHECK rows for manual review.

Still stuck after following these steps? Open a support ticket and the IMIDC 24/7 technical team will help. Include the server IP, OS version, the model you use and the error output, and if possible a sample document without sensitive data, so we can pinpoint the issue faster.