Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by
  • When: 31 Jul 2013 10:27 AM
  • Last revision by (cMVP, Microsoft Community Contributo)
  • When: 18 Sep 2013 10:59 AM
  • Revisions: 3
  • Comments: 2
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  SharePoint 2010: Importing Data from an Excel File into a SharePoint List Using PowerShell

SharePoint 2010: Importing Data from an Excel File into a SharePoint List Using PowerShell

SharePoint 2010: Importing Data from an Excel File into a SharePoint List Using PowerShell

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. 

In steps PowerShell!

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
 
#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?



, , , , , [Edit tags]
Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 18 Sep 2013 10:59 AM

    Richard Mueller edited Revision 1. Comment: Removed extra space in tag "SharePoint  2010"

  • 31 Jul 2013 10:30 AM

    Matthew Yarlett edited Original. Comment: Updated title.

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 31 Jul 2013 10:30 AM

    Matthew Yarlett edited Original. Comment: Updated title.

  • Posted by
    on 18 Sep 2013 10:59 AM

    Richard Mueller edited Revision 1. Comment: Removed extra space in tag "SharePoint  2010"

Page 1 of 1 (2 items)