Some time ago I was working with an Excel-loving crowd who had to share their data in a SharePoint list.
I wanted to make the transition as smoothly as possible, so I created a special “Data Import View” (a Datasheet View) to make it as easy as possible to copy their Excel data into the list.
There were too many people involved with too many different PC configurations, to trust a Data Connection. So a one-time copy-paste action it had to be!
After some tests I gave them the following instructions:
1. Sort the columns in your Excel sheet in exactly the same order (left-to-right) as the Data Import View (I listed all columns in the correct order)
2. Use the exact same words, spelling and capitalization as the values in the dropdown fields in the SharePoint list.
I listed all the words – we used “color” and not “colour”, for instance.
If you use different capitalization in a Choice column (e.g. “Preferred Supplier” instead of “Preferred supplier”) the Choice column will accept your value without error message, but when you edit the item, the value will change to the first of the Choice values.
3. Check your cells have exactly the same formatting as the SharePoint list.
Sometimes text fields gave errors while copying – copying the text from the dropdown field and paste that into the Excel file often solved that.
4. Use the correct date-and-time format. (I had to test a few different notations to know what worked and what not in our multinational organization)
5. Select the first (utter left) cell before you start pasting. (The Data Import View contained many columns, and needed horizontal scrolling to see all columns, and sometimes people forgot to scroll back).
6. Start pasting only when the first cell is ready. It shows a bold line around it.
7. Do a test-run with one line. Adjust your data and check again when you encounter issues.
Next time your users say that “they can not copy -paste their Excel data into a Datasheet View”, these tips may point you towards a solution.
I could have made things slightly easier by using a single-line-of-text for each column. Single-line-of-text is like bloodtype AB: it can receive anything :-). But because I had originally designed the list for adding new items via “New Item” in the Standard View we had to create these instructions. And single-line-of-text columns lead to more deviations in spelling.
David Young has also found that you need to refresh your screen after deleting data and trying again.
Do you have any other tips or experienced other errors? Please share!
You may also like:
Design Fault, Scope Creep, Moving Insight or Unexpected Success? (The project that inspired this post)