Microsoft Excel Setup

The Microsoft Excel Synapse enables data synchronization between your Excel workbooks stored in OneDrive or SharePoint and Syncari entities. It supports reading and writing rows across one or more Excel files organized in folders, and can be used as both a source and destination in your pipelines.

Prerequisites

• A Microsoft 365 account with access to OneDrive or SharePoint.
• An Azure App Registration (Microsoft Entra ID) with the following Microsoft Graph API permissions:
  • Files.ReadWrite.All
  • Sites.ReadWrite.All (required for SharePoint)
• A Client ID and Client Secret from the Azure App Registration.
• The OAuth endpoint URL for your tenant (e.g., https://login.microsoftonline.com/<tenantId>).
• Excel files in .xlsx or .xls format with column headers in the first row.

Supported Synapse Capabilities

• Schema discovery
• Create, Read, Update, and Delete rows in any worksheet corresponding to an Entity
• Schema Sync
• Supports both OneDrive and SharePoint as file storage locations
• Single-file mode (specific workbook) or multi-file mode (all workbooks in a subfolder treated as one entity)

Organizing Your Excel Files in Azure

The synapse maps your folder hierarchy to Syncari's data model as follows:

• A root folder (configured via Folder Path) — think of this as the database.
• Subfolders within the root folder — each subfolder becomes an Entity in Syncari, named after the subfolder.
• One or more Excel workbooks (.xlsx / .xls) inside each subfolder contain the object data.
• The first row (header row) represents the attributes; each subsequent row is a record.

Example structure:

OneDrive / SharePoint Document Library
  └── SyncariData/                    ← Root Folder (Folder Path)
        ├── contacts/                  ← Subfolder = Entity "contacts"
        │     ├── contacts_us.xlsx     ← Workbook (partition 1)
        │     └── contacts_eu.xlsx     ← Workbook (partition 2, same headers)
        └── leads/                     ← Subfolder = Entity "leads"
              └── leads.xlsx           ← Workbook

Configure the Microsoft Excel Synapse in Syncari

Once you have the prerequisites in place, you can connect the Microsoft Excel synapse in Syncari:

1. In Syncari, open Synapse Studio.
2. Find Microsoft Excel (SharePoint/OneDrive) in the Synapse Library, and drag it to the canvas.

3. Complete the information in the Configure step:

Common Attributes (OneDrive & SharePoint)

• Synapse Name Required — Enter your own custom name for the synapse.
• Endpoint URL Required — Microsoft Entra ID OAuth endpoint with tenant ID.
  Example: https://login.microsoftonline.com/<tenantId>
• Location Type Required — Select OneDrive or SharePoint. Default is OneDrive.
• Folder Path Required — Path to the root folder containing your entity subfolders.
  For SharePoint, this path is relative to the document library root.
• Workbook Name Optional — Specific workbook filename (e.g., data.xlsx). If provided, syncs only this file (single-file mode). If empty, syncs all Excel files in each subfolder (multi-file mode).
• Worksheet Name Optional — Specific worksheet tab to sync. If empty, uses the first worksheet.
• Authentication Method — Select OAuth.

Additional Attributes for SharePoint

When SharePoint is selected as the Location Type, two additional attributes appear after Location Type:

• SharePoint Site URL Required SharePoint only — Full URL of your SharePoint site.
  Example: https://test.sharepoint.com/sites/team-site
• Document Library Optional SharePoint only — Name of the document library (e.g., Shared Documents). If empty, uses the default "Documents" library.

Authenticate

4. Click Next to proceed to the Authenticate step.
5. Enter your Client ID and Client Secret from the Azure App Registration.
6. Copy the OAuth redirect URL displayed in the dialog and register it in your Azure App Registration under Authentication > Redirect URIs.
7. Click Verify the redirect URL and authenticate. You will see a "Saved and authenticated" confirmation.

Finish

8. Click Next.
9. Select Make Synapse Active.
10. Click Close.

Your Microsoft Excel synapse is now active and connected.

Unique Identifier

The synapse identifies each row in a worksheet as a record. There are 3 methods for defining the unique identifier on a source Excel file:

1. Define a SyncariId column

Add a column with the header name SyncariId anywhere in your worksheet. Each row must have a unique value (e.g., an email address or UUID). Use this method if rows may be inserted or reordered.

2. Configure the ID Attribute in Schema Studio

If your sheet already has a column with unique values, you can mark it as the ID attribute:

1. Go to Schema Studio and select your Microsoft Excel Synapse.
2. Select the Entity, click ⋮ > New Draft, then click Attributes.
3. Select the attribute, click Edit Attribute, check "ID Attribute", and click Save.
4. Click Publish to save.

3. Default

If neither of the above methods is configured, Syncari falls back to generating the ID using the workbook name and row number (e.g., contacts_us-49). This is position-based and will break if rows are inserted or reordered. We recommend using method 1 or 2 instead.