Home >Tips >PowerShell with SharePoint from Scratch >Import Excel data to SharePoint kalmstrom.com site map icon Site map  

Control Excel Data Import to SharePoint App

A PowerShell with SharePoint tutorial

PowerShell iconBoth SharePoint and Excel have functions for import of Excel data to SharePoint, but with methods that go directly from Excel to SharePoint, you cannot control exactly what is being transferred and how it is done.

By using PowerShell, you can define what Excel data that should be exported to SharePoint, so even if it takes longer to create the scripts Peter Kalmström describes in the demo below, it can be worth the effort.

The demo also shows several useful techniques that can be valuable in other contexts, such as key-value pairs, the Get-Content and Get-PnPValue cmdlets and the way to tell PowerShell about a tab delimiter: 't. When Peter recorded the demo he did not remember the word for the sign before the t, but here we can tell you that it is called backtick or backquote.

Peter describes two methods to import data from Excel lists to SharePoint via PowerShell. The first method can only be used for a list with one column. Here Peter basically creates a lot of commands to run a function that creates new list items in SharePoint. Such commands can easily be created in Excel, as we have already seen in a previous article.

function Add-Country([string] $CountryName){
    Add-PnPListItem -List "Countries" -Values @{'Title'=$CountryName}
}

The second method works for all Excel lists. Peter saves the Excel file as a text file and uses the Get-Content cmdlet to fetch the data from it. The variable Rows is used for an array that makes sure the data is ordered properly.

$Text = Get-Content "C:\Users\PeterKalmström\Documents\Customers.txt"
$Rows = ConvertFrom-Csv -Delimiter "`t" -InputObject $Text
#$Row = $Rows[0]

foreach($Row in $Rows){
    $RowValues =@{}
    $RowValues +=@{'Title' = $Row.'Contact last name'}
    $RowValues +=@{'Company' = $Row.CustomerName}
    $RowValues +=@{'FirstName' = $Row.'Contact first name'}
    $RowValues +=@{'Email' = $Row.'E-mail'}
    Add-PnPListItem -List "Customers" -Values $RowValues 
}

A forEach loop runs through the Rows array and creates new items in the SharePoint app, and the values in each new item are defined in a RowValues variable. With key-value pairs, we can match the properties of each Excel row with the internal column names in the SharePoint app.


back icon next icon
Products Buy FAQ Services Tips Books Contact About Us Tools

Security and integrity

Copyright  Kalmstrom Enterprises AB  All rights reserved