Bulk copy and paste into a SharePoint List

There is a lot of users out there who are finding a great way to get rid of a shared Excel spreadsheet is to create a list in SharePoint, and using datasheet views simply creating an online version of their spreadsheet. Various users can then have access to it, and can edit and add data to the online spreadsheet. Using the datasheet, users can also export the data to Excel or Access where they can do further reporting or charting on the data.

One limitation I thought it had was the ability to bulk add data to a SharePoint list – I thought even in the datasheet view you could only add data one row at a time. This is not the case (and maybe I should have read the documentation a little more…). It is completely possible to bulk add data (at any time) to a SharePoint list by using the datasheet view.

Simply

  1. Create a datasheet view of your list (in the same format as the Excel spreadsheet you are going to copy from)
  2. Highlight the data in your Excel spreadsheet and click copy
  3. In your datasheet view, click the first box next to the * in the bottom row, and then paste (This is the only real tricky bit – make sure you DO NOT select the entire row. Just the first box)
  4. And lo and behold, the data magically pastes into your datasheet

Of course there are a couple of gotchas. Here is some more information on that (straight from the Access Web Datasheet Help):

When you paste the contents of the Clipboard, new rows will be added to the bottom of the Datasheet view if necessary. However, if the view does not have sufficient columns, the paste operation will fail. A paste operation will also fail in the following situations:

  • Some or all of the destination cells are read-only
  • Pasting the contents into the selected cells will make them fail the validation rules
  • Attempting to paste data that does not match the data type of the destination cells
  • Attempting to paste data into a calculated column

When you attempt to paste data of one type into a column of a different type, the list will try to convert the source data to match the type of the destination column. For example, if the source data is numeric, and the destination cells support text, source data will be converted to text and then placed in the destination cells. If the source data cannot be converted to match the destination cell type, the paste operation will fail.

About these ads
This entry was posted in SharePoint. Bookmark the permalink.

14 Responses to Bulk copy and paste into a SharePoint List

  1. kimboucher says:

    this appears to work then when I move to regular view, the rows were never saved. I think there is a setting somewhere I need to change. any ideas?

  2. Vivek says:

    When in the datasheet view, click on the actions tab. A drop-down shall appear, select refresh data, which shall receive all the changes made in the datasheet. Change the view to standard, using the actions tab again, Voila…It is Done!! :)

  3. ST says:

    I have 3 lists on Sharepoint. Is it possible to create 1 list which consist of these 3 lists info in it? Thanks.

    • Yes, you should definitely be able to copy information from each list and paste it into a single list. You will just have to do them one at a time and ensure that all lists are setup with identical views of the data.

      • Candy says:

        May I ask, when u create a Web Part which consist of different datas, let’s say “Sales contacts” and “Distributors contacts”, and you want to minimize those datas and only let it appear/dropdown the details when clicked, depending on what the user want to see. Is it possible? or any alternatives? Thanks v much!

      • There is no easy solution to this problem. I think you might have to look at custom development as you are going to have to manage the state of the expanded details etc based on what a user is selected.
        If you are creating a custom web part I would bind each of the datasources seperately and have them bound to expandable controls, and save the state of each control based on the current user.
        That should solve your problem

  4. Nafia says:

    How many records can be copied from the excel to sharepoint list data sheet view. I currently have data with 255 columns and 100000 records.

  5. When I attempted this, I got a message saying “You cannot perform this operation on an attachment field.” Any ideas? The attachment field IS first box next to the * in the bottom row. Thanks.

    • Anwesha says:

      I got the same error as well. Which box do you mean beside *? I can see only the attachment field and it does not paste. Can you elaborate since this is very important!!! Thanks.,

      • Anwesha says:

        Gosh! I just figured it out. It is the first column of the item and not the attachment. PERFECT! Thank you

  6. Amy says:

    just wanted to thank you for this original problem/solution post. I googled this very topic this morning and yours came back 3rd in my results. Third time was the charm, I was able to successfully copy paste data into the original list I’d created. For a non-techie, I’m feeling like a rockstar, THANKS again!

  7. angelhaze1 says:

    This was very helpful! I am creating a glossary in Datasheet view and needed to copy/paste from Excel. It pasted the first time but I discovered that one of my columns was designated as a single line of text, which doesn’t work when your definition for a word in a glossary is several lines. So I created a new column with mulitple lines of text selected. Now when I go to paste the content from Excel into the datasheet, it is stating that the ‘Selected Cells are Read Only’. I didn’t make them read only and it worked the first time so what is preventing it from working now? How do I modify the column so that it is Read/Write? Thanks for your help.

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