As a part of my project to convert a Lotus Notes system to something else, I am making good use of SharePoint Lists to replicate the functionality of some of the less complex Notes applications.
For some of these databases, I am exporting the documents into Structured Text, then converting the structured text to Excel spreadsheets.
A particularly troublesome error occurred when I was trying to copy a large number of rows from an XLSX file into the Datasheet View of a SharePoint 2010 List. I had originally copied several thousand rows into the List, only to realize that I had accidentally selected the wrong range of columns (which made for some interesting looking data when the data types were dissimilar). To fix this problem, I deleted the rows from the Datasheet View, and then attempted to copy-and-paste them again – this time using the correct range.
At this point, SharePoint gave me an several different errors indicating that an incorrect number of columns had been selected, that some cells were read-only, and that pasted data would go off the sheet.
After verifying multiple times that I had indeed selected the right number of columns and the correct columns in the Excel file, I finally decided to reload the Datasheet View in the browser with F5.
Voila, no more error when trying to paste the data. I do not know if this is a “known issue” or not, but it seems that reloading the Web page after deleting data from the Datasheet View is necessary prior to attempting to load new data.
I did find a good site for tips about copying from Excel into a Datasheet View.