Quickly adding data to SharePoint List

I was working on a SharePoint project where I needed to quickly add 100’s of list items to a list. The list was not your ordinary list. The list had the following column types.

Text
Person or Group (Show Name field)
Person or Group (Show Account)
Yes/No (default Yes)
Lookup (based on a “division” list)

This list was to store a list of approvers for a workflow application I was working on. The Person columns were used to get the Display Name and the account name of the approvers (keep in mind we use Active Directory for our user store). The Yes/No column indicated whether or not to email the user. And the Lookup column was for identifying what division the person worked for. When it came time to populate this list, the users did not want to have to type in 100’s of people into this list. So they asked me to put them in. Lucky me.

Since I’m lazy, I wanted a quick way of doing this. First, I thought about doing it programmatically, but again, I’m lazy. So, there had to be a better way. Why can’t I just use SharePoint’s native functionality? Please know that what I’m about to describe took me the better part of half a day to figure out. But once I had the process down, it only takes a few minutes to accomplish what I needed to do.

Step 1: Clean Data
The users gave me a spreadsheet of the people they wanted added. And if your users are like mine, the spreadsheet contains data that is not very conducive to “importing”. It is always dirty and never contains all the data you really need. Since I knew that I would be adding users that are in our Active Directory, I did tell them I needed to have a unique way of identifying people. Luckily, we store our employee numbers in Active Directory, therefore, I was able to bounce the list against AD to obtain the approvers’ Display Name and Account ID. Hopefully, you have similar environment where you can easily do this. Once I had the AD data I needed, I continued in Excel and removed duplicates and so forth. Tip: learn about Excel’s VLOOKUP function. Its great!

Step 2: Build an “Import Spreadsheet”
Once I had clean data to work with, I turned to how I was going to get the data into the SharePoint list. I knew the list had the following columns.

Title
DisplayName
Account
EmailBackup
Division

So I went into Excel and created a sheet that had the same column names, mapping the clean data accordingly. For the Title column, I just entered “Approver” for all the rows. For the EmailBackup column, the users indicated which approvers should get an email. Therefore, using some Excel functions (IF and LEN functions), I populated this column with either a value of “Yes” or a blank value. For the Division column, I had a value of the Division for the approvers I was importing. The key here is to make sure the value matches that of one in the list the defines the lookup column. Once done, I had what looked like a clean and complete Excel spreadsheet that matched my SharePoint list.

Step 3: Importing data into SharePoint
When you look at a list, you have under the Actions menu a option called Edit in Datasheet. This is the key to doing what I needed to do quickly. But there are some problems with doing this. To actually get your data from the Excel spreadsheet into the list, all you really have to do is copy all columns/rows from the spreadsheet and paste them into the Edit in Datasheet view. Simple, simple! But, because of the column types, this becomes very problematic. Most notably, the Person or Group column is the biggest problem! When you try to paste, you may get an error similar to the following.

Cannot paste the copied data due to data type mismatches or invalid data. Some source rows which would have created new rows contained invalid values in required fields. These rows were skipped.

Say what? Okay, to save a lot of Googling, what this really means is you have data that cannot be used. And as I already clued you into, the columns that are defined as People or Group are the problem. The reason is because of how the Edit in Datasheet view works with these columns. I don’t have the 100% accurate explanation of this, but it appears that SharePoint gets from its “user store” all possible users and puts them into a dropdown list. If you are in the Edit in Datasheet view and dropdown a Person or Group column, you will see what I mean. So, what is happening is you have users in your spreadsheet data that is not in the SharePoint “user store”. Hence, invalid data.

Step 4: Fixing the Invalid Data error
So, how do you fix this? Easy, return to Excel for a quick moment. Create a new column in your spreadsheet. Using the CONCATENATE function, append a semicolon onto the end of either the user Display Name or Account (keep in mind, this is the data I got from AD). Now that you have a list of users with a semicolon at the end, copy that column and return to SharePoint.

Go to the Site Settings of the site where your list is and go to People and Groups. Create a new group, naming it so that it sticks out like a sore thumb. Now, add users to this group. In the Users/Group textbox, do a paste. What should happen is everything you copied from the spreadsheet (users with an semicolon appended to the end) should have pasted. Uncheck the “Send email” option and click Ok. The group should be populated with all of the users. Great. Now, delete the group.

This is the magic operation to get Edit in Datasheet People or Group dropdown list populated with the values you need!

Step 5: Finally, Populating the List
Return to your list and change back into Edit in Datasheet mode. Now you should be able to copy and paste your Excel data directly into the Datasheet, without error. It is as simple as that!