Exporting Data from Lotus Notes Databases

Exporting from Lotus Notes

Lotus Notes was innovative and, by most accounts, a great collaboration software in its heyday. Though there are still some 35 million users of Notes, there are regular rumors of its imminent demise.

Because of this, some organizations that have used Notes over the years may want to move to some other system, such as SharePoint. Getting data out of Notes for reporting or for use in a different system is not very straightforward, as there are several options depending on your needs.

The first is to connect Notes to SQL, which I covered back in June 2012.

Exporting from Lotus Notes
Exporting from Lotus Notes

Another is to export the data into Lotus 1-2-3, and then into Microsoft Excel, if need be. This is problematic for many Excel users today, as you cannot open Lotus 1-2-3 files with versions of Excel newer than Excel 2003.

Exporting view data into a comma separated value (.csv) file is another option. I have read that this particular option does not always work exactly right, especially if there are commas or some other special characters in the data, so YMMV.

The main problem with all of the above options is that you can only export data shown in a particular view with these methods. Unless you want to create (or already have) a view that has all document fields in the view, you won’t be able to see them all. If you want to export all the data in each document, you must export documents into what is called “Structured Text”.

Structured Text, while readable by text editors, cannot be directly imported into CSV or other formats using only Word or Excel. However, some tools been developed for this purpose. One example of such a tool is the Structured Text Parser (STP) written by James J. Schwaller. Tools such as these will make converting data from Lotus Notes into other formats or for use in other systems much simpler, and will not require hiring contractors or buying expensive tools that you will not need once your data is converted.

Good luck!

How to Configure the Auth.go file for the Google Glass Mirror API Using Golang

Go (golang) logo

For those who have asked how my auth.go file is configured for the Go Quick Start project, I’ve posted my auth.go file below.

// Copyright (C) 2013 Google Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//      http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package qslquery

import (
	"fmt"
	"net/http"
	"strings"

	"code.google.com/p/goauth2/oauth"
	"code.google.com/p/google-api-go-client/mirror/v1"
	"code.google.com/p/google-api-go-client/oauth2/v2"

	"appengine"
	"appengine/urlfetch"
)

const revokeEndpointFmt = "https://accounts.google.com/o/oauth2/revoke?token=%s"

// Because App Engine owns main and starts the HTTP service,
// we do our setup during initialization.
func init() {
	http.HandleFunc("/auth", authHandler)
	http.HandleFunc("/oauth2callback", errorAdapter(oauth2callbackHandler))
	http.HandleFunc("/signout", errorAdapter(signoutHandler))
}

// auth is the HTTP handler that redirects the user to authenticate
// with OAuth.
func authHandler(w http.ResponseWriter, r *http.Request) {
	url := config(r.Host).AuthCodeURL(r.URL.RawQuery)
	http.Redirect(w, r, url, http.StatusFound)
}

// oauth2callback is the handler to which Google's OAuth service redirects the
// user after they have granted the appropriate permissions.
func oauth2callbackHandler(w http.ResponseWriter, r *http.Request) error {
	c := appengine.NewContext(r)

	// Create an oauth transport with a urlfetch.Transport embedded inside.
	t := &oauth.Transport{
		Config:    config(r.Host),
		Transport: &urlfetch.Transport{Context: c},
	}

	// Exchange the code for access and refresh tokens.
	tok, err := t.Exchange(r.FormValue("code"))
	if err != nil {
		return fmt.Errorf("Exchange(%q): %v", r.FormValue("code"), err)
	}

	o, err := oauth2.New(t.Client())
	if err != nil {
		return fmt.Errorf("Unable to instantiate UserInfo service: %s", err)
	}
	u, err := o.Userinfo.Get().Do()
	if err != nil {
		return fmt.Errorf("Unable to retrieve user ID: %s", err)
	}

	userId := fmt.Sprintf("%s_%s", strings.Split(clientId, ".")[0], u.Id)

	if err = storeUserID(w, r, userId); err != nil {
		return fmt.Errorf("Unable to store user ID: %s", err)
	}
	storeCredential(c, userId, tok)
	bootstrapUser(r, t.Client(), userId)
	http.Redirect(w, r, "/", http.StatusFound)
	return nil
}

// bootstrapUser sets up sharing contact and notification for a new user.
func bootstrapUser(r *http.Request, client *http.Client, userId string) {
	c := appengine.NewContext(r)
	m, _ := mirror.New(client)

	if strings.HasPrefix(r.Host, "https://") {
		s := &mirror.Subscription{
			Collection:  "timeline",
			UserToken:   userId,
			CallbackUrl: fullURL(r.Host, "/notify"),
		}
		m.Subscriptions.Insert(s).Do()

		c := &mirror.Contact{
			Id:          "APP_Name",
			DisplayName: "App Name",
			ImageUrls:   []string{fullURL(r.Host, "/static/images/gopher.png")},
		}
		m.Contacts.Insert(c).Do()
	} else {
		c.Infof("Post auth tasks are not supported on staging.")
	}

	t := &mirror.TimelineItem{
		Text:         "Welcome to QSL Query!",
		Notification: &mirror.NotificationConfig{Level: "DEFAULT"},
	}

	m.Timeline.Insert(t).Do()
	insertCallsignSearchItem(r, m)
}

// signout Revokes access for the user and removes the associated credentials from the datastore.
func signoutHandler(w http.ResponseWriter, r *http.Request) error {
	if r.Method != "POST" {
		return nil
	}
	c := appengine.NewContext(r)
	userId, err := userID(r)
	if err != nil {
		return fmt.Errorf("Unable to retrieve user ID: %s", err)
	}
	if userId == "" {
		http.Redirect(w, r, "/auth", http.StatusFound)
		return nil
	}
	t := authTransport(c, userId)
	if t == nil {
		http.Redirect(w, r, "/auth", http.StatusFound)
		return nil
	}
	client := urlfetch.Client(c)
	_, err = client.Get(fmt.Sprintf(revokeEndpointFmt, t.Token.RefreshToken))
	if err != nil {
		return fmt.Errorf("Unable to revoke token: %s", err)
	}
	storeUserID(w, r, "")
	deleteCredential(c, userId)

	http.Redirect(w, r, "/", http.StatusFound)
	return nil
}