Ignore Excel Lock Files When Processing a Folder

A script that loops over glob("*.xlsx") in a shared folder works in testing and fails on Monday morning with ValueError: Excel file format cannot be determined or BadZipFile: File is not a zip file. The culprit is rarely a broken workbook. When someone has sales.xlsx open, Excel creates a small hidden file called ~$sales.xlsx next to it — and that name matches *.xlsx. LibreOffice creates .~lock.sales.xlsx#. A file still being copied into the folder is a truncated zip for a few seconds. None of these is a workbook to process, and a robust job recognises each and moves on, saying what it skipped. This page is part of Error Handling and Logging in Excel Automation.

Prerequisites

pip install pandas openpyxl

inbox/ contains three good workbooks — north.xlsx, south.xlsx and west.xlsx — and the debris of a real shared folder: an Excel owner file ~$south.xlsx because someone has South open, a LibreOffice lock file for West, a temporary ~WRL0913.tmp left by an interrupted save, east.xlsx still being copied (only half of it has arrived), and a text file.

Step 1 — See the naive loop fail

Path.glob("*.xlsx") matches Excel's owner file, and pandas cannot read it; the half-copied file is not a valid zip:

from pathlib import Path
import pandas as pd

inbox = Path("inbox")
print(sorted(p.name for p in inbox.glob("*.xlsx")))
for path in sorted(inbox.glob("*.xlsx")):
    try:
        pd.read_excel(path)
        print("read   ", path.name)
    except Exception as err:
        print("FAILED ", path.name, "-", type(err).__name__, str(err)[:48])

Two of the five matches fail: the ~$ owner file with "Excel file format cannot be determined" — pandas looks at its first bytes and finds neither a zip nor an old .xls — and east.xlsx with BadZipFile, because the zip directory at the end of the file has not arrived yet. In a job without the try, the first failure stops everything, and the reports after it in alphabetical order are never built. Fix BadZipFile When Reading an Excel File in Python covers the error in general.

Step 2 — Filter out lock and temporary files by name

The debris has recognisable names. Filter it before anything tries to open a file:

import re

WORKBOOK_SUFFIXES = {".xlsx", ".xlsm", ".xls", ".xlsb"}
IGNORE = re.compile(r"^(~\$|\.~lock\.|~WRL|\.)", re.IGNORECASE)

def candidate_workbooks(folder):
    for path in sorted(folder.iterdir()):
        if not path.is_file():
            continue
        if IGNORE.match(path.name):
            yield path, "lock or temporary file"
        elif path.suffix.lower() not in WORKBOOK_SUFFIXES:
            yield path, "not a workbook"
        else:
            yield path, None

for path, reason in candidate_workbooks(inbox):
    print(f"{path.name:22} {reason or 'candidate'}")

~$ marks Excel's owner files, .~lock. LibreOffice's, ~WRL Excel's temporary files during a save, and a leading dot hidden files in general — .DS_Store from a Mac user, for example. Each skip carries a reason so the job's log can say what it ignored instead of silently processing fewer files than expected.

Step 3 — Skip files that are not complete yet

A workbook being copied has the right name before it has the right content. An .xlsx file is a zip archive, and zipfile.is_zipfile checks for the directory at the end that only exists once the whole file is there. Files that pass by name are checked for that, and — for jobs that watch a folder — for a size that has stopped changing:

import time
import zipfile

def is_complete(path, settle_seconds=0.0):
    if path.suffix.lower() == ".xls":
        return path.stat().st_size > 0            # old binary format, not a zip
    if settle_seconds:
        size = path.stat().st_size
        time.sleep(settle_seconds)
        if path.stat().st_size != size:
            return False
    return zipfile.is_zipfile(path)

for name in ("north.xlsx", "east.xlsx"):
    print(name, "complete" if is_complete(inbox / name) else "incomplete — try again later")

is_zipfile reads only the end of the file, so the check is cheap even for large workbooks. The size-settle check catches a copy that happens to pause at a moment when the partial file looks like a valid zip — rare, but possible with some copy tools. Incomplete files are left for the next run rather than treated as errors: they are not broken, just not ready. Watch a Folder and Process New Excel Files uses the same idea for event-driven jobs.

Step 4 — Notice workbooks that are open elsewhere

An owner or lock file next to a workbook means someone has it open. The file on disk is still readable — it is the last saved version — but it may not be what that person is looking at, and it may change minutes later. Whether to process it, warn or skip it is a policy decision; the job should at least know:

def open_elsewhere(path):
    return (path.with_name("~$" + path.name).exists()
            or path.with_name(f".~lock.{path.name}#").exists())

for name in ("north.xlsx", "south.xlsx", "west.xlsx"):
    print(name, "open elsewhere" if open_elsewhere(inbox / name) else "not open")

On a Windows file share the names are case-insensitive, so compare them in lower case if users' files come with mixed capitalisation. An owner file also survives a crash: if Excel was killed, ~$sales.xlsx stays behind although nobody has the file open. That is another reason to warn rather than refuse, and to report stale owner files older than a day so someone can delete them.

Step 5 — Put it together with a skip report

The loop now processes what is ready, leaves what is not, and records every decision:

from dataclasses import dataclass

@dataclass
class Outcome:
    file: str
    action: str
    detail: str = ""

def process_folder(folder):
    outcomes, frames = [], []
    for path, reason in candidate_workbooks(folder):
        if reason:
            outcomes.append(Outcome(path.name, "skipped", reason))
        elif not is_complete(path):
            outcomes.append(Outcome(path.name, "deferred", "not a complete workbook yet"))
        else:
            frames.append(pd.read_excel(path))
            note = "open in another program — last saved version used" if open_elsewhere(path) else ""
            outcomes.append(Outcome(path.name, "processed", note))
    return pd.concat(frames, ignore_index=True), pd.DataFrame(outcomes)

combined, report = process_folder(inbox)
print(combined.to_string(index=False))
print(report.to_string(index=False))

Three workbooks are combined; South and West are flagged as open elsewhere; East is deferred to the next run; the owner file, lock file, temporary file and text file are skipped with their reasons. Writing report to the job's log — or into a sheet of the output workbook — means a missing region is explained before anyone asks. Log Python Excel Script Output to a File shows how.

Performance notes

Every check here is cheap: filtering is string matching, is_zipfile reads the last few kilobytes of a file, and the owner-file test is two exists calls. The only slow part is the optional settle wait, which sleeps; for a scheduled job that runs every few minutes, skip the wait and rely on is_zipfile plus the next run to pick up anything that was mid-copy. On network shares, directory listings can be slow for folders with thousands of files — list once and reuse the result, rather than globbing repeatedly.

Common pitfalls

• glob("*.xlsx") on its own. It matches ~$ owner files; filter by name first.
• Treating a half-copied file as corrupt. It is not ready yet; defer it instead of alerting.
• Deleting owner files automatically. One may belong to a person who has the file open right now; report stale ones instead.
• Stopping on the first bad file. Handle each file separately and report, so one problem does not hide the rest.
• Writing to a workbook someone has open. On Windows the write fails with a permission error; see Handle Permission Denied When Writing Excel in Python.

Conclusion

Before opening anything, filter the folder by name — ~$, .~lock., ~WRL and hidden files are not workbooks — and by extension. Check that each remaining .xlsx is a complete zip with zipfile.is_zipfile, optionally after its size has settled, and defer the incomplete ones. Note which workbooks have an owner or lock file beside them, process them as their last saved version with a warning, and report every skip and deferral so the output explains itself.

Frequently asked questions

What is the ~$ file next to my Excel workbook? It is an owner file that Excel creates while a workbook is open, recording who has it open. It is deleted when the workbook is closed, but can be left behind if Excel crashes. It is not a workbook and cannot be read by pandas or openpyxl.

Why does pandas say "Excel file format cannot be determined"? The file is not a recognisable Excel file — in a folder loop, usually a ~$ owner file that matched *.xlsx. Skip names starting with ~$ before reading.

How can I tell if an Excel file has finished copying? Check it with zipfile.is_zipfile, which is only true once the whole .xlsx has arrived, and optionally wait until its size stops changing. Leave files that fail for the next run.

Can I read an Excel file while someone has it open? Usually yes: you read the last saved version on disk, which may differ from what they see on screen. Writing to it while it is open will fail on Windows.

Related

• Up one level: Error Handling and Logging in Excel Automation — making report jobs robust.
• Combine Multiple Excel Files into One — the loop this page hardens.
• Skip a Report When the Input File Has Not Changed — another reason to leave a file alone.
• Process Multiple Excel Files in Parallel with Python — the same filtering before fanning out.