in

Dot Net Mafia

Group site for developer blogs dealing with (usually) .NET, SharePoint 2013, SharePoint 2010, Office 365, SharePoint Online, and other Microsoft products, as well as some discussion of general programming related concepts.

This Blog

Syndication

Archives

Corey Roth [MVP]

A SharePoint MVP bringing you the latest time saving tips for SharePoint 2013, Office 365 / SharePoint Online and Visual Studio 2013.

Creating SharePoint lists with Excel data using Access 2013

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 said:

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

March 12, 2013 12:10 PM
 

Ethan dunham said:

Nice work!  Thanks.

May 7, 2013 11:17 AM

Leave a Comment

(required)  
(optional)
(required)  
Add

About CoreyRoth

Corey Roth is an Applications Architect at Infusion specializing in ECM and Search.
2012 dotnetmafia.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems