Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

Automatically create SharePoint custom list populated with Excel data - Part 1


By:   |   Read Comments   |   Related Tips: > Sharepoint

Problem

It is easy to export SharePoint lists to an Excel spreadsheet, but how about the reverse? If we already have data in Excel format with predefined columns, headers, formulas, etc... how can we make those available in a SharePoint list?

Solution

Via Microsoft Office Excel 2007 we can create a table and then export it to a new SharePoint list. SharePoint provides OOTB (Out Of The Box) functionality to import Excel spreadsheets and to save it as a custom list. There are multiple techniques for getting this job done. Parts I and II of this tip will explore three different approaches.

  • Export an Excel spreadsheet table to a SharePoint custom list (Default text data type for table - Columns headers)  - Part I.
  • Export an Excel spreadsheet table to a SharePoint custom list (Multiple data type for table - Columns headers) - Part II.
  • Import an Excel spreadsheet table to a SharePoint custom list (SharePoint OOTB Custom Lists feature) - Part II.

1. Export an Excel spreadsheet table to a SharePoint custom list : (Default text data type for table - Columns headers)

- Open Microsoft Excel 2007 , Select (Highlight) a range of cells then click on the "Table" button under Insert tab:

home

- Make sure that the displayed range of cells match what you have selected (highlighted) then click on Ok button :

page layout

- Change the columns headers and enter some data, then Click on "Export" : "Export Table to SharePoint List..." under Design tab:

authors

- Then, type your SharePoint site URL at the "Address" section , Provide a name and description for your exported table and click Next button:

sharepoint list

- Make sure that all of your selected (highlighted) column headers are displayed, then click the Finish button:

export table

- Wait until you get a message box telling you that "The table was successfully published and may be viewed on" :

windows sharepoint services

- After clicking on the link (shown above) we'll see our table that was exported to a SharePoint custom list in datasheet view:

sharepoint tips

- Then, we can return to the default standard view by clicking on "Show In Standard View" button under "Actions" menu items :

show in standard view

- And finally here's our new automatically created SharePoint custom list imported with our Excel table data:

excel

In the next part of this article, we will see the exciting technique of doing the Excel export again, while preserving data types in the columns.

Next Steps


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools