Bulk Import CSV to SharePoint with Power Automate

Table of Contents

Introduction

Importing large amounts of data from CSV or tab-delimited files to a SharePoint list is a common business need. This guide explains how to build a Power Automate flow for bulk import, highlights key challenges, and provides practical tips for reliable results.

CSV vs Tab-Delimited: Which to Use?

  • CSV files can be problematic if your data contains commas or newlines, as splitting by commas may break columns.
  • Tab-delimited files (.txt) are more reliable for complex data. If you don’t need to handle commas/newlines, CSV is fine; otherwise, save as tab-delimited and follow this guide: Import Large tab delimited Data to SharePoint with Power Automate

Step-by-Step Flow Setup

1. Trigger: File Upload

  • Use the “When a file is created (properties only)” SharePoint trigger to monitor the folder where your files are uploaded to start the Power Automate flow.

2. Settings (Parse JSON)

  • Initialize settings like site URL, list name, batch size, and oData level.

Settings

Sample Content:

{
  "tenantBaseUrl": "4g6zf4.sharepoint.com",
  "sitePath": "sites/Products",
  "listName": "Products1",
  "batchSize": 1000
}

Sample Schema:

{
    "type": "object",
    "properties": {
        "tenantBaseUrl": {
            "type": "string",
            "title": "Base URL of SharePoint Site",
            "description": "Base URL of SharePoint Site"
        },
        "sitePath": {
            "type": "string",
            "title": "Relative path of the site"
        },
        "listName": {
            "type": "string",
            "title": "Sharepoint List",
            "description": "List where items are to be batch created"
        },
        "batchSize": {
            "type": "integer",
            "title": "Batch Size",
            "description": "Number of items to send in a single batch"
        }
    }
}

3. Get File Content

  • Use Get file content action to retrieve the uploaded file’s data.
  • Site Address: @{concat('https://',body('Settings')?['tenantBaseUrl'],'/',body('Settings')?['sitePath'])}
  • File Identifier: @{triggerOutputs()?['body/{Identifier}']}

Get file content

4. Parse CSV

For CSV: Use a Compose action: @{base64ToString(body('Get_file_content')?['$content'])} What this does: The “Get file content” action returns file data in base64 encoded format (a way to represent binary data as text). The base64ToString() function converts this encoded data back into readable text so we can work with the actual CSV content in subsequent steps.

Parse CSV

5. Generate SharePoint Data

Add a Select action to map columns using the ParseCSV output:

  • From: @{skip(split(trim(outputs('ParseCSV')), decodeUriComponent('%0A')), 1)}
  • Map fields:
    • Title: @{split(item(), ',')[1]}
    • Description: @{split(item(), ',')[2]}

What this expression does:

  • trim(outputs('ParseCSV')) - Removes any leading/trailing whitespace from the CSV content
  • split(..., decodeUriComponent('%0A')) - Splits the text into an array using newlines (%0A = line break)
  • skip(..., 1) - Skips the first row (header row) and processes only the data rows
  • split(item(), ',')[1] - For each row, splits by comma and takes the second column (index 1) for Title
  • split(item(), ',')[2] - Takes the third column (index 2) for Description

Example: If your CSV has:

Name,Title,Description
John,Manager,Team Lead
Mary,Developer,Frontend Specialist

The expression will skip “Name,Title,Description” and process only the data rows, mapping “Manager” to Title and “Team Lead” to Description for John’s record.

Generate SP data

6. Batch Processing and Create Items

  • Use Apply to each to chunk data: @{chunk(body('GenerateSPData'), body('Settings')?['batchSize'])}

Why batching is necessary: Power Automate has a 5,000 item limit per Apply to each loop. If your CSV has more than 5,000 rows, the flow will fail without batching.

Issue loading more than 5k

How the chunking works:

  • body('GenerateSPData') - Takes all the mapped SharePoint data from step 5
  • body('Settings')?['batchSize'] - Uses the batch size from your settings (e.g., 1000 items)
  • chunk() function - Splits the data into smaller arrays of the specified size

Example: If you have 3,500 items with batchSize = 1000:

  • Batch 1: Items 1-1000
  • Batch 2: Items 1001-2000
  • Batch 3: Items 2001-3000
  • Batch 4: Items 3001-3500

Each batch processes separately, preventing the 5,000 item limit from being exceeded.

Chunk processing

  • Inside, loop through each item using another apply to each action referring to item

Apply to each

Use Create item action to add to SharePoint.

  • Site Address: @{concat('https://',body('Settings')?['tenantBaseUrl'],'/',body('Settings')?['sitePath'])}
  • List Name: body('Settings')?['listName']
  • Title: @{item()?['Title']}
  • Description: @{item()?['Description']}

Create Item

Full Flow Diagram

whole flow

Third Party alternatives

References