Tuesday, January 7, 2014

Import a CSV file into a SharePoint List

I borrowed parts of this script from so many different sites, I don't even know who to give credit to anymore.  However, rest assured that I stole most of this.  Now, in the great tradition of coding, it is mine, all mine. Until you steal it.  Then it will be yours.  I have added my own comments in places.  In others, they are stolen.  Change the items in blue for this to work for you.

What this script does:
  1. Checks for a CSV file on a network location.  If it exists, then it runs.  If not, it exits.
  2. Checks the number of items in a list, displays the site name, the number of items, then deletes all items.
  3. For each row in the CSV, adds an item to a SharePoint list
  4. Renames the file when the process is done.

I set this up to run every 15 minutes through a Windows task so that users could place files in a network location and the job would run automatically.

#specify file (create variable for it) and check to see if it exists.  If it does, continue.  If not, exit 
$Filecsv = Import-CSV "\\fileshare\folder\FileName.csv"
if ($Filecsv) {"File exists"} else {exit}

#Add SharePoint PowerShell Snapin which adds SharePoint specific cmdlets
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

#specify variables needed.  The webURL should be the site URL, not including the list
#the listName should be the
$WebURL = "http://sharepoint/sites/mysitename"
$listName = "mylistname"
#Get the SPWeb object and save it to a variable
$web = Get-SPWeb -identity $WebURL
#Get the SPList object to retrieve the list
$list = $web.Lists[$listName]

#START deletes all items. code shows the number of items in a list, then deletes all items  
#If you don't want your script to delete items, then remove this
 $site = new-object Microsoft.SharePoint.SPSite ( $WebURL )
 $web = $site.OpenWeb()
 "Web is : " + $web.Title

# Enter name of the List below instead of
$oList = $web.Lists["mylistname"];
"List is :" + $oList.Title + " with item count " + $oList.ItemCount

$collListItems = $oList.Items;
$count = $collListItems.Count - 1
for($intIndex = $count; $intIndex -gt -1; $intIndex--) {        
"Deleting record: " + $intIndex        
#END Deletes all items

#goes through the CSV file and performs action for each row
foreach($row in $Filecsv)
#Create new item
$newItem = $list.items.Add()
      $item = $list.items.add()     
      # Check if cell value is not null in excel
      if ($row."CSV column Name" -ne $null)
            # Add item to sharepoint list.  for this one, I had to use the internal column name. 
            #You don't always have to, but I had trouble with one SharePoint column, so I did
            {$newItem["sharepoint_x0020_Column_x003f_"] = $row."CSV column Name"}
      if ($row."CSV column Name2" -ne $null)
            {$newItem["sharepointcolumn 2"] = $row."CSV column Name2"} 
      if ($row."CSV column Name3" -ne $null)
            {$newItem["sharepointcolumn 3"] = $row."CSV column Name3"} 
      if ($row."CSV column Name4" -ne $null)
            {$newItem["sharepointcolumn 4"] = $row."CSV column Name4"}

# Commit the update, then loop again until end of file         

# get the date and time from the system
$datetime = get-date -f MMddyy-hhmmtt
# rename the file
rename-item -path "\\networklocation\folderl\FileName.csv" -newname ("FileName_Processed_" + $datetime + ".csv")

No comments:

Post a Comment