HOW TO Upload a .csv file to a Sharepoint 2013 List

I recently had to write a simple Powershell script to upload Work Order data from a Maintenance Department stored in .csv format to a Sharepoint 2013 list (We wrote a cool Filemaker Go app to record the Work Orders on iPads). The script first deletes the contents of the list before uploading (Function: Delete_List). What I do not like about this approach is that the ID continues to increment and does not reset to 1. I am still looking for a solution for that.

I struggled a little bit with the DateTime formatting because I was getting an error “specified cast is not valid” but converting the date field to String solved the problem (see below).

The Benefits of Microsoft Office 365 Sharepoint

Sharepoint 365 is a program that lets small businesses stay organized.  There is no set up required because Microsoft does it for you.  This program gives small companies a way to stay connected with their employees and stay hands on.  It focuses on real project benefits and is easy to use.  It sets up a personal website that can be effortlessly customized for each business.  Any documents that are entered into Sharepoint can be viewed, edited, and managed throughout the company, at any place whether it be at home or at the office.  This program is a great entry way for small to medium size businesses.  Read on to see examples of how great sharepoint really is.

Does your company have a lot of everyday routines? Sharepoint has a system for reminders.  If a business has monthly/weekly/or daily routines that need to be reminded of, they can be entered into the Sharepoint database and the business user will be reminded when required.  Another example of how Sharepoint works is the search bar.  If a user cannot find something the search bar is one of the best around.  It brings you to anything that can be related to your search with ease.  Sharepoint is also great for keeping track of projects that are going around.  It helps sync your employees with your projects to find out what stage they are in.  Are you a small business owner that needs help organizing? Think about getting Office 365 installed.

Sharepoint 2010 Powershell Script to Replace Contents from one Column with Another in a Document Library

Recently, a Client asked me to replace the contents of one column in a Document Library with another. Although I could have done this in Dataview using Copy/Paste I decided to use Powershell because we also wanted to manipulate the value of the field before saving it in the new column and we had thousands of .pdf and .docx files, which is faster to process in Powershell.
There were two columns that needed to be transferred
  • “Name” to “DocNR”, stripping the file extension “.pdf” or “.docx” first.
  • “Title” to “Description0”
It is important to use the “internal” column name in this script and not what you see in the Sharepoint web interface.
The task was made easier due to the fact that I only had three subfolders, so I decided to run the script three times, each on a different subfolder. Maybe not the most elegant solution, but a hell of a lot simpler than enumerating all of the subfolders with Powershell.
Also, version control needs to be turned off for this script to work.

[sourcecode language=”powershell”]
$spSite = Get-SPWeb -Identity https://myspserver/mysite
# Get the Folder
# Pass 1
$spFolder=$spSite.GetFolder(“doclibrary/subfolder1”)
# Uncomment for Pass 2 and 3 respectively
# $spFolder=$spSite.GetFolder(“doclibrary/subfolder2”)
# $spFolder=$spSite.GetFolder(“doclibrary/subfolder3”)
$spFileCollection=$spFolder.Files
ForEach ($file in $spFileCollection)
{
$docName = $file.Item[“Name”].ToUpper()
$doctitle = $file.Item[“Title”]
# Strip the extension
$dot = $docName.Indexof(“.PDF”)
if ($dot -eq -1)
{
$dot = $docName.Indexof(“.DOCX”)
}
$short = $docName.SubString(0,$dot)
# Log on the console for reference
Write-Host $doctitle, $docName+”:”$dot,$short
$file.Item[“DocNR”] = $short
$file.Item[“Description0”] = $doctitle
# Log on the console for reference
Write-Host $file.Item[“DocNR”]”, “$file.Item[“Description0”]
$file.Item.Update()
}
$spSite.Dispose
[/sourcecode]

HOW TO Remove Sharepoint 2010 Users and Groups Using Powershell

A recent project required me to remove several thousand obsolete users and groups from a Sharepoint 2010 Site. Instead of performing this task manually I wrote the two scripts below, which may come in handy for anyone performing a similar task.
I also built in a condition to retain groups that started with “CompanyName” (e.g. “CompanyName Extranet Members”) and user accounts that started with “COMPANY\” (e.g. “COMPANY\donald.duck”).
Here are the scripts:
Powershell Script to Delete Groups
$webURL="https://SharepointSite"
$site = new-object Microsoft.SharePoint.SPSite($webURL)
$web = $site.OpenWeb()
$groups = $web.sitegroups
$grctr=1
$GroupsToDelete = @(100)
foreach ($spgroup in $groups)
{
if ($spgroup.Name.Startswith("CompanyName"))
{
write-host "Name : ",$spgroup.Name
}
Else
{
$GroupsToDelete += $spgroup.Name
$grctr=$grctr+1
}
}
foreach ($delgroup in $GroupsToDelete)
{
write-host "Deleted : ",$delgroup
$web.SiteGroups.Remove($delgroup)
$web.Dispose()
}
write-host "Number Deleted: ",$grctr

Powershell Script to Delete Users in Sharepoint
$webURL="https://SharepointSite"
$site = new-object Microsoft.SharePoint.SPSite($webURL)
$web = $site.OpenWeb()
$webusers = $web.siteusers
$grctr=1
$UsersToDelete = @(100)
foreach ($spuser in $webusers)
{
if ($spuser.UserLogin.Startswith("COMPANY\"))
{
write-host "Name : ",$spuser.UserLogin
}
Else
{
$UsersToDelete += $spuser.UserLogin
#  write-host "Delete : ",$spuser.UserLogin
$grctr=$grctr+1
}
}
foreach ($deluser in $UsersToDelete)
{
write-host "Deleted : ",$deluser
$web.SiteUsers.Remove($deluser)
$web.Dispose()
}
write-host "Number Deleted: ",$grctr

Sharepoint Foundation 2010 on Amazon EC2

Amazon EC2 is a great environment when you need to set up a test server. You can do it in a matter of minutes! And, as long as you turn off the server when it is not in use, it is super cheap. But there are some gotchas.

Case in point: I recently used EC2 to set up a Sharepoint Foundation 2010 test server for a Client. All went well until I shut down the server and then the next day I started up and ran Sharepoint Products Configuration Wizard and it threw and exception similar to this one:

04/19/2011 10:38:35  13  ERR                Task adminvs has failed with an unknown exception
04/19/2011 10:38:35  13  ERR                Exception: System.IO.FileNotFoundException: Could not find file ‘C:\inetpub\wwwroot\wss\VirtualDirectories\42539\web.config’.

The solution was quite simple.

  1. If you already installed and the error above was thrown, uninstall Sharepoint Foundation 2010
  2. Open config.xml in C:\Program Files\Amazon\Ec2ConfigService\Settings
  3. Change tag <State> from “Enabled” to “Disabled”
    <Name>Ec2SetComputerName</Name>
    <State>Disabled</State>
    </Plugin>
  4. Change the computer name
  5. Reboot
  6. Install Sharepoint Foundation 2010