Last week a friend of mine asked a fairly innocent question, "Can I import a spreadsheet into SharePoint?" My answer to almost all SharePoint questions is, "Of course you can, and it'll rock!" Of course this is no exception. I thought I'd write up a quick blog post to show some of the ways it rocks.
I started out with this spreadsheet:
Pretty simple. Of course he could have just uploaded this spreadsheet to a document library, but then anyone that wanted to add vales would need to have Excel installed to open it. Plus there would be the added complexity of check in and check out to deal with, which aren't necessary for this. Instead, we're going to import this spreadsheet into a list in SharePoint. To do that go to the home page of the site where you want to create the list and click Site Actions > Create like below:
Under Custom Lists choose "Import Spreadsheet."
You'll get have to enter a list name and choose the XLSX file you want to import. This will fire up Excel and you'll get to choose which parts of your spreadsheet you want to import. In this example I lassoed the relevant information:
Click okay and let SharePoint work its magic. SharePoint generates a list like this:
SharePoint took pretty good care of this, and even used the header row for the column names. Pretty cool. But it made some general assumptions about the data. It added everything as a single line of text as shown here:
This doesn't work well. Fortunately you have a lot of flexibility. Let's go in and change the birthdate field to a date field. Go into the list settings and change the type.
These settings look appropriate for the data in the column. When I hit okay I got this dialog box:
Being the adventurous sort, I hit OK. Here's what was waiting for me:
Of course there was not a big pink question mark, but the Birthdate was missing for Harry. How come? If you look back to my original spreadsheet you'll notice the value for Harry's date was 12/31/174. Since it's not a valid date, SharePoint kicked it to the curb. Even though we lost a value, it was understandable and I considered it a success. I decided to try the Rank column and see what SharePoint could do. It made sense to make it a choice value. I opened of the list settings and changed the Rank column to a choice type. Look what SharePoint did for me:
It went ahead and populated the existing values into the dropdown choices. All that for no extra cost. Sweet.
Hopefully this blog will help if you want to move some spreadsheets into SharePoint lists.
tk