writeSDoCfromExcel on Github

writeSDoCfromExcel is a Python tool that adds Wikidata QIDs to the Structured Data on Commons (SDoC) of files listed in an Excel sheet. It supports one or multiple properties (e.g. depicts (P180) depicts, creator (P170) creator), de-duplicates safely, and logs every action.

For example, it can add putto (Q284865) to depicts (P180) for File:Atlas_Schoemaker-UTRECHT-DEEL1-3120-Utrecht,_Utrecht.jpeg based on rows in your Excel.

Latest update: 17 October 2025

What it does

• Reads your Excel (sheet & column names are configurable in the script).
• Per row and per mapped property:

 ** Determines the file’s M-ID (uses CommonsMid if given; otherwise resolves it from CommonsFile, which can be a full URL, a File: title, or a bare filename).
 ** Validates inputs (M-ID, P-id, Q-id).
 ** Fetches the file’s current SDoC values for that property.
 ** Skips duplicates (won’t add a QID that’s already present).
 ** Adds the new claim via the Commons API (unless running in DRY_RUN mode).

• Writes a CSV log with: ADDED, SKIPPED_DUPLICATE, SKIPPED_INVALID, WOULD_ADD (dry-run), or ERROR.

Requirements

• Python 3.10+
• Libraries: pandas, requests, urllib3 (Retry), and optional python-dotenv for loading credentials.

pip install pandas requests urllib3 python-dotenv

Configuration

The tool is IDE-friendly (PyCharm / VS Code). Configure the CONFIG block inside the script and run it.

Credentials

Recommended: use a bot password and store credentials in a .env file next to the script. But you can also use your regular Wikimedia credentials. The script loads it automatically if CONFIG["DOTENV_PATH"] points to it. Example:

COMMONS_USER="YourWikiUsername"
COMMONS_PASS="YourWikiPassword"
COMMONS_USER_AGENT="KB-Excel-SDoC-Writer/1.0 (User:YourWikiUsername; your.email@example.org)"

You can also set CONFIG["USER"] / CONFIG["PASSWORD"] directly (not recommended). If login fails or is omitted and DRY_RUN is False, edits may be attributed to your IP.

Running

1. Set DRY_RUN: True to simulate and review output/logs.

1. Run the script (from your IDE or shell).

1. Inspect console output and the CSV in the logs/ folder.

When satisfied, set DRY_RUN: False to perform real edits. Keep a non-zero PAUSE and consider a MAX_EDITS cap.

Logging

Each attempt is logged to CSV with:

timestamp, commons_mid, commons_file, property, qid, action, details, edit_id, dry_run

Console messages include full Commons and Wikidata URLs.

Notes & etiquette

• Uses retries/backoff for transient errors and a maxlag hint on writes.
• Please be courteous to Commons: keep PAUSE > 0, use a bot account, and test in small batches.
• The script adds values of the wikibase-item datatype (QIDs). Other datatypes need code changes.

Known issues

• If your Excel has empty CommonsFile cells and no valid CommonsMid, those rows are skipped as invalid.
• File title normalization handles URLs, File: titles and bare filenames; odd encodings may still need cleanup.

Source

• Repository on Github: writeSDoCfromExcel
• Python script: WriteSDoCfromExcel.py

Contact & credits

• Written by Olaf Janssen (KB, National Library of the Netherlands).
• Contact Olaf via his KB expert page or Wikimedia user page.
• Many thanks to User:Multichill and the Wikimedia Commons community.

License

Released under CC0 (public domain). Feel free to reuse and adapt. Attribution (KB, National Library of the Netherlands) is appreciated but not required.