2 Ways of Importing an Excel Staff List to SharePoint

hi I'm Peter caramel comes from norcombe business solutions in this demonstration I'll show you how to get data from Excel into SharePoint as you see I have a list here of data with staff from the contoso company and what I'm gonna do now is that I'm gonna import this into SharePoint and the first thing that you need to remember to do is to format the list here as a table doesn't matter which one you choose just choose one that looks interesting to you and of course the table functionality in Excel it's a whole demonstration by itself and has a lot of functionality but in this case I'm just gonna go for the one thing the export feature here the export table to SharePoint list which is exactly what I want to do and we're gonna just type the URL here which is a SharePoint comto so calm and I'm gonna create a list when the name of that list is gonna be staff next and as you see now it's creating columns for each and every one of these columns that I have in Excel and I'm just gonna click Next there are no options in Excel to modify which fields are being created and that's where I'm gonna do the other type of import shortly all right so now that has been created so let's go into that and now that's under site contents of course and here I have my list now these staff list 272 items so here's all my data and as you see there are several problems with this list for one thing all the fields are text fields and for example the Department shouldn't be a text field another big issue here is that the view is not good at all so I'm gonna modify the view and remove most of the fields because I don't want to see everything just a simple phone list would be nice enough first name last name email you don't need to see it this work phone could be good actually Department is good too we can just remove the rest of it here and making a much more readable list also I want to show the Edit field I think that's much much more useful than the type icon since all of these have the same icon I don't really care to see that over and over again doesn't really serve any purpose but the edit button does so I'm gonna put that at the top and remove the type icon I'm gonna click OK as you see now that's much much better but I'm also gonna create a new one here because this one goes straight into the quick edit mode I don't actually like that my users get straight into that and there are some connecting lists and stuff like that that doesn't work with when you're in quick edit mode so I'm gonna create another view I'm gonna base that on all items but it's gonna be a standard view gonna call that all stuff for something I'll make that the default one and it chooses the same list so that's fine all staff all items ok then I can just go in and change the one yeah the one that's called all items and call that edit mode or something like that making it very simple anyhow so that's the first way of getting data into SharePoint from Excel there's actually a much much better one and I'm gonna show you that one now I'm gonna go into site contents and actually create a contact list and I call that one staff too and as you see now that has a lot of built-in fields some of those I'm gonna use since I'm not gonna use so let's go in and actually modify this first remove some of them all of these are from the same company so I don't need the company field here I'm gonna delete that I'm also gonna change the notes field here make sure that that is plain text because I want to import into that and when I'm importing with access which is the second thing I'm gonna do here yeah it works much much better with plain text and then I'm also gonna go into the Advanced Settings of my list and remove the attached files I'm gonna say that those are not enabled for my text list and finally I'm gonna add a new column actually I'm gonna add two columns and I had one from the existing columns and that's the contact photo contact photo there I'll click OK and then I'm gonna upload all of the pictures of these people have a list of those so I'm gonna upload those to a picture library staff pictures in the staff picture so I'm gonna upload all the files and to do that really quick I'm going to open with Explorer and put that as a favorite their current location two favorite staff pictures and I'm gonna go into my picture library where I have all the pictures here and I'm gonna drag those in that's gonna take a few seconds search 172 items so while that's uploading we can go back here and see that we actually get some nice thumbnails of the people here now the can copy the shortcut to one of those pictures and we'll see that it's going to note that and see what the link is it's actually not the one I wanted let's go into one of those double click the picture and there we have a bit better URL to the picture as we see the link to each and every picture now is this one right so in the Excel spreadsheet now make sure that the link actually is correct yeah picture now it's picture just the images I'm gonna replace all of those things so it's gonna do a find and replace and replace it with all that I'm gonna replace images backsplash with all that place all right much much better clothes and I'm gonna save this now the key to getting this list here um staff to to be filled with all the data is using Microsoft Access there's one more step I want to do though as you see if I create a new item here there's no information about department so I want to create a department of course I don't want the department to be a text field I want it to be a drop-down and there are a lot of different options there are no less than four different options for creating a drop-down and SharePoint and the agent have their benefits problems but the one I'm gonna be using now is the choice one because that one works excellently with the search which I'm gonna be showing in another demonstration so I'm gonna go into site settings and create a new site column and I'm gonna call that compose the department and that's gonna be a choice and now of course I need to put that into a new group called contoso columns and then I want to type in each of the choices now to find all the unique choices here in this Excel table I'm gonna just place my cursor anywhere in this table and on the table tool so I'll summarize this with a pivot table that creates a new sheet and then I can take the department field and put that on the rows and here I have all my unique row labels and all the departments then I can just copy that and paste it into the column here nice and they're also alphabetical so that it's just great and I don't want to have a default choice there a default value so that does it now then I'm gonna go into this stuff to list add the existing column to that list out from existing site columns of course I'm gonna select the one I use there cantos the department and then click OK now let's go into the actual importing and let's do that by going into the list and opening that list with access and that creates a new desktop database called staff - I'm gonna link to that and of course I need to log in again because it's another security context here and now it's opening that in access unless you see I have the staff there and I can open that up and it's empty of course the next thing to do now is to get the excel list in here I don't want to save this to SharePoint site copy and paste would be the first option of course and that does work but much much better one is to actually link to the external data let's make sure I save this now I don't actually want this anymore so if you don't know that I'm gonna save this now and then go into access again and then link to an excel file important link and then I'm gonna link to the data source and find the contoso staff XLS file which I have here next first jokin and contains column headings beautiful next and the Excel that's what it's what it contains right so I'm gonna finish that and it's done so now I have an Excel staff table here which is linked to excel and I have a staff to which is linked to the SharePoint site so the next thing to do I don't need that one now is to create a query we're going to create a sign I'm gonna show the Excel list there and then I'm gonna append to the existing SharePoint list the staff to table of course and then it's just a matter of selecting all the fields the name should go into the full name field the first name should go into the first name field last name that's name field and so on work email address all right let's just move through these really fast out there then by the magic of video recording here I am did a couple of more here so you didn't have to watch all that on the bore get bored as you notice I'm adding Department about me and picture also to this and that's all I want that's another option that I can access of course gives you you can also do transforms of the data using all the powers of this access query tool really really powerful so anyhow now that I'm done with this query which is of course underneath an SQL query which does all that then I can simply run it by clicking on run here and that's now gonna update all that or actually append all the rows from the excel list into the SharePoint waste so I'm running the query now and that's updating rows and it's rather quick and I'm gonna go into the contacts list here and refresh here you see there's the whole gang with all the data fill them that I import it just open one of these and will see that I actually filled in the about me field also so looks rather good right that's is of course a much more powerful way to import the data into SharePoint from Excel so I'm gonna do another demo based on this next which is how I can actually search these people so that's the next demo thank you for watching this one

Channel: kalmstrom.com

Views: 158,782

Rating: 4.8025751 out of 5

Keywords: Microsoft Excel (Software), Microsoft SharePoint (Software), import Excel data to SharePoint, kalmstrom.com demo, kalmstrom.com tutorial, Excel 2013, SharePoint 2013, Access 2013, Microsoft Access (Software), Microsoft, staff list in SharePoint

Id: 3ExC13OjTZI

Length: 11min 42sec (702 seconds)

Published: Sat Sep 14 2013