Creating a SharePoint list from a large Excel table (with thousands of rows and many columns) requires a method that ensures data integrity, avoids import limits, and allows for column type mapping. Here's a reliable, scalable approach using Power Automate (recommended) or alternatives like Access and PowerShell if you're dealing with very large datasets.

✅ Recommended Method: Power Automate (Flow)

Why?

• Handles large datasets in batches

• Custom mapping of Excel columns to SharePoint list columns

• Can skip headers, empty rows, and supports conditional logic

• Doesn’t hit the 5000 item view threshold like Excel direct import

📌 Step-by-Step via Power Automate

✅ Prerequisites:

• Excel table stored in OneDrive or SharePoint (must be formatted as a table!)

• SharePoint list created with matching column names/types

🔄 Flow Outline:

1. Trigger:

   • Use the manual trigger or schedule trigger (Recurrence) for automation.

2. List rows from Excel:

   • Action: List rows present in a table

   • Connect to your Excel file in OneDrive or SharePoint

   • This reads the table rows; supports up to 256 columns

3. Apply to Each row:

   • Use the Apply to each loop

   • For each row, use the Create item or Update item in SharePoint

4. Create item in SharePoint:

   • Map each Excel column to the SharePoint list field

   • Supports all common data types (text, number, choice, date)

⚠ Tips for Large Files:

• Add a ‘Status’ column in Excel to track imported rows (helps with retries)

• Paginate Excel connector: set Pagination ON → Threshold: 5000+

• Consider breaking the dataset into chunks for easier flow execution

🚫 What to Avoid

• Direct Import via SharePoint UI: Only supports 20MB Excel and <5000 rows reliably

• Drag-and-drop in "Import Spreadsheet" app: Deprecated, unsupported, and buggy

🛠 Alternative Methods (for >10K rows)

1. Microsoft Access (One-time Imports)

• Import Excel into Access

• Use “Export to SharePoint List” feature

• Good for one-time, bulk operations but not dynamic

2. PowerShell (For Admins)

• Use PnP PowerShell to read Excel and insert rows

• Reliable, but needs script logic for batching and error handling

powershellCopyEditImport-Module SharePointPnPPowerShellOnline
$excel = Import-Excel -Path "C:\Data\LargeFile.xlsx"
foreach ($row in $excel) {
    Add-PnPListItem -List "TargetList" -Values @{
        Title = $row.Title
        Status = $row.Status
        ...
    }
}

🏁 Final Best Practices

• Match Excel and SharePoint column names exactly or use custom mappings in Flow

• Avoid exceeding SharePoint’s lookup column limit (12) or view threshold (5000) by using indexed columns and filtered views

• If performance degrades, break large lists into multiple lists or use Microsoft Lists (premium) with partitioning