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

By: Hesham Saad   |   Updated: 2011-02-22   |   Comments   |   Related: > 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:

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

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

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

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

- 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)

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

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

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

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

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

- Check the Columns section as shown below:

II. Add a new list item:

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

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

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:

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

- Click on "Create":