Archive

Archive for January, 2014

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

January 15, 2014 5 comments

 Office365logo       SP2013logo

Greetings SharePoint Online users!

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
2. Office 365 guide series – A guide to SharePoint Navigation using metadata
3. Office 365 guide series – Using your document templates in SharePoint online

This first time, I will explain how to:

Create a new custom list from an existing excel spreadsheet
This 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

AddanApp

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

ImportSpreadsheet

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

AddApp1

11. The Import Wizard starts, by default, the Range type is set to Table Range
12. Change this to Range of cells
13. 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)

ImportWiz2

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

NewList

Note: You may note sometimes that a ‘mailto:’ has been added to the email column values.
ColumnError2
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.

ColumnError2

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.

Error1


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.

References

None so far 🙂

SP2013logo

_________________________________________________________

Enjoy!

Regards

Twitter | Technet Profile | LinkedIn

Skydrive Pro availability and functionality chart

January 10, 2014 Leave a comment

SkydriveSP_h64

Wow! Skydrive Pro, what a great invention! (I know, It has been out there for a while already…)
I assume that you all know what Skydrive Pro is all about, but to just real short sum it up, SkyDrive Pro is:
A client application that allows a user to keep files synchronized between his device and his online SharePoint document library.

Now, the thing is, that apparently, you are not really supposed to run Skydrive pro unless you with that mean to it against anything but Office365…seems like this was meant to be but is slowly turning toward not meant to be…
I do realize that Office 365 is the future for Microsoft, but when such a great feature is available, and it must be a rather easy task to get the functionality dynamic so that you can connect to any webfolder, it is a shame that the support for running Skydrive Pro as a SharePoint onprem has so little support.

From my tests and research this is what works and what does not work:

Windows 8.x x64 Windows 8.x 32bit Windows 8.x RT Windows Phone IOS   Android   Mac OSX 
App to Onprem * No No No Yes No 3rd party No
Desktop to Onprem** Yes Yes No No No No No
App to Office 365 *** Yes Yes Yes Yes Yes 3rd party No
Desktop to Office 365 **** Yes Yes No No No No No

Please, do let me know if I am missing out on anything in the chart!

Windows 7 uses the same App and Desktop application as Windows 8x

* The App model with the possibility to connect to SharePoint 2013 Onprem
** A Desktop integrated application that connects to SharePoint 2013 Onprem
*** The App model with the possibility to connect to SharePoint Online/Office 365
**** A Desktop integrated application that connects to SharePoint Online/Office 365

So, if you want to make use of your internal onprem SharePoint 2013 farm for Skydrive Pro, you have to rely solely on Windows 8x or Windows Phone (Available in the Office hub)

The Skydrive pro desktop klient is available in Office 2013 versions as well, except for the Office 2013 for Windows RT

Skydrive pro requires SharePoint 2013 or Office 365 (Eligable subscriptions)
SharePoint 2010 works only with SharePoint Workspace on Windows OS

References

What is SkyDrive Pro?
http://office.microsoft.com/en-us/sharepoint-server-help/what-is-skydrive-pro-HA102822076.aspx

What is SkyDrive Pro in SharePoint 2013 and how does it relate to SkyDrive?
http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=388

How to setup SkyDrive Pro on Mac OSX
http://www.dogu.no/blog/posts/2013/08/26/how-to-setup-skydrive-pro-on-mac-osx.aspx

_________________________________________________________

Enjoy!

Regards

Twitter | Technet Profile | LinkedIn