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 2


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 : (Multiple data type for table - Columns headers)

- Open Microsoft Excel 2007. Start to simulate a table by typing it's columns headers in bold then type some data , We can change the column data types as shown below:

excel data

- Then select (highlight) your range of cells and click on "Table" button under the Insert tab:

table

- Also, Make sure that the displayed range of cells match what you have selected (highlighted) then click on the Ok button, then check the "My table has headers" check box in order not to create initial column headers and to map your bold text headers to be your table columns headers:

create table

- Then Click on "Export" : "Export Table to SharePoint List..." under Design tab:

export table

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

export table to sharepoint

- Make sure that all of your selected (highlighted) column headers are displayed and then click the Finish button: (Note here that we have multiple predefined data types as : Text(Single Line) , Date , Number)

sharepoint list

- Wait until the message box shows indicating that "The table was successfully published and may be viewed on":

windows sharepoint services

- After clicking on the above link which is displayed, we'll see our table exported to SharePoint custom list in datasheet view:

excel

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

task name

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

excel

- We need to make sure that our predefined excel table column headers are exported successfully and match the custom SharePoint list columns.

Quality check:

I. Check the list column data types:

- Click on "List Settings" under the Settings menu items:

exel

- Check the Columns section as shown below:

columns

II. Add a new list item:

- Click on "New Item" under the New menu:

excel

- Check here that "Authors & Position" are text (single line) column data type while "Latest Tip" is a date column data type and "Tips" is a number data type":

new item

2. Import an Excel SpreadSheet table to a SharePoint custom list : (SharePoint OOTB Custom Lists feature)

- Open Microsoft Excel 2007. Start to simulate a table by typing it's column headers in bold then type some data:

authors

- Go to the SharePoint site, Click on the "Site Actions" menu then click on "View All Site Content":

view all site content

- Click on "Create":

all site content

- Click on "Import SpreadSheet" under Custom Lists section:

import spreadsheet

- Then, Type a "Name & Description" to your list, then click on the "Browse" button to import our predefined excel spreadsheet :

sharepoint tips
authors

- Once we click on the "Open" button this will open the Excel spreadsheet and another popup window to select the range of cells that we want to import it to the SharePoint list.

Select "Range of Cells" from the "Range Type" drop down list then click on the...

minus

...button.

At "Select Range" for selecting the range of cells, choose the particular rows and columns that we want to import it to the SharePoint list:

range of cells

- Then, select (highlight) the whole range of cells (i.e: For example from A1 : C3 , as shown below) :

windows sharepoint server

- Then Click on "Import" button:

import to windows sharepoint services

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

excel

Notes:

Sometimes when we try to import Excel spreadsheet, we might get the "Method Post of object IOWSPostData failed " error so in order to fix this error click here. Also, after fixing this problem we can face another error "Cannot connect to the server at this time. your table cannot be published" so to fix this problem we just need to check that we have created a top level site or not. If not then just create the top level site and it will start working. This may not seem logical but it works. Also, I noticed that this problem no longer exist afer installing SharePoint SP2 (Service Pack 2).

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Hesham Saad Hesham Saad

View all my tips





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