Export a SharePoint list back into a pandas DataFrame.

Reads a list (default: the Stocks_5yr list created by from_dataframe.py) and materializes it into a DataFrame via the deferred to_dataframe() — .execute_query().value holds the result.

The symmetric counterpart (importing a DataFrame into a list) is from_dataframe.py.

Permissions

Requires: pip install office365-rest-python-client[pandas]

View source

import argparse

from office365.sharepoint.client_context import ClientContext
from tests.settings import client_id, password, team_site_url, tenant, username


def _page_loaded(col) -> None:
    """Built-in progress demo — no tqdm needed.

    ``get_all(page_loaded=...)`` fires once per page with the loaded collection;
    print how far the read has progressed.
    """
    print(f"  loaded {len(col)} items so far")


def progress_bar(description: str):
    """tqdm-backed hook — the library only needs a ``Callable[[Progress], None]``."""
    from tqdm import tqdm

    bar = tqdm(desc=description)

    def hook(p):
        if p.total is not None and bar.total is None:
            bar.total = p.total
        bar.update(p.done - bar.n)
        if p.total is not None and p.done >= p.total:
            bar.close()

    return hook


def main():
    p = argparse.ArgumentParser(description="Export a SharePoint list into a pandas DataFrame")
    p.add_argument("--list-title", default="Stocks_5yr")
    p.add_argument(
        "--select",
        default="Id,date,open,high,low,close,volume,Name",
        help="comma-separated fields to export",
    )
    p.add_argument("--no-progress", action="store_true", help="do not print per-page progress")
    p.add_argument(
        "--progress",
        action="store_true",
        help="show a tqdm read-progress bar (can be combined with --no-progress)",
    )
    args = p.parse_args()

    ctx = ClientContext(team_site_url).with_username_and_password(
        tenant=tenant, client_id=client_id, username=username, password=password
    )

    df = (
        ctx.web.lists.get_by_title(args.list_title)
        .items.get_all(
            page_loaded=None if args.no_progress else _page_loaded,
            progress=None if not args.progress else progress_bar("Reading list"),
        )
        .select(args.select.split(","))
        .to_dataframe()
        .execute_query()
        .value
    )

    print(f"Read back {len(df)} items:")
    print(df.head())


if __name__ == "__main__":
    main()

---

← Back to Lists