Office 365 guide series – Create a new list from an Excel spreadsheet

This is the first in a series of Office 365 and SharePoint Online guides primarily aimed at users and power users, my aim with these guides is to show in an easy to grasp way, how you do a few relatively easy but important tasks in SharePoint Online/Office 365. Tasks that will give you a lot of functionality with relatively little effort. Since a lot have been covered already for SharePoint onprem in blogs and other online documentation like TechNet, but not specifically for Office 365 or SharePoint Onlne, I feel that there is a gap to fill here.

1. Office 365 guide series – Create a new list from an Excel spreadsheet

Create a new custom list from an existing excel spreadsheetThis task is simple if everything goes smoothly, but if you have never done it before, or if you have tried but got stuck on any of the little hickups you may encounter, then this may be the perfect guide for you.

– Start by creating your Excel spreadsheet. Make sure that it is a xlsx file. You may also use an existing spreadsheet with your listdata, copy from an old file, save another format as xlsx.

In the spreadsheet, follow the following rules to get a good result:
– Make sure that you do not have any empty column headers between columns. This will cause all headers to be imported as row 1 instead of beeing headers, new column headers will be created as 1, 2, 3 and so on.
– Keep the spreadsheet ‘clean’, remove empty rows, empty columns and any text above the header row and to the right or under of the table or cell range.
– The first column to the left will be the default edit field in the SharePoint list. Make sure that it has values on all rows.
– For best result, avoid formulas with calculated values.
– Use Excel 2013.

In a SharePoint Online site, do this:
1. Decide on a name for the list, it will be the URL and name of the list.
2. Go to the ‘cogs’/Settings and select Add an app

3. You will now see a list of all available apps (installed)
4. Search for Import or scroll down until you find the app called Import Spreadsheet
5. Click on Import Spreadsheet

6. Type in the Name of the new list
7. Type in a optional Description8. At the File location field, click Browse…
9. Browse to your xlsx file on your computer
10. Click on Import

11. The Import Wizard starts, by default, the Range type is set to Table Range12.Change this to Range of cells13. Click in the Select Range field
14. In the Spreadsheet, select the top left cell and then make sure that you select the entire table of data you want imported
15. It should now read something similar to: Sheet1!$A$1:$G$400 (top left : bottom right)

16. Now you click on Import
17. You may now get a logonprompt from Excel, enter the emailaddress for the SPO account and click next
18. Enter the account password and click on Sign in
19. Excel will now create the list and start importing the data, you will see a little progressbar at the bottom of the Excel application.
20. When the import is done, the new list will open and you will see the columns from the top

Note: You may note sometimes that a ‘mailto:’ has been added to the email column values.
This is added in excel but hidden when a cell is formatted as an email address. In the example above, note that the first row has the value of ‘Email’ which made the list field be formatted as single line of text instead of email.
The trick is most of the time to make sure that all fieldvalues in a column are consistent in format. One cell with a different format will cause the entire column to get a different formatting.

21. Done!

– You may now want to add a link in the left hand navigation to the list. Check the url in the addressbar of IE and copy it.
– Click on EDIT LINKS
– Click on +Link
– Type in the Text to display (will be seen in the navigation), for example ‘Contacts’ and paste the URL into Address
– Click OK
– Try the link out to make sure
– Done! (Again)

Error list

1
You get ‘The specified file is not a valid spreadsheet or contains no data to import’
When: When you browse to your Excel spreadsheet and click Import
Fix: Add site URL to trusted sites in Internet Explorer, it has to be in either trusted sites or Local Intranet sites.

2
A new header row is created instead of the headers I got (Column1, Column2, Column3…) and the headers becomes values if the first datarow
When: You have one or many empty headers in your cellrange
Fix: Remove all columns with blank headers, or add a value to them, this is only within your cell span.

3
If you get the error, ‘An unexpected error has occured. (-2147467259)’
When: Something is wrong with the SPO User session you have open
Fix: Sign out of your SPO session, close all IE windows, Close Excel – logon again to SPO, Add app…same procedure as last time.

4
Very large files, field data types get wrong, you expect Single line of text but get Multiple lines of text
When: The cellrange probably contain one or more ‘special’ values that is interpreted by excel/SPO as something else than it is.
Fix: With large files, create a new excelfile with only header and one row, make sure that the header row or the data row do not contain any value or characters out of the ordinary. Then, copy paste the rest of the rows in quick edit or datasheet view.

5
The wrong Column becomes the default edit column with the ‘…’ for the edit dropdown meny.
When: The column you want to use for default edit is not the first from the left.
Fix: First column will be the default edit field with the …make sute the Cell range starts with the top left cell.

‘

Note: All the same steps apply in SPO as well as SharePoint 2013 onpremise, exept for the Excel logon prompt and the ‘add site URL to trusted sites issue. In an onprem situation the URL is most of the time already considered Trusted or Local intranet.

Share this:

Like this:

Related

I have tried this steps but it gives me this error “An unexpected error has occurred.(-2147221005)” when click on import button. I have follow the steps you have suggested to fix this issue but still it gives above error.