Bulk Add Rows to SharePoint List With PowerShell & Excel

Adding rows one by one to a SharePoint list is time-consuming and inefficient, especially with large datasets. I’ve encountered this issue in multiple projects, and here I’ll guide you on how to bulk add rows to SharePoint list with simple steps.

Today I’ll explain:

• Why I Needed to Bulk Add Rows to SharePoint
• How to Bulk Add Rows to SharePoint List Step by Step
  • Method 1. Copy & Paste from Excel
  • Method 2. Import via Excel
  • Method 3. Power Automate
  • Method 4. PowerShell
  • Method 5. Bulk Add Rows Across Sites or Tenants
• Best Practices
• Author’s Verdict
• People Also Ask

Why I Needed to Bulk Add Rows to SharePoint

I needed to import a large dataset from Excel into a SharePoint Online list. I wanted a solution that:

• Preserves list formatting
• Supports lookups, choice fields, and other features.
• Doesn’t require complex scripting
• It is repeatable in case I need to do it again

Therefore, I’m wondering about how to import rows to SharePoint Online simultaneously. Luckily, I found a solution to do so, which I will explain in the next section.

How to Bulk Add Rows to SharePoint List Step by Step

First, back up SharePoint Online to local storage to ensure data accessibility in case anything goes wrong. Then, let’s start with the simplest method:

Method 1. Copy & Paste from Excel

Copying and pasting the entire Excel sheet to SharePoint is the best and quickest way, especially after reviewing Microsoft’s guide on editing lists in grid view. To do so, here are the steps:

1. Open the target SharePoint list.
2. Click on the Edit in grid view button.
3. Open the Excel file and copy the rows.
4. In the grid view, click into the first empty row and paste.
5. Click Exit grid view to save the changes.

That’s all! Within minutes, changes will be reflected in your SharePoint list.

Method 2. Bulk Add Rows to SharePoint List Via Excel

If you’re creating a new SharePoint list, then you can create it directly from Excel:

1. Open the SharePoint Site.
2. Now, click on the Site Contents, then New > List.
3. Choose the From Excel option, and upload your Excel file.
4. SharePoint will try to recognise column headers and data types.
5. Confirm the mapping and click Create.

This is best when needed to create a SharePoint list from scratch.

Method 3. Power Automate

When I need to import rows in bulk from a source like Excel automatically, I opt for Power Automate. For the same, I:

1. Create a flow in Power Automate.
2. Use the List rows present in a table action.
3. Use the Apply to each control and add a Create item action.
4. Map each Excel column to the SharePoint fields.
5. Save & test the flow to bulk add rows to SharePoint list.

This looks easy, but not. It requires technical expertise to proceed. 

Method 4. PowerShell

PowerShell is an alternative to Power Automate. I use PowerShell and the PnP PowerShell module for automatic bulk uploads:

• Install PnP PowerShell and connect to SharePoint Online:

• Read data from a CSV file:

• Loop through and create items:

Method 5. Bulk Add Rows to SharePoint List Across Sites or Tenants

While the above methods work within the same SharePoint site, what if you need to bulk add rows from one SharePoint list to another in a different tenant or site collection?

Don’t worry! I found that SysTools SharePoint Migrator is a solution which I needed. 

Download Now Purchase Now

I’ve used this tool to migrate entire SharePoint lists, including rows, metadata, column settings, permissions, and folder hierarchy, from one tenant to another. 

It helped me to:

• Migrate SharePoint list to another site or Tenant
• Retains metadata, version history, user permissions, and folder hierarchy
• Supports selective migration using date, file type, etc., filters
• Allows mapping between source and destination list fields

I recommend using this solution for a one-click, reliable migration with everything preserved. Here are the quick steps:

• Step 1. Launch the software, and select source & target as Microsoft 365.
• Step 2. Click on the List option, and leverage the Date Filter if needed.
• Step 3. Map the source & target, and validate permissions. Click Next.
• Step 4. Press the Start Migration button.

Best Practices to Bulk Add Rows to SharePoint List Error-free

Although we learned how to add rows simultaneously. Still, it is necessary to follow best practices: 

• Always validate your data before importing. 
• For choice fields or lookup columns, make sure the incoming values match exactly.
• Use data templates to ensure consistency with repeating imports.
• With PowerShell, use Write-Output to catch errors during processing.

Author’s Verdict

Bulk adding rows to a SharePoint list is straightforward with the appropriate solution. Using these methods, I’ve cut down the manual workload and improved data quality. So, you can opt for any of them to bulk add rows to SharePoint list. 

People Also Ask

Q1. Can I bulk upload attachments along with list items?
Yes, but only using PowerShell or Power Automate.

Q2. Why are some of my rows not showing up in SharePoint?
Check for validation errors in your list. Also, ensure Excel doesn’t have merged cells or hidden characters.

Q3. Can I undo a bulk upload in SharePoint?
No “undo” button exists. If needed, create an Office 365 backup before uploading.