Ravendra's Blog

Uploading CSV/ Excel data into sharepoint list


You can upload excel/ CSV data in existing sharepoint list using following powershell script.  

Paste following powershell script in the PowerGUI script editor (or any other powershell editor you are using). Change following values to suit your stuff

1.      csv path/ filename,

2.      weburl

3.      list name and

4.      List column names and CSV cell names

Execute the script and that’s it.

# CSV path/File name

$contents = Import-Csv ‘C:\ShipDataMonthly.csv  

# Web URL

$web = Get-SPWebwebUrl ‘http://hweb22/Phase2′  

# SPList name

$list = $web.Lists[ListName] 

# Delete
existing List Data, If not required remove this line

$list.get_items() | % { $_.Delete() } 

# Iterate for each list column

foreach ($row in $contents)

{

      $item = $list.items.add()     

      # Check if cell value is not null

      if ($row.CsvCell1 ne $null)

            {$item[“ListColumn1”] = $row.CsvCell1}     

      if ($row.CsvCell2 ne $null)

            {$item[“ListColumn2”] = $row.CsvCell2}     

      if ($row.CsvCell3 ne $null)

            {$item[“ListColumn3”] = $row.CsvCell3}     

      $item.update()

Advertisements

14 Comments »

  1. Awesome, thank you

    Comment by troy — May 25, 2011 @ 9:42 pm | Reply

  2. We had to use -Identity not -webURL to get this to work in 2010

    Comment by Mark — September 8, 2011 @ 1:04 am | Reply

    • Can’t get this to work at all. Powershell complains of terminators being invalid or expected. Have used single quotes, double quotes, square brackets, single quotes within square brackets etc but to no avail. Can you show a working example if you have one?

      Comment by Pete — December 9, 2011 @ 1:23 pm | Reply

  3. When i run the script, it get blank lines imported. Any thoughts?

    Comment by Mike Bowers — November 4, 2011 @ 3:52 pm | Reply

  4. Thanks for sharing this information, I will use it as a basis for my import script to import information from excel to publishing pages in SharePoint 2010

    Comment by joranmarkx — December 7, 2011 @ 3:27 pm | Reply

  5. This is crap and doesn’t work – don’t waste your time on it

    Comment by Piertro — December 19, 2011 @ 2:22 pm | Reply

  6. Thanks for the info.but in my scenario my csv file is having metadata also so when i’m trying to uimport evrything works fine but the metadata items are empty nothing is dispalyed.Please help me out in this.Thanks in advance.

    Comment by vikram — January 30, 2012 @ 9:56 pm | Reply

  7. same here..
    When i run the script, it get blank lines imported. any updates?

    Comment by David Lim — February 11, 2012 @ 6:08 am | Reply

  8. check out http://www.braindumpblog.co.uk later today as I have sorted this

    Comment by JR52MAR — March 12, 2012 @ 1:26 pm | Reply

  9. This Script does not work. Returns empty row. Dont waste time using it.

    Comment by Navish — April 5, 2012 @ 12:11 pm | Reply

  10. Hi everyone!..
    Check following link and you will get good result.
    http://www.spjeff.com/2012/02/26/powershell-upload-csv-file-to-list-both-server-and-client-om/

    Comment by Rajesh — May 15, 2012 @ 7:08 am | Reply

  11. It’s great that you are getting thoughts from this post as well as from our dialogue made at this place.

    Comment by Verna — July 20, 2013 @ 6:06 pm | Reply

  12. Hi, you inspired me to make something similar a scheduled Task in our Environment. Thank you!

    Comment by Felix Hoffmann — September 12, 2013 @ 6:42 am | Reply

  13. Dat Smiley tho :/

    Comment by DemComment — June 25, 2014 @ 1:56 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: