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 currently don’t have a unique column. I will add one to the CSV file.
I work for a manufacturing company. There is a an app that the workers use submit a sort of ticket when something breaks. The data is placed in a CSV file. I’m trying to setup Sharepoint to pick up the CSV data and put it in a task list for the Maintenance team. The CSV file isn’t very large. I was planning to have the script run every 15min
So there needs to be value or column to use to identify which records to update, and which records are considered new
Hi David,
I found your article very helpful. I have the script setup to import data from a CSV file.
Is there a way to have it compare the CSV file with the sharepoint list and only upload new entries on the CSV file to sharepoint?
Absolutely. You can take each unique identifier from the CSV, like a column or two that should be unique in SharePoint, and query the list for it. If it exists, move on. Depending on how much data you have, there’s a few different ways you can do this optimally. How much data is in the list and how much do you process in the CSV?
Hi ,
we get error:
Import-DataIntoSharePoint_nordflex.ps1 : The term ‘Import-DataIntoSharePoint_nordflex.ps1’ is not recognized as the nam
e of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, v
erify that the path is correct and try again.
At line:1 char:1
+ Import-DataIntoSharePoint_nordflex.ps1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Import-DataIntoSharePoint_nordflex.ps1:String) [], CommandNotFoundExcep
tion
+ FullyQualifiedErrorId : CommandNotFoundException
what are we doing wrong?
This is usually due to a typo or you’re not in the same directory as your script. Also, you’ll need .\ before your script name to run it, like .\Import-DataIntoSharePoint_nordflex.ps1
given code is not working on SharePoint online??
This won’t work on O365. This requires to be run on a SharePoint server.
Hi ,
I have number field along with percentage(40%) . I am not able to move this fields from csv to SharePoint list .
I am getting error as “Exception calling Update with 0 arguments: Invalid number value.”
How do I fix this?
Hi,
Thanks for great explanation. I have a query where i need to read a CSV file with column named “ID” which contains more than 1000 unique ids.Then i need to compare each id from CSV with a Sharepoint list and if ID value is available in sharepoint List then i need to append a text “Invalid” for entire row available for this ID
Hi,
You’ll need to perform a CAML query on the list to find each ID. Something like:
foreach($i in Import-CSV YourCSVFile.csv)
{
$caml = New-Object Microsoft.SharePoint.SPQuery
$caml.Query = “” + $i.ID + “”
$items = $list.GetItems($caml)
$items.count
}
The above is quick and dirty, but shows how you can loop through your Excel file, then query the list looking for your value. $items.count should return a 0 if no items exist. If one does, you can grab $items[0] to get the first list item and then update it (see other blog posts on that). Let me know how it goes!
Sorry for delayed response..it worked great with caml query and i was able to append the text to existing value. Thanks for your help
Hi,
I am receiving “Cannot index into a null array” errors but I have created the custom list with headings to match the script. How do I fix this?
You can email me your script and Excel file and I can take a look. david.lozzi at slalom.com
please can I use .xls or .xlsx instead of csv ?
Hi Jasmin,
You can read an xls file, check out http://blogs.msdn.com/b/sriram_reddy1/archive/2012/07/16/excel-with-powershell.aspx, great walkthrough on how to do so.
Thank you for this simple yet great way to achieve this, but my question is as follow:
I made a scenario to make a list of all users’ profiles, with a csv of them, updated weekly, can the image of the user be shown as an image or will it show a path? knowing that it’s working on their profiles, after being imported then populated from Active Direcory
I’m not sure I follow, do you want the pic to appear in the csv file instead of the path? Seeing your other question on using an xls, I’m guessing this is the case and as you may have guessed by now, you cannot show images in a csv file. A csv is simply a text file of comma separated values.
Here is my current script and I am getting several errors, including:
——————————————————
Add-PSSnapin: No snap-ins have been registered…
The term ‘Get-SPWeb is not recognized…
Cannot index into a null array. (several occurences)
You cannot call a method on a null-valued expression. (several occurences)
Any ideas?
———————————————
if((Get-PSSnapin | Where {$_.Name -eq “Microsoft.SharePoint.PowerShell”}) -eq $null) {
Add-PSSnapin Microsoft.SharePoint.PowerShell
}
$web = Get-SPWeb http://psenet/projects/eastside230kv
$list = $web.lists[“P6 Calendar”]
$cnt = 0
foreach($i in Import-CSV C:\P6_Export\P6Calendar.csv)
{
$new = $list.Items.Add()
$new[“Title”] = $i.Title
$new[“StartDate”] = $i.StartDate
$new[“EndDate”] = $i.EndDate
$new.Update()
$cnt++
}
“Added ” + $cnt.ToString() + ” records.”
“Done. Press any key to continue…”
$null = $Host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)
Where are you running this? It’ll have to be run on a SharePoint server.
I did notice that I had a slash incorrectly in my .csv path name and fixed it. Still not getting records to display.
Our goal is to do an export from our project management software daily, and overwrite existing records each day with new/updated tasks. We don’t want to append each day. I’ve automated the export and formatting of the .csv file, I just need to get PowerShell to do the daily import of all records now.
Here is my code:
——————————————————-
if((Get-PSSnapin | Where {$_.Name -eq “Microsoft.SharePoint.PowerShell”}) -eq $null) {
Add-PSSnapin Microsoft.SharePoint.PowerShell
}
$web = Get-SPWeb http://psenet/projects/eastside230kv/lists
$list = $web.lists[“P6 Calendar”]
$cnt = 0
foreach($i in Import-CSV C:\users\sfinne\P6_Export/P6CalendarExport.csv)
{
$new = $list.Items.Add()
$new[“Title”] = $i.Title
$new[“StartDate”] = $i.StartDate
$new[“EndDate”] = $i.EndDate
$new.Update()
$cnt++
}
“Added ” + $cnt.ToString() + ” records.”
“Done. Press any key to continue…”
$null = $Host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)
Thank you. I was able to correct the problem and ran the script. It populated my list the first time, but when I then added a record to the .csv file, reran the PowerShell script, but it did not update my list with the new record. I deleted all records out of the list and tried to re-run it and it is saying it exported 7 records, but they are not in the list. Any ideas? I’m getting lots of errors referencing that “You cannot call a method on a null-valued expression,” and “Cannot index a null array.” Thanks for your assistance.
Can you tell me how you formatted your Date/Time columns in the .csv file? I am getting a parsing error.
I’ve used dates like
1/2/2012 1:00 PM
I created the script below (replaced “path” with actual path name), but am still getting the following error message:
———————————————————————–
Unexpected token ‘Date/Time’ in Expression or statement.
At C:\path\importP6.psi:25 char:49
+ $new[Start Date/Time”] = $i.Start Date/Time <<<<
+ CategoryInfo : ParserError:
+ FullyQualifiedErrorID : UnexpectedToken
————————————————————————
Any clue why I’m getting this error?
When I open the CSV file in Excel, it displays the date as
8/22/2013 14:00
but when I look at it in the function bar, it displays it as
8/22/2013 11:00:00 AM
If I open it in Notepad, it displays it as
8/22/2013 11:00 AM
———————————————————————————————
if((Get-PSSnapin | Where {$_.Name -eq “Microsoft.SharePoint.PowerShell”}) -eq $null) {
Add-PSSnapin Microsoft.SharePoint.PowerShell
}
$web = Get-SPWeb http://path/lists
$list = $web.lists[“P6 Calendar”]
$cnt = 0
foreach($i in Import-CSV http://path/P6CalendarExport.csv)
{
$new = $list.Items.Add()
$new[“Title”] = $i.Title
$new[“Start Date/Time”] = $i.Start Date/Time
$new[“End Date/Time”] = $i.End Date/Time
$new.Update()
$cnt++
}
“Added ” + $cnt.ToString() + ” records.”
“Done. Press any key to continue…”
$null = $Host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)
I believe the issue is the column names of Start Date/Time. Try renaming to StartDate and EndDate (in the CSV and code) and try again.
Great script and worked great for new items. However, I have a scenario where an export to .csv runs once per week. I’d like to update the list with any changes. Is this possible? There are about 10 columns and only one of these might change as well as there being new items. Is it possible for it to check for what is different and update it? Or should I run someting to delete the content of the list before the scheduled script runs?
Hi Michelle,
You have two options. The first would be to delete what’s there and reload it. That might be the easiest. The second option would be to search the list for the item from the CSV, check values, and overwrite if needed, and then add new items if they don’t exist. That would be loads more work, but probably cleaner in the long run (leaving your recycle bin happier too).
Thanks,
David
At my work we do in next schema. At update, we clear all list, and import at time job all elements again.
Hi David. How Could I add to exist list another one smaller and marge them to the one list ? I must update ID field too. Please help me.
You wish to merge the two lists together? And keep the ID of the items the same?
No the same :) I Want to marge in one big list elements from smaller secound list, and give next ID’s from biger list. And do all this by time Job.Meybe at 6:00 a’clock a.am.
I want to add one list to another exist list. Please David.
Do you want to keep the IDs the same as well? This part won’t be possible.
No no I want to take ID’s from the biger one and next increment all elements. If there are 5000, 5001,5002, 5003 and I adding from smaller one. If I add from smoller one another 3 records it should take ID’s in bigger one 5004, 5005,5006
Check out this script: https://sp2010adminpack.codeplex.com/downloads/get/682415. You’ll need to modify it to use your web, lists and columns, but it should work. It will leave the IDs as is, so as the new items are added, they’ll continue down the same numbering scheme. Let me know how it goes.
Hello David,
After running the script a bunch of items with “(no title)” were created. Have you experienced that before?
Regards,
Igor
Hi Igor,
(No Title) indicates an item was added but the Title field was left blank. Make sure you’re setting a valid value into $new[“Title”]. Make sure $i.Title, or whatever field you may be using, is valid in the CSV. Email me, david at lozzi dot net, with your script and data and I can take a look.
Thanks,
David
This is excellent. Any way we can use PowerShell to upload data from an excel file (in document library) into a SharePoint list.
Sure thing! Within the code, map a drive to the library (using net use command) and then open the Excel file. The syntax might be different for opening an Excel file vs a CSV file. i’ll check it out and probably write up a new post! Thanks!
Thanks! Looking forward to the new post!
David
Did you ever get a chance to write one to open the Excel File? I have an excel file with the extension xlsx and would like to load it to SharePoint.
Can you provide me a sample code?
Thanks
Cynthia
I haven’t. I’d imagine you’d need access to Microsoft libraries to open up that proprietary file format.
Thanks
Very helpful! Thank you!