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.


  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.

This entry was posted in SharePoint. Bookmark the permalink.

22 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.

  8. Ann B says:

    Is there anyway to customize the error when copying and pasting multiple rows-so it tells you what row it did not import and what the error might be?

  9. Teresa says:

    I have followed the above ideas and after multiple tries I am still stuck. I am working in SP 2013. My Excel 2013 list is 10 columns of text, and I’ve made them single line text (except A2 which is plain text multiple lines). I’ve cleaned the spreadsheet of formatting and word-wrapping and the headers. Paste gets two responses, one is “format of the clipboard data is not recognized”, the other response is that it only pastes the data from A2 into the first field of the datasheet ignoring A1 and all other copied data. I also deleted the Dataview list and started over a couple of times.

  10. Teresa says:

    Slight correction to the above. Column 2 is the multiple lines of text, not cell A2.

    • Might definitely be multiple lines of text. I think it often recognises the carriage return as a new line in 2013. As Matt said in earlier post best way is to make the multiple lines plain text. http://devblog.bardoloi.com/2012/08/sharepoint-2010-datasheet-view-selected.html

      • Teresa says:

        Yahoo! I figured it out (and have tested 3 times). In SP 2013, use the “Add an app” feature from the Gear dropdown to access the “Import from Excel” app. It creates the SP list with the fields in your spreadsheet. You have to prepare the data using the Excel feature “Format as a Table” on the Design tab the spreadsheet. I also removed all Excel formatting, word wrap, etc.- just in case! Multiple lines of text worked including the blank lines. Oh, and the import even brought over URL links!. Again, this spreadsheet was all text – no calculations or look ups. Once I got it into SP, I changed the fields (one for Y/N, another for Date, and two others to multiple choice with dropdowns). It took all day to figure this out and, WOW, what a high when it finally worked!

  11. jess says:

    thank you ๐Ÿ™‚

  12. Lauren says:

    With the list I’m working in (task list), I don’t have any issues with the multiple lines (they are not set to enhanced), but I did experience issues with the Choice fields. I think I’ve resolved the “cell is read only” pasting issue by setting “Allow ‘Fill-in’ choices” to Yes within Change Column under List Settings. I did a quick test by copying and pasting the first item to a new item and it worked, but that’s not a full test. Does anyone know if this will resolve the issue? I’d prefer not to keep testing in this list and mess up the ID numbering. Thanks!

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s