Working Around the “The format of the clipboard data is not recognized” Error When Copying Into SharePoint Lists

Howdy, Campers! Today I was working on migrating some OData from Project into a SharePoint list, to allow a client to bulk edit certain project information and still have it connected to the Project record. Although some rows succeeded in copying, I then got to a point where, no matter how carefully (or how few rows) I copied, I would get “The format of the clipboard data is not recognized”. I checked all data carefully, and saw nothing wrong.  Copying cell by cell, I found the culprits:  multi-line text fields. Further investigation revealed that these cells included non printable characters, such as when users had put in carriage returns (Alt Enter in Excel). I needed to eliminate these characters, so I duplicated each column and used the “Clean” function in Excel: =Clean(A2).

You can learn more about the Clean function here: CLEAN function – Office Support (microsoft.com)

After duplicating the columns, checking they aligned, I then hid the original columns and pasted the data from these new, scrubbed columns into SharePoint.  As soon as I did this, the rows copied to SharePoint without issue. 

Note that I still recommend copying no more than 50 or so rows at once directly into Datasheet.  For anything more than that, Access is recommended.

Best of luck! Reach out to info@ppmworks.com with any questions or comments!