Creating SharePoint lists with Excel data using Access 2013
Posted
Thursday, February 28, 2013 10:00 AM
by
CoreyRoth
It’s not uncommon for users to want to move data from an Excel spreadsheet into a new SharePoint list. There have been various techniques to achieve this, but Access 2013 provides a way to this pretty easily. The process is pretty simple. We create a new Access 2013 database and import the Excel data. From there, we export it to SharePoint. Let’s look at the process. First, let’s take a look at my data in Excel.
To import it to SharePoint, start by creating a new blank Access 2013 database. The name doesn’t matter.
Now, we need to import the data from our Excel Spreadsheet. This is a multi-step wizard process but it isn’t too complicated. Go to the External Data tab and then click the Excel button under Import & Link.
On this step, browse to the path of your Excel file. I typically use the Import option, but you could use Append, or Link depending on your needs.
On the next step, you’ll be prompted for details about the data in your spreadsheet. You’ll get a preview of your data. It’s ideal if the first row of your spreadsheet has the column names.
On the next step, you can tweak the data types used in each column. This is important to ensure SharePoint sets the types for the columns appropriately. You’ll notice the format of your dates comes through weird but it will be ok in the end.
On this step, you can specify a primary key or create a new one. I’m not sure if SharePoint pays attention to this or not when the list is created.
Finally, you can confirm the name of the table you want to create.
At this point the data is now visible inside Access as you can see below. Now, we want to create the SharePoint list using this data. Under the Export section, click More and choose SharePoint List.
Now, a new wizard will guide you through importing the data in SharePoint. First, you will need to provide the URL to the site as well as the name of the list.
When it finishes, you’ll now see your data in SharePoint. You’ll notice it provides it to you in a grid view that you can immediately begin editing.
It seems to do a pretty good job of mapping to appropriate SharePoint column types as well. Take a look and note how it used types of Single line of text, Date and Time, Number, and Currency.
Keep in mind this is only an import process. The data is not synchronized. That means this will behave differently than taking an existing list and clicking the Export to Excel button. This seems like a pretty simply way to get data from Excel into SharePoint. I like it because it creates the list for you with little effort and it maps the data types for you. Do you find this useful? How do you import data from Excel?