Import data from Excel to SharePoint using PS Script

 You might start to use SharePoint (on-premise or online) and you need to migrate the existing data from excel to SharePoint list. There are many ways to import data from Excel to SharePoint, just google a keyword and you will get a ton of results in a second. For sure, the link you are following may give the road to reach to your goal. Some of them are free and some are not free. The most important thing is to support enough your situations in the real world.

In this article, it's free and just shows to you 3 methods in generally such as 'New', 'Update' and 'Delete' SharePoint list item. For example, you start getting the method to use both SharePoint list and Excel file. Day by day, the Excel file has existed both old and new data because of accumulating updated by your user. In the short time, you (your team) can not stop using Excel or completely work on SharePoint, and a half of data is locally and others in SharePoint list.

Ideally is to structure your excel data and use PS script to import data correctly. In this example, the excel data is to structure for SharePoint Calendar list.

There are some definitions in the excel file:

  • The last column named "SharePoint ID" is to store the SharePoint List Item ID. If this column is empty or null (or whitespace), that means that row will be added as new item. After processed, this column will be inserted the item id automatically.
  • If the "SharePoint ID" column value is greater than zero (0), this row will be updated to SharePoint List Item.
  • If the "SharePoint ID" column value is less than zero (0), this row will be deleted and that related item is also deleted.

Note: please don't modify manually this column (SharePoint ID) to keep the data flow is processed correctly.

Note 2: please remember change the parameter in the script following your design.

Result: PS Script output

Result: SharePoint Calendar List

Code snippet

$username = "YOUR_LOGIN_NAME" 
$password = "YOUR_PASSWORD" 
$url = "YOUR_SHAREPOINT_SITE_URL"

$datafile = "YOUR_EXCEL_FILE_FULL_PATH"

$securePassword = ConvertTo-SecureString $Password -AsPlainText -Force 

Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll" 
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" 
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.WorkflowServices.dll"

#connect/authenticate to SharePoint Online and get ClientContext object.. 
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($url) 
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword) 
$clientContext.Credentials = $credentials 

$web = $clientContext.Web
$lst = $web.lists.GetByTitle("Calendar")
$clientContext.load($lst)
$clientContext.Load($web)
$clientContext.ExecuteQuery()

#Load data from Excel file
$xl = New-Object -COM "Excel.Application"
$xl.Visible = $false

$wb = $xl.Workbooks.Open($datafile)
$ws = $wb.Sheets.Item(1)

#Start row at #2, @1 is header
$intRow = 2

do {

 # First Column: Title
 $title = $ws.Cells.Item($intRow, 1).Value()

 # Second Column: Location
 $location = $ws.Cells.Item($intRow, 2).Value()

 # Third Column: Start Time
 $starttime = $ws.Cells.Item($intRow, 3).Value()

 # Third Column: End Time
 $endtime = $ws.Cells.Item($intRow, 4).Value()

 # Third Column: End Time
 $description = $ws.Cells.Item($intRow, 5).Value()

 $spid =   $ws.Cells.Item($intRow, 6).Value()

 if ([string]::IsNullOrEmpty($spid) -or [string]::IsNullOrWhiteSpace($spid)) {
    $spid = "0"
 }


 if ([int]::Parse($spid) -gt 0)
 {
    #Update
    $lstitem = $lst.GetItemById($spid)
    $lstitem["Title"] = $title
     $lstitem["Location"] = $location
     $lstitem["EventDate"] = $starttime
     $lstitem["EndDate"] = $endtime
     $lstitem["Description"] = $description
     $lstitem.Update()
     $clientContext.Load($lstitem)
     $clientContext.ExecuteQuery()
    Write-Host 'New item ['$lstitem.id'] has been updated successful!'
 }
 elseif ([int]::Parse($spid) -lt 0)
 {
    $lstitem = $lst.GetItemById([math]::Abs($spid))
     $lstitem.DeleteObject()
     $clientContext.ExecuteQuery()
    Write-Host 'The item ['$spid'] has been deleted successful!'

    #delete entire row in excel
    [void]$ws.Cells.Item($intRow, 1).EntireRow.Delete()
 }
 else
 {
    #Add New
    #Create Calendar Item
     $info = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
     $newitem = $lst.AddItem($info)
     $newitem["Title"] = $title
     $newitem["Location"] = $location
     $newitem["EventDate"] = $starttime
     $newitem["EndDate"] = $endtime
     $newitem["Description"] = $description
     $newitem.Update()
     $clientContext.Load($newitem)
     $clientContext.ExecuteQuery()
     $ws.Cells.Item($intRow, 6) = $newitem.id
     Write-Host 'New item ['$title'] has been added successful! ID=[' $newitem.Id ']'
 }
 
 

 # Move to next row
 $intRow++

} While ($ws.Cells.Item($intRow,1).Value() -ne $null)

Write-Host 'Close all connections!'
$wb.save()
$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
Write-Host 'Done!'


The PS script works only for SharePoint Online. If you need to work with SharePoint on-premise, please comment for request.
Credit: the headline image is downloaded from the internet.




















































Comments








Post a Comment