Data loading from excel to list - lesson learnt

Scenario:
Client wanted to move his data from old system to SharePoint lists. We didn't have direct access to the external system but administrator was able to dump all the information in Excel sheet. So we were suppose to load that in SP Lists.

Lesson Learnt:
Excel is very powerful as it provides filtering , bulk edit , pivot and many more features. So massage the data in excel as much as you can before you attempt to load it to SharePoint.

On the other side we had DataSheet View which is almost excel , so copying multiple records is easier that way.

Steps:

1. Create a mapping document as it will be helpful to identify columns in excel > columns in SP List

2. Identify any missing columns

3. Identify required fields in SP List and using excel filter make sure that column doesn't contain Blank / None values

4. Visit List setting > column setting to check default values of columns. While copying SharePoint will assign Default value to these columns , which you may not like.

5. Create a datasheet view with columns in same sequence ( Left to Right ) as they are in excel. This will be helpful in copying data quickly.

6. Text Columns - Usually its easy to copy data but make sure length of text is max. 255

7. Choice Columns ( Single Selection )- This is tricky. Either have all the possible choices pre-configured , other wise data will not be copied properly. Other option which I will recommend is to have a text column and copy over data into it. Once done , convert text column to Choice Column

8. Choice Columns ( Multi-Selection ) - SharePoint uses '#;' as separator for multi-select values + Another issue is it doesn't like separator if there is only one option selected. So make sure you have proper text in excel before you attempt to copy.

9. Lookup Columns ( Single Selection )- This is tricky. Either have all the possible option in look-up list , other wise data will not be copied properly.

10. Lookup Columns ( Multi-Selection ) - SharePoint uses '#;' as separator for multi-select values. So make sure you have proper text in excel before you attempt to copy.

11. Yes / No Columns - Make sure that you dont have 'None'/NA etc in source excel for this columns. Yes / No / Blank are only allowed values.

12. We had 20+ columns and 10,000+ rows. Moving all the data in one shot was not possible because of clipboard limitations and other issues. I will recommend copying 1 column fully

Validations:

1. You can check item count from 'View All Content' , each list will show list of items.

2. If you have some Choice column / Look-up Column, you can export the SharePoint list in excel and do a pivot to see same number of item exist in original excel.