Excel ‘helpfully’ converting numbers longer than 15 digits to scientific notation when importing from .csv

When you attempt to open or import .csv data into Excel, long numbers are helpfully converted into scientific notation. In most situations, this is fine – it make it easier to see the number in a cell and the original number can be seen in full if the cell number format is changed.

However, when numbers are longer than 15 digits, Excel silently rounds anything after the 15th digit – a real problem with GUIDs in general and QuestionMark Perception Question IDs in particular.

There are a number of potential solutions to this, any of which may work for you:

  1. Change the file’s .csv extension to .txt or  choose Data | Get External Data | From Text to bring up the Text Import Wizard and, in Step 3 of 3 where you set the data format, select the appropriate column (or columns with Shift held down), set the Column data format to Text. However, this appears to fail with very high numbers of columns (in this case >200), leading me to solution 2:
  2. Upload the .csv file into Google Docs, making sure that Convert documents, presentations, spreadsheets, and drawings to the corresponding Google Docs format is ticked. Then export as Excel by File | Download As | Microsoft Excel. Thanks to kpm for this. However, this still seems to randomly covert a few GUIDs into 15 significant figure sceintific numbers.
  3. The solution which worked for me was to open the .csv file in OpenOffice 3 Calc and, as with Excel, in the Text Import dialogue, select all the  columns in the Fields section and then change their Column type to Text. Unlike Excel, this did reliably import all >200 columns without converting any GUIDs.

 

 

Early Thoughts on LTI

We have been investigating using the Learning Tools Interoperability (LTI) tool available through our VLE, WebLearn (Sakai), in order to authenticate users for various services that we provide (or are considering providing), including Rogo, iCases and OxPro.  We initially thought that this would mean we could take user/authentication control completely out of these systems, but unfortunately (in terms of ease, but fortunately in terms of security) it is not so easy.

The LTI specification has been created by the IMS Global Learning Consortium and uses OAuth to sign requests for accessing a tool. The Tool Provider (TP), e.g. Rogo, iCases or OxPro, stores a secret and an ‘oauth_consumer_key’ for each Tool Consumer (TC), e.g. WebLearn. When a tool is launched from the TC through LTI, a signed request is sent to the TP. The TP uses the oauth_consumer_key sent with the signed request to look up the secret, recreate the signature and check that the signatures match. Matching signatures results in a valid request, and the user is allowed to access the tool. If everything within the tool is available to every user this is as complicated as it gets. It is also possible to differentiate access rights based on a user’s role, which should (i.e. it is recommended by the LTI specification) be passed through in the LTI launch data. Both of these scenarios do not require any user information or privileges to be stored by the TP.

The situation gets more complicated when a user should only be allowed to access certain resources within the TP. This is the situation with Rogo, for example, as a student who is allowed to access Rogo should not necessarily be allowed to access every paper within Rogo. Therefore, the Rogo team at the University of Nottingham have implemented LTI in such a way that it is necessary for Rogo to contain it’s own user information. When a user first accesses Rogo through LTI, they must login separately to Rogo. Rogo then stores a link between the user_id passed through in the LTI launch data (which, in combination with the oauth_consumer_key, uniquely identifies the user) and the user within the Rogo system. Thereafter that user should never again need to login to Rogo when coming in through LTI from the same TC.

An LTI link to Rogo only provides a user with access to a single paper. In order to define which paper is linked, the first time an administrator/module convenor (as defined in Rogo) accesses the LTI link (which should be when the link is set up in the TC), they choose the paper from the list of papers for which they are an editor. Thereafter, students coming in through this LTI link will only be able to access that paper (and then only if they are allowed to access that paper within the Rogo system, i.e. they are a member of the module to which the paper belongs).

The situation with Rogo causes problems for us, as we want our students to be able to login using the University of Oxford single-sign-on system (which is used to login to Weblearn), and not need a separate set of login details for Rogo, even if they only need them for their first login. Therefore, we are looking into logging users into Rogo directly through the single-sign-on system, rather than using LTI.

For iCases, we do not want to have to pre-register all users within the system, as is necessary in Rogo. Unlike in Rogo, we are planning only allow access through LTI. We will then create a user profile when a user first accesses the system and this user profile will be linked to the LTI user_id/oauth_consumer_key. WebLearn passes through sufficient information (email address, name, and hopefully single-sign-on username) that we can know who a student is based on this LTI launch data, so we can monitor students’ activity and performance. This information is not required under the LTI specification, but we will make other institutions who use iCases aware that in order to track students’ activity and performance, their TC will need to provide this information, otherwise they will need to find out who a user is based on the user_id passed through in the launch data.

An LTI link to iCases will only allow a student access to a single iCase. If a user wants to access a different scenario, they will have to come in via a different LTI link, with the appropriate resource_link_id. We would prefer not to maintain a list of user/scenario associations, which would enable users to access multiple scenarios through a single link. However, we are still in the early stages of implementing LTI for iCases, and it may be something we choose to do further down the line.

 

Zoomify – Flash vs HTML5

We use Zoomify as part of our CSlide project, to enable viewing of microscope slides scanned at up to 40x resolution. It works in a similar way to Google Maps, loading small image tiles to make up a larger image, without the need to load the entire image (up to 20,000 megapixels!) in one go. This makes it much quicker to load the section of the image that the user wants to look at.

Zoomify have just released an HTML5 version of the viewer, in addition to the Flash-based viewer that has been available since Zoomify was first released. The major advantage of this from our point of view is that it enables microscope slides to be viewed on mobile devices, in particular iPads and iPhones, that do not support Flash. The two viewing options provide a similar viewing experience, but there are a few differences, as follows:

  • Mouse scroll wheel zooming – in the Flash viewer it is possible to zoom in and out using the mouse scroll wheel. This does not seem to be possible using the HTML5 viewer, although I am not aware of any reason why this could not be made possible.
  • Full screen view – in the Flash viewer, going to ‘full screen’ view mode results in the viewer taking over the entire screen. In the HTML5 viewer, going to ‘full screen’ view mode results in the viewer taking up the entirety of the browser window. These behaviours are a result of the nature of the viewing options: HTML5 exists entirely within the browser and is therefore constrained by the browser dimensions; Flash on the other hand is, to some extent, ‘outside’ of the browser, and so can occupy the entire screen. My personal opinion is that the HTML5 viewer way of doing this is preferable, as it enables a user to see a large view, but also switch to other programs/browser tabs without needing to close ‘full screen’ view.
  • Annotation Viewing – currently the Flash annotation viewer has a few more capabilities, as described below. However, I do not feel that any of these represent significant advantages for normal use. Screenshots of the two annotation viewers are shown at the end of the post:
    • Display options menu – Flash annotation viewer has a display options menu, with various options that are not available in the HTML5 annotation viewer, e.g. adding a pointer, or saving the currently shown section of the image to file. However, the most of useful of the display options in the Flash annotation viewer, ‘full screen view’, is available on the toolbar in HTML5 annotation viewer, so full screen mode can be activated in a single click, whereas it requires 2 clicks in the Flash annotation viewer.
    • Ruler – The Flash annotation viewer has a ruler panel, to give an indication of scale at the current zoom level. This is a potentially handy, but not essential, feature.
    • Point of Interest preview – the Flash annotation viewer shows a preview of each Point of Interest, so a user can see what they are going to be taken to before they choose to go to a point of interest. Again this is handy, but not essential.
  • Annotation Editing – currently, the only annotation editing that can be done using the HTML5 viewer is to add polygons, or change the shape of existing polygons. It is not possible to add points of interest, labels or notes. Therefore the HTML5 viewer falls a long way short in this respect, and, at present, we are not going to enable any editing using the HTML5 version. However, Zoomify are presumably (I am awaiting confirmation of this) planning to extend the annotation editing capabilities in HTML5, to bring them in line with the Flash viewer.
One advantage of the HTML5 viewer over the Flash viewer, from our perspective at least, is that we are more familiar with HTML/JavaScript/CSS, on which the HTML5 viewer is based, that we are with Flash. This will make it easier for us to modify the HTML5 viewer, if required. Furthermore, I believe that, with HTML5, CSS and JavaScript being open, and universally supported, standards, HTML5 is the best long term solution.
Zoomify HTML5 Annotation Viewer Screenshot
HTML5 Annotation Viewer Screenshot
Zoomify Flash Annotation Viewer Screenshot
Flash Annotation Viewer Screenshot