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.

ExcelDefaultData

To import it to SharePoint, start by creating a new blank Access 2013 database.  The name doesn’t matter.

AccessNewBlankDatabase

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.

AccessExternalDataExcelImportButton

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.

AccessGetExternalDataExcel

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.

AccessImportSpreadsheetWizard

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.

AccessImportSpreadsheetWizard2DataTypes

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.

AccessImportSpreadsheetWizard3PrimaryKey

Finally, you can confirm the name of the table you want to create.

AccessImportSpreadsheetWizard4Name

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.

AccessExportToSharePointList

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.

AccessExportToSharePointListWizard

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.

ExcelImportedList

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.

ExcelListDataTypes

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?

Comments

# Creating SharePoint lists with Excel data using Access 2013 - Corey Roth [MVP] | SharePoint Resources | Scoop.it

Pingback from  Creating SharePoint lists with Excel data using Access 2013 - Corey Roth [MVP] | SharePoint Resources | Scoop.it

# re: Creating SharePoint lists with Excel data using Access 2013

Tuesday, May 7, 2013 11:17 AM by Ethan dunham

Nice work!  Thanks.

Leave a Comment

(required) 
(required) 
(optional)
(required)