Introduction


There are several different ways to import data from a spreadsheet into a SharePoint list. Depending on your requirements, you can copy data into SharePoint lists using the DataSheet view, for example. However, there are some circumstances where this won't work (i.e. lack of support for certain fields in the DataGrid view), where you need to use a different approach. 

The PowerShell Approach


PowerShell can handle importing tens, hundreds, thousands, tens of thousand, even hundreds of thousands of items from a CSV. It can manipulate the data before inserting it, and it supports inserting data into all the different SharePoint field types. It can also automatically approve items (if the list requires approval of items), as well augment data with additional information (e.g. performing a lookup on the data being imported, joining it with other columns, performing validation, etc).

The basic process for importing data with PowerShell is;

Save the Excel Spreadsheet as a CSV file, get the CSV file using Import-CSV, enumerate each row in the CSV, and finally, add each row to the SharePoint list. As you iterate through the rows in the CSV file, you can perform data manipulation before saving the list item.

The examples here import a CSV called VesselInPortReport.csv, which contains data about a companies ships as they move into a shipping port.

The CSV file has the following columns (headings):

TRIP_NO
VESSEL_NAME
FLAG
AGENT_NAME
CURRENT_LOCATION
RPT_DATE
EMPLOYEE (recorded as a persons name, e.g. "Matthew Yarlett")
EMPLOYEE_TYPE (e.g. Captain, First Mate, etc)

Example: Get the CSV file, and iterate through the items, writing the "VESSEL_NAME" column for each item to the console

#Get the CSV file and connect to the SharePoint list            
$vessellist = import-csv -Path "C:\Temp\VesselInPortReport.csv"            
$itemCount = $vessellist.Count;            
$currentItem = 1;            
foreach($item in $vessellist)            
{            
    #Update the progress information            
    Write-Progress -Id 1 -ParentId 0 -Activity "Listing Data In CSV File" -PercentComplete (($currentItem/$itemCount)*100) -Status "Item $currentItem or $itemCount";            
    $currentItem++;            
    #Write the rows VESSEL_NAME column to the console            
    Write-Host $item.VESSEL_NAME;            
}


Example: Get the CSV file, and iterate through the items, adding each item as a new list item in the SharePoint VesselsInPort list. This example shows how to cast the data from the CSV file into some different SharePoint fields; Text, Number, User (Person), Choice. It displays progress information as items are added using Write-Progress.

#Get the CSV file and connect to the SharePoint list            
$vessellist = import-csv -Path "C:\Temp\VesselInPortReport.csv"            
#Get the list used to import the data to            
$l = (Get-Spweb "http://corporate").GetList("http://corporate/Lists/VesselsInPort")            
             
#Get the lists EmployeeType field (choice)            
$employeeType = $l.Fields["EmployeeType"] -as [Microsoft.SharePoint.SPFieldChoice]            
             
#Loop through the items and add them to the list            
$r = 1;            
$itemCount = $vessellist.Count;            
$currentItem = 1;            
foreach($item in $vessellist)            
{            
    Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From CSV into SharePoint" -PercentComplete (($currentItem/$itemCount)*100) -Status "Adding item $currentItem or $itemCount";            
    $currentItem++;            
    $ni = $l.items.Add();            
    #Add the Title, using the rows VESSEL_NAME column            
    $ni["Title"] = $item.VESSEL_NAME;            
                 
    #Add the "Date Recorded" field, using the csv rows "RPT_DATE" column            
    [DateTime]$rd = New-Object System.DateTime;            
    if([DateTime]::TryParse($item.RPT_DATE, [ref]$rd)){            
        $ni["Date Recorded"] = $rd;            
    }            
                 
    #Add the csv rows "TRIP_NO" column to the new list items "Trip Id" field (SPFieldNumber)            
    [Int64]$tn = New-Object System.Int64;            
    if([Int64]::TryParse($item.TRIP_NO, [ref] $tn)){            
        $ni["Trip Id"] = $tn;            
    }            
                 
    #Add some other text properties            
    $ni["Flag"] = $item.FLAG;            
    $ni["Agent Name"] = $item.AGENT_NAME;              
    $ni["Current Location"] = $item.CURRENT_LOCATION;                  
                 
    #Add user information            
    $ni["employee"] = $w.EnsureUser($item.EMPLOYEE); #In this case, the $item.EMPLOYEE value from the spreadsheet is a persons name. Eg. "Matthew Yarlett"             
    $employeeType.ParseAndSetValue($ni,$item.EMPLOYEE_TYPE); #In this case, the $item.EMPLOYEE_TYPE value from the spreadsheet is valid choice present in the EmployeeType list field. Eg. "Manager"            
                 
    #Update the item            
    $ni.Update()            
    Write-Host ([String]::Format("Added record:{0}",$r));            
    $r++;            
}


Importing data from Excel has been asked in the following forums:
How to programmatically import Excel into existing SharePoint list?
How to copy Excel sheet data to SharePoint 2010 List?


Other Languages Available


This article is also available in the following languages :