One of my biggest headaches with developing and testing in SharePoint is my lack of test data to work with. Sometimes I need to generate a load of test data so I can perform searches or test performance on custom code. Like in this blog post, I needed to load up a bunch of fake data so I could walk through an example.
To resolve this issue I made a quick PowerShell script which will read a CSV file and import the data I need. The script needs to be customized each time, but it’s pretty easy. You can download the script called Import-DataIntoSharePoint from my CodePlex project.
This script can also be used to automate importing data into SharePoint. Assuming you have an external system which is exporting to CSV, this script can be scheduled via Windows Tasks to run and import the data on a periodic basis.
First, create your CSV file. How? Open Excel, and load your columns and rows of data. It’s easiest to make the first row your column titles, and have the column titles match the fields in the SharePoint list you’re importing into. It’s ideal not to use spaces in the names as well.
Now load up your data! The values have to be the same as SharePoint would expect. For most fields that’s pretty easy. Text and numbers can be entered as is. Dates should include the time, i.e. 1/20/2012 11:00 AM. Choice fields work like text fields. User and lookup fields are unique, those should be formatted like ID;#Title, i.e. for a user 2;#David Lozzi, or for a lookup 34;#Request for PO. This part can be tricky. Check out my post on Using PowerShell to play with SharePoint Items to better understand the data format.
After you’ve loaded up your data, save the Excel file as a CSV file, and put it in the same folder as the downloaded script.
Open the script up in NotePad (I prefer NotePad++). Make the following modifications as needed
- Set your web’s URL, i.e. http://servername, or http://servername/site/site
- Set your lists’s name, i.e. Calendar, Tasks, My List
- Set your CSV file name
- Add all fields you wish to add. The $new[ is your SharePoint field name, the $i. is your CSV file column name.
Now save it and run it! With any luck you’ll get a nice response like
RSS - Posts
I’ve added a new column (UID). Here’s what I have so far:
if((Get-PSSnapin | Where {$_.Name -eq “Microsoft.SharePoint.PowerShell”}) -eq $null) {
Add-PSSnapin Microsoft.SharePoint.PowerShell
}
$web = Get-SPWeb http://SP01/Maint
$list = $web.lists[“csvtest”]
$cnt = 0
foreach($i in Import-CSV C:\WebApplistcsv.csv)
{
$new = $list.Items.Add()
$new[“Problem”] = $i.Problem
$new[“Linenumb”] = $i.Linenumb
$new[“Assigned”] = $i.Assigned
$new[“Priority”] = $i.Priority
$new[“UID”] = $i.UID
$new.Update()
$cnt++
}
“Added ” + $cnt.ToString() + ” records.”
“Done. Press any key to continue…”
$null = $Host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)