Control Excel Data Import to SharePoint App
A PowerShell with SharePoint tutorial
 Both
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.
|
