SharePoint

Need help deploying these sources or figuring out how to run them in your data stack?

Join our Slack community or Get in touch with the dltHub Customer Success team.

SharePoint is the document and collaboration platform of Microsoft 365. A SharePoint site keeps structured data in lists and files in document libraries.

This SharePoint dlt verified source and pipeline example load lists and files of one site with Microsoft Graph to the destination of your choice:

• Lists: dlt loads the items of a list into a table named after the list, one column per list column.
• Incremental lists: the first run reads the whole list with the Graph delta query. Later runs read only the items that were added, changed or deleted. dlt merges them on id.
• Files: dlt reads the rows of csv, jsonl, parquet and Excel files of a document library folder into one table. Each row has the URL and the name of its file. Each run reads only new or modified files.
• Your own readers: the folder listing is dlt's filesystem resource, so dlt's readers and your own transformers work on it. The read_files transformer also reads files from any other filesystem, for example a bucket.

The sources and resources that this verified source supports are:

Name	Description
sharepoint_lists	A source with one sharepoint_list resource per list of the site
sharepoint_list	A resource that loads one list, named after the list
sharepoint_files	A source that loads the rows of the files of a folder into one table
sharepoint_folder	A resource that lists the new or modified files of a folder
read_files	A transformer that reads csv, jsonl, parquet or Excel files into rows
to_decimal	A converter for reader_kwargs that loads amounts as decimals
SharepointFileSystem	A read-only fsspec filesystem over a document library

Setup guide

Grab credentials

dlt accesses Graph as a Microsoft Entra ID application, without a user. Register the application. Then grant it read access to the site:

1. Sign in to the Microsoft Entra admin center. Go to Identity > Applications > App registrations > New registration. Give the application a name, for example dlt. Leave the redirect URI empty.
2. On the Overview page of the application, copy the Application (client) ID and the Directory (tenant) ID.
3. Go to Certificates & secrets > New client secret. Copy the Value of the secret immediately. Entra ID does not show it again.
4. Go to API permissions > Add a permission > Microsoft Graph > Application permissions. Add Sites.Read.All or Sites.Selected. Sites.Read.All gives read access to every site of the tenant. Sites.Selected gives read access only to the sites that an administrator grants to the application.
5. Click Grant admin consent for the tenant. Without the consent, Graph answers every request with HTTP 403.
6. If you added Sites.Selected, grant the application access to the site with the permissions API.

Then find the id of the site. Sign in to the Graph Explorer. Run this request with the host of your tenant and the path of the site:

GET https://graph.microsoft.com/v1.0/sites/contoso.sharepoint.com:/sites/finance?$select=id

The id has three parts: contoso.sharepoint.com,<site-collection-id>,<web-id>. For the root site of the tenant, request /sites/contoso.sharepoint.com?$select=id.

Initialize the verified source

1. Run the following command:

   dlt init sharepoint duckdb

   This command creates the pipeline example with SharePoint as the source and duckdb as the destination.

2. To use a different destination, replace duckdb with the name of your destination.

The command creates a directory with the files and the config that the pipeline needs.

Add credentials

In .dlt/secrets.toml, set the ids and the secret of the application and the id of the site:

[sources.sharepoint.credentials]
tenant_id = "tenant_id"
client_id = "client_id"
client_secret = "client_secret"
site_id = "contoso.sharepoint.com,site-collection-id,web-id"

Run the pipeline

1. Install the dependencies:

   pip install -r requirements.txt

2. Run the pipeline:

   python sharepoint_pipeline.py

   The pipeline example loads the lists of the generic template, see sharepoint_lists. load_list_columns and load_files load one list and the files of one folder. To run them, set a list and a folder of your site. Then uncomment the calls at the end of the script.

3. Show the loaded data:

   dlt pipeline sharepoint show

For more information, read the guide on how to run a pipeline.

How data is loaded

Lists

dlt reads the items of a list with the Graph delta query. The first run reads all items. dlt keeps the delta link in the pipeline state. Later runs continue from this link. They read only the items that were added, changed or deleted after the previous run. dlt merges the items on id, the id of the item in the list.

• A row has a column for each column that Graph returns in the fields of an item. These are the columns of the list and the system columns, for example Title, Created, Modified, AuthorLookupId and ContentType. dlt removes the @odata annotations. included_columns selects the columns.
• Column names are the internal names of the list columns. SharePoint removes spaces from the name that you type, so a column named "Story Points" is StoryPoints. To see the internal names, request /sites/{site-id}/lists/{list-title}/columns in the Graph Explorer.
• A lookup or person column loads as <column>LookupId, the id of the referenced item.
• Number columns load as double. A date-only column loads as midnight in the time zone of the site, converted to UTC, for example 2024-04-01T07:00:00Z for a site in Pacific time.
• A multi-value column, for example a multi-choice column, becomes a nested table. To keep it as JSON, set max_table_nesting = 0 on the source.
• Graph can send the same item more than once in one run. dlt keeps the last occurrence. To find it, dlt adds _dlt_list_idx, the position of the record in the run, with the dedup_sort hint.

When an item is deleted in SharePoint, Graph sends only its id. By default, dlt keeps the record with _deleted set to true and the other columns empty. With hard_delete=True on sharepoint_lists, dlt deletes the record from the destination. For a single sharepoint_list, set the hard_delete hint on the _deleted column:

tasks = sharepoint_list("Tasks").apply_hints(columns={"_deleted": {"hard_delete": True}})

A delta link is valid for one list and one set of columns. When you change included_columns, the next run reads the whole list again and merges it into the same table. The records of items that were deleted since the previous run stay in the table. To drop the table and read the list again, run the pipeline with refresh="drop_resources". If Graph cannot continue from the stored delta link (HTTP 410), dlt raises an error that asks for this refresh. This occurs, for example, after a long time without a run.

Files

sharepoint_folder lists the files of a folder of a document library with dlt's filesystem resource. It yields the files in the folder that match file_glob. With ** in the glob, it also yields the files in the subfolders. dlt lists a OneNote notebook like a folder, so ** also finds its section files.

The listing is incremental on modification_date, the time of the last change in SharePoint. Each run yields only the files modified since the last run. With start_date, the first run loads only the files modified since this date. An incremental that you pass replaces the default one, for example to load a window of time.

read_files reads the rows of each file and adds three columns:

Column	Description
_dlt_file_url	The sharepoint://<library>/<path> URL of the file. The library is empty for the default library.
_dlt_file_name	The name of the file
_dlt_file_modified_at	The time of the last change, in UTC

_dlt_file_url is the merge_key of the table. As a result, the records of a modified file replace all of its old records. A renamed file counts as a new file. The records of a file that was deleted in SharePoint stay in the table. dlt skips blank files, that is, empty files or files with only whitespace. As a result, the records of a file that becomes blank also stay.

sharepoint_files pipes sharepoint_folder into read_files and loads all matching files into table_name. A file with other columns adds these columns to the table. The file listing is the <table_name>_files resource of the source. dlt does not select it by default. To also load the name, path, size and modification time of each file, select it:

reports = sharepoint_files("Reports", "csv", table_name="reports")
reports.resources["reports_files"].selected = True

File types

• dlt reads csv and excel files with pandas. reader_kwargs are the arguments of pandas.read_csv and pandas.read_excel, for example sep, encoding, header, dtype, converters or sheet_name. pandas infers the types, so an amount like 10.50 loads as double. To load it as decimal, pass the to_decimal converter:

  reports = sharepoint_files(
      "Reports", "csv", table_name="reports", reader_kwargs={"converters": {"amount": to_decimal}}
  )

  to_decimal removes $ and , from text. It keeps the 15 significant digits that Excel shows. It loads empty cells and text that is not a number as null.

• An Excel workbook loads one table per sheet, named <table_name>_<sheet>. sheet_name in reader_kwargs selects sheets by name or position. Excel dates load as timestamp. Excel durations load as a number of seconds. .xlsx files need openpyxl, which requirements.txt installs. .xls files need xlrd.

• jsonl keeps nested objects as JSON. dlt uses only encoding from reader_kwargs.

• dlt reads parquet files with pyarrow. columns in reader_kwargs selects the columns.

Graph does not send the encoding of a file. dlt selects the encoding of a csv or jsonl file in this order:

1. The byte order mark of the file (UTF-8, UTF-16 or UTF-32).
2. encoding in reader_kwargs.
3. UTF-8.

If Excel saved a csv file in a Windows code page, set encoding, for example to cp1252.

Downloads

dlt downloads each file of up to 16 MiB in one piece on its extract workers, 5 at a time by default. The main thread parses the downloaded files. dlt reads larger files in ranges while it parses them. To download more files at a time, set more workers in config.toml:

[extract]
workers = 10

Sources and resources

Source sharepoint_lists

@dlt.source(name="sharepoint_lists")
def sharepoint_lists(
    list_titles: Optional[List[str]] = None,
    hard_delete: bool = False,
    credentials: SharepointCredentials = dlt.secrets.value,
) -> Iterable[DltResource]:
    ...

sharepoint_lists returns a sharepoint_list resource for each list, named after the list.

• list_titles: the display names of the lists to load. Without it, dlt reads the lists of the site when you create the source. It loads every visible list of the generic template, that is, the lists made with Blank list or From CSV. Lists of other templates, for example tasks or contacts, load only when you name them. A list that you add in SharePoint becomes a new table on the next run. To keep a fixed set of tables, pass list_titles.
• hard_delete: deletes the records of deleted items from the destination. Without it, dlt keeps them with _deleted set to true.

If two list titles normalize to the same table name, for example "Sales Report" and "Sales-Report", both lists load into one table. Item ids start at 1 in every list, so the records of the two lists overwrite each other. To keep both, load one list with sharepoint_list and rename it with with_name.

Resource sharepoint_list

@dlt.resource(name=lambda args: args["list_title"], write_disposition="merge", primary_key="id")
def sharepoint_list(
    list_title: str = dlt.config.value,
    included_columns: Optional[List[str]] = None,
    credentials: SharepointCredentials = dlt.secrets.value,
) -> Iterator[TDataItems]:
    ...

The resource and its table are named after the list. To load the list into another table, rename the resource with with_name. dlt reads the config of a list from sources.sharepoint.<list title>.

• list_title: the display name of the list, as shown in SharePoint.
• included_columns: the internal names of the columns to load. Without it, dlt loads the columns that Graph returns by default. Graph returns up to 12 lookup columns per request.

Source sharepoint_files

@dlt.source(name="sharepoint_files", max_table_nesting=0)
def sharepoint_files(
    folder_path: str = dlt.config.value,
    file_type: TFileType = dlt.config.value,
    table_name: str = dlt.config.value,
    library: Optional[str] = None,
    file_glob: str = "*",
    reader_kwargs: Optional[Dict[str, Any]] = None,
    start_date: Optional[TAnyDateTime] = None,
    incremental: Optional[dlt.sources.incremental[Any]] = None,
    credentials: SharepointCredentials = dlt.secrets.value,
) -> Iterable[DltResource]:
    ...

sharepoint_files returns two resources: <table_name>, which pipes sharepoint_folder into read_files, and the unselected <table_name>_files listing. dlt keeps the incremental state per table_name. When you change the folder or the glob, run the pipeline with refresh="drop_resources". Several sharepoint_files sources in one pipeline share one schema, so refresh="drop_sources" on one drops the tables of the others. Give each its own name, for example sharepoint_files.with_args(name="reports")(...).

• folder_path: the folder relative to the root of the library, for example Reports/2024. An empty string loads the root of the library. The path cannot contain glob characters.
• file_type: "csv", "jsonl", "parquet" or "excel".
• table_name: the destination table. For Excel files, the prefix of the sheet tables, <table_name>_<sheet>.
• library: the display name of the document library, for example Documents. Without it, dlt reads the default library of the site. System libraries, for example Site Assets, cannot be named.
• file_glob: the glob that the paths under the folder must match, * by default. **/*.csv also searches subfolders.
• reader_kwargs: the arguments of the reader, see File types. Converters are functions, so pass them in code, not in config.
• start_date: the first run loads the files modified since this date. Without start_date, the first run loads all files. A date without a time zone is in UTC.
• incremental: your own dlt.sources.incremental on modification_date instead of the default one, for example with an end_value. An incremental with an end_value stores no state, so you can backfill with it. Pass start_date or incremental, not both.

Resource sharepoint_folder

def sharepoint_folder(
    folder_path: str = dlt.config.value,
    library: Optional[str] = None,
    file_glob: str = "*",
    start_date: Optional[TAnyDateTime] = None,
    incremental: Optional[dlt.sources.incremental[Any]] = None,
    credentials: SharepointCredentials = dlt.secrets.value,
) -> DltResource:
    ...

sharepoint_folder returns dlt's filesystem resource over the folder, with the arguments of sharepoint_files. It yields FileItems, so dlt's readers read_csv, read_jsonl and read_parquet and your own transformers can follow it. dlt reads its config from sources.sharepoint.sharepoint_folder.

Transformer read_files

@dlt.transformer(write_disposition="merge", merge_key="_dlt_file_url")
def read_files(
    items: Iterable[FileItemDict],
    file_type: TFileType = dlt.config.value,
    reader_kwargs: Optional[Dict[str, Any]] = None,
) -> Iterator[TDataItems]:
    ...

read_files reads the files that sharepoint_folder or any other listing of FileItems yields, for example dlt's filesystem resource over a bucket or a local folder. Rename the table with with_name or a table_name hint.

Customization

Create your own pipeline

1. Configure the pipeline. Give the pipeline name, the destination and the dataset name:

   pipeline = dlt.pipeline(
       pipeline_name="sharepoint",
       destination="duckdb",
       dataset_name="sharepoint_data",
   )

2. To load selected lists and delete the rows of deleted items, pass list_titles and hard_delete:

   source = sharepoint_lists(list_titles=["Tasks", "Projects"], hard_delete=True)
   print(pipeline.run(source))

3. To load selected columns of one list into a table of your own name, use sharepoint_list:

   tasks = sharepoint_list("Tasks", included_columns=["Title", "Status", "DueDate"])
   print(pipeline.run(tasks.with_name("task_status")))

4. To load the csv files of a folder, including its subfolders, from a date on:

   reports = sharepoint_files(
       "Reports",
       "csv",
       table_name="reports",
       library="Finance",
       file_glob="**/*.csv",
       start_date="2024-01-01",
   )
   print(pipeline.run(reports))

5. To load one sheet of the Excel workbooks of a folder into budgets_data:

   budgets = sharepoint_files(
       "Budgets", "excel", table_name="budgets", reader_kwargs={"sheet_name": "Data"}
   )
   print(pipeline.run(budgets))

Backfill a window of files

An incremental with an end_value loads the files modified in a window and stores no state. Run one window after another to backfill a large folder:

from datetime import datetime, timezone

window = dlt.sources.incremental(
    "modification_date",
    initial_value=datetime(2024, 1, 1, tzinfo=timezone.utc),
    end_value=datetime(2024, 2, 1, tzinfo=timezone.utc),
    range_start="closed",
    range_end="open",
)
print(pipeline.run(sharepoint_files("Reports", "csv", table_name="reports", incremental=window)))

The listing is in folder order, not in the order of modification_date. For this reason, chunked loads with add_limit and row_order do not work.

Use dlt's readers or your own

sharepoint_folder yields FileItems like dlt's filesystem resource, so the readers of the filesystem source work on it. They do not add the file columns, so the records of a modified file do not replace its old records:

from dlt.sources.filesystem import read_csv

rows = sharepoint_folder("Reports", file_glob="*.csv") | read_csv()
print(pipeline.run(rows.with_name("reports")))

read_files works on any listing of FileItems, for example dlt's filesystem resource over a bucket:

from dlt.sources.filesystem import filesystem

files = filesystem(bucket_url="s3://bucket/reports", file_glob="*.xlsx")
print(pipeline.run(files | read_files("excel").with_name("reports")))

Read files with pandas

SharepointFileSystem is registered as the sharepoint protocol of fsspec, so pandas and other fsspec clients can read files of a library directly:

import pandas as pd
import sharepoint  # registers the protocol

df = pd.read_csv(
    "sharepoint://Documents/Reports/report_2024_01.csv",
    storage_options={
        "tenant_id": "...",
        "client_id": "...",
        "client_secret": "...",
        "site_id": "...",
    },
)

The URL is sharepoint://<library>/<path>. For the default library of the site, leave the library empty: sharepoint:///Reports/report_2024_01.csv.

Performance and troubleshooting

Throttling

SharePoint limits the resource units per minute that an application can use in a tenant. The limit depends on the number of licenses of the tenant. If an application uses more units, Graph answers with HTTP 429 or 503 and a Retry-After header. dlt then retries the request after this time.

dlt sends its name in the User-Agent header. SharePoint gives named traffic priority over unnamed traffic. See avoid getting throttled.

A file costs two units: one request for its metadata and one for its content. A page of a folder listing or a delta query costs one or two units. For a folder with thousands of small files, the unit limit sets the speed, not the size of the files.

Tokens

dlt requests an access token for the application on the first call. The token expires after about one hour, and dlt renews it before that time. All resources of a pipeline share one token.

Limitations

• Files deleted in SharePoint keep their records in the destination. To delete these records, run the pipeline with refresh="drop_resources". dlt then loads all files of the folder again.
• The time of the last change of a file is the time that the client reported. The OneDrive sync client and migration tools keep the local times of a file. Such a file can be older than the last modification_date that dlt stored. dlt loads it only in a run with refresh="drop_resources".
• dlt finds a list by its display name. If you rename the list in SharePoint, pass the new name. The next run then reads the whole list again.
• dlt does not load attachments, list versions or site pages.