Loading SharePoint lists from Excel using PowerShell

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

Import Data into SharePoint via PowerShell

  1. Set your web’s URL, i.e. http://servername, or http://servername/site/site
  2. Set your lists’s name, i.e. Calendar, Tasks, My List
  3. Set your CSV file name
  4. 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

Importing data into SharePoint with PowerShell

Advertisements

45 thoughts on “Loading SharePoint lists from Excel using PowerShell

  1. Shahrukh Syed

    This is excellent. Any way we can use PowerShell to upload data from an excel file (in document library) into a SharePoint list.

    Reply
    1. David Lozzi Post author

      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!

      Reply
  2. Igor Demjanov

    Hello David,

    After running the script a bunch of items with “(no title)” were created. Have you experienced that before?

    Regards,
    Igor

    Reply
    1. David Lozzi Post author

      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

      Reply
  3. grisza88

    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.

    Reply
      1. grisza88

        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.

      2. grisza88

        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

  4. Michelle Brosnan

    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?

    Reply
    1. David Lozzi Post author

      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

      Reply
      1. Stacie Finnelly

        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”)

      2. David Lozzi Post author

        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.

  5. Stacie Finnelly

    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.

    Reply
  6. Stacie Finnelly

    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”)

    Reply
  7. Stacie Finnelly

    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.

    Reply
  8. Stacie Finnelly

    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”)

    Reply
  9. jasmin974

    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

    Reply
    1. David Lozzi Post author

      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.

      Reply
  10. roggasblog

    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?

    Reply
  11. JustintoSharepoint

    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

    Reply
    1. David Lozzi Post author

      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!

      Reply
      1. JustintoSharepoint

        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

  12. Indresh

    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?

    Reply
  13. Alexander Ekberg

    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?

    Reply
    1. David Lozzi Post author

      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

      Reply
  14. Tiaz

    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?

    Reply
    1. David Lozzi Post author

      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?

      Reply
  15. Tiaz

    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

    Reply
  16. Tiaz

    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”)

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s