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.
- Create a datasheet view of your list (in the same format as the Excel spreadsheet you are going to copy from)
- Highlight the data in your Excel spreadsheet and click copy
- 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)
- 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.