There's no two ways about it, SharePoint has not been fun for me. I don't know if it's because it's a monster or if I just don't enjoy dealing with it. What I can tell you is that it seems like there's some type of issue at every corner I turn and every time I try to do something simple it seems to take 2x or 3x the amount of work it seems like it should. With that said, using PowerShell with SharePoint was a blast.

While converting data, I was faced with the task of moving over a bunch of files from a file system into SharePoint. That's easy enough using Windows Explorer from file system to SharePoint, just drag and drop. However, I then needed to append some properties onto the newly imported files and I have the needed information in a csv file.

After some testing of the file, I found that I needed to convert it to a tab delimited file because there were some commas in the data and I didn't see a way to handle data wrapped in quotes with PowerShell. After figuring this out the import went fairly smooth. Here's the code I used to do the importing:

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

#some temp variables if we want to update other lists later
$SharePointSite = "http://sharepoint/"
$SharePointWeb = "Site"
$SharePointList = "ListName"
#where the file you're going to read data from to get the info is located
$workingdir="C:\Powershell\data.txt"

#make the connection to sharepoint and to the list
$spSite = new-object Microsoft.SharePoint.SPSite($SharePointSite)
$spWeb = $spSite.OpenWeb($SharePointWeb)
$spList = $spWeb.Lists[$SharePointList]
$spItems = $spList.items | Sort -Property Name

#import a csv file with a tab delimiter
$lines = Import-Csv -Path $workingdir -Delimiter `t

$getLine = 0
#loop through all of the lists
#update each one from the file we generated from SQL
foreach ($spItem in $spItems)
{
    $line = $lines[$getLine]

    if($spItem["Name"] -eq $line.name + ".wmv")
    {
        $spItem["Title"] = $line.title
        $spItem["Category"] = $line.category
        $spItem["Scheduling Start Date"] = $line.date

        #write-host $line.name
        #write-host $spItem["Title"]
        #write-host $spItem["Category"]
        #write-host $spItem["Scheduling Start Date"]
        #write-host "-----------------------------------"
        $spItem.Update()
        write-host $getLine
    }
    else
    {
        write-host "Failed to update because name didn't match:"
        write-host "name from file: " + $line.name
        write-host "name from sp: " + $spItem["Name"]
        write-host "line: " + $getLine
        write-host "-----------------------------------"
    }
    $getLine++;
}


I also needed to do some importing into a list from that same file. Much of the code is the same but here's what I changed:

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

#some temp variables if we want to update other lists later
$SharePointSite = "http://sharepoint/"
$SharePointWeb = "Site"
$SharePointList = "List"
#where the file you're going to read data from to get the info is located
$workingdir="C:\Powershell\data.txt"

#make the connection to sharepoint and to the list
$spSite = new-object Microsoft.SharePoint.SPSite($SharePointSite)
$spWeb = $spSite.OpenWeb($SharePointWeb)
$spList = $spWeb.Lists[$SharePointList]
$spItems = $spList.items | Sort -Property Name

#import a csv file with a tab delimiter
$lines = Import-Csv -Path $workingdir -Delimiter `t

$getLine = 0
#loop through all of the line and insert a new item
foreach ($line in $lines)
{
    $spListItem = $spList.AddItem()
    $spListItem["Subject"] = $line.name
    $spListItem["Body"] = $line.title 
    $spListItem["Last Updated"] = $line.date
    $spListItem.Update()

    write-host $getLine
 
    $getLine++;
}

Two things to note with the scripts:
  1. Both scripts have a counter that is output to tell how much data is inserted and also to give an idea of the progress
  2. The looping done in the update script used sorting to make sure that the names would match but also checked that the names matched to make sure.