Different Migration Methods to get data from a lotus notes application

1)    Identify Scope

First step of your lotus notes migration is identity the inventory of lotus notes to be migrated. Creation of an application inventory which contain the list of lotus notes forms, structure and volume of data. This confirm what should be migrated to maximize project efficiency.  

Application inventory details may vary based on the scope and complexity of the project. However a typical Lotus notes application migration inventory can contain below fields.

Lotus Database Name	Lotus Form name	Details	Scope or out of Scope	Total Fields	Total Records	Lotus Navigator details	View Details

There should be a mapping between lotus notes application fields to destination system fields including the data type mapping. Main data types used in lotus notes application are text, number, Date/time, dialog list, computed etc. This has to be mapped with the destination application data types. Also need to consider the default value used in the corresponding lotus notes forms. This can be represent in below format for each lotus notes form.

Lotus Notes Field Name	Data Type Lotus Notes	Destination Field name	Destination Field Type	Default Value	Sample Data	Views used

2)    Choose Suitable Migration Method

2.1.                    Via Lotus Client Export Excel 

If you are looking for only few and selected data to be migrated, then you no need to go for any complex methods. You can do it using Lotus notes Client export tool exporting to excel feature. For lotus notes 8.5 client tool, this can be achieved by using by any of the methods.

1. Using Export option in file menu.
   
   
   Tabular text export can be achieved by using this method. Using this data can be exported to excel sheet for further process.

 

1. Using Edit menu option
   Open the view , go to edit menu and opt select all. Again got to edit menu opt copy as => table.
   Now open an excel sheet and paste the records. There is a restriction in number of columns can be copied at a time.

Pros : Non Code solution

Cons : Only possible for limited columns as it is  view dependent. Also data segregation is difficult.

2.2.                    By using XML services of Lotus notes. 

You can get the required application data using built-in XML Services of Lotus notes. These web services are exposed and can be retrieved in browser URL or using any other tool or program.

Pros : Lotus Notes Application Data is available through built-in XML service .

Cons : Unable to get the xsl transformation file which is required to bifurcate columns.  Creation of xslt from scratch is difficult and time consuming.

2.3.                    By using SQL server SSIS program. 

By Using S SSIS Program you can easily import data from lotus notes and export to corresponding data destination. In order to get data from lotus notes IBM Lotus Notes SQL Driver for ODBC can be used

Pros :  Non code solution and easy approach.

Cons : Some versions of  IBM Lotus Notes SQL Driver for ODBC is not compatible with all operating systems.

2.4.                    By using scripts from Lotus client tool.

Lotus script can be used to fetch records from lotus notes. Lotus script is similar to VB script. Given below a sample script to fetch the names of the fields from the lotus notes form. Below script can be used to generate application inventory. We can use this platform to write script to get data from lotus notes and export to other data sources.

Steps:

create a form in lotus and create button in that (create =>Hotspot=>Button)and on click event of a button script you can select as lotus script. And include below code in that.

go to design =>Preview in note to run this program. Enable debug lotus notes script option in tools if you want to debug the code.

Dim session As New NotesSession

                Dim db As NotesDatabase

                Dim fileName As String

                Dim fileNum As Integer

                Dim headerstring As String

                Dim values As String

                Dim selection As String

                Dim collection As NotesDocumentCollection

                Dim doc As NotesDocument

                Dim tipoField As String

                'On Error Resume Next

                Set db = session.CurrentDatabase

                Set oexcel=createobject("Excel.Application")

                oexcel.workbooks.add

                oexcel.visible=True

                frmno=1

                Forall form In db.Forms

                                'Messagebox form.Name

                               

                                intLine=1

                               

                                Set form1 = db.GetForm( form.Name)

                                If  Len(form.Name)>=30 Then

                                                frmNewname = Left(form.Name,29)

                                Else

                                                frmNewname=form.Name

                                End If

                                frmname="Sheet"&frmno

                                frmno=frmno+1

                                oexcel.Sheets(frmname).Name = frmNewname

                                intLine=1

                                Forall fie In form1.Fields

                                                oexcel.cells(intLine,1)=fie

                                               

                                                ftype  = form1.GetfieldType(fie)

                                                oexcel.cells(intLine,2)=ftype

                                                intLine=intLine+1                            

                                End Forall

                                oexcel.Sheets.add

                End Forall           

                'oexcel.SaveAs "C:\TEST.XLS"    

                Set oexcel = Nothing

2.5.                    By using Custom programs. 

We can create Custom Programs to pull data from lotus notes application. This can be achieved by using corresponding lotus notes API’s available from IBM lotus note site. For .net  applications it can be referenced to lotus notes APIs (inerop.domino.dll)

Pros :  Migration procedure is customizable . We can pull all data structure using .net script

Cons : Need to develop migration programs for that. Below is a sample program in .net using

complete code can be contacted from here

code:

    NotesSession lotusNotesSession = new NotesSession();
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            object misValue = System.Reflection.Missing.Value;
            // Opening lotus database for operation
            lotusNotesSession.Initialize("<password>");
            NotesDatabase localDatabase = lotusNotesSession.GetDatabase("<server name>", "<dbname>", false);
          

            Domino.NotesForm form1;
            foreach (Domino.NotesForm frm in localDatabase.Forms)
            {
                form1 = localDatabase.GetForm(frm.Name);
            }
            Domino.NotesDocumentCollection docs;
            Domino.NotesDocument doc;
            Domino.NotesItem Nitem;
            int i;
            Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
            Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            string msg = "";
            int ctr = 0;
            int cln = 0;
            docs = localDatabase.AllDocuments;
            MessageBox.Show(docs.Count.ToString());
            //looping through all documents in Domino
            doc = docs.GetFirstDocument();
            while (doc != null )
            {

                i = 1;
                foreach (Domino.NotesItem ni in doc.Items)
                {
                    Nitem = ni;
                    if (ctr == 0)
                    {
                        //getting field names to store as column header also stores in a list array
                        xlWorkSheet.Cells[ctr + 1, i] = ni.Name;
                        lstExcelColumns.Add(ni.Name);
                        xlWorkSheet.Cells[ctr + 2, i] = ni.Text;
                    }
                    else
                    {
                        cln = getColumnOfsheetfrmlst(xlWorkSheet, ni.Name);
                        xlWorkSheet.Cells[ctr + 2, cln] = ni.Text;
                    }

                    i++;
                }

                ctr++;
                doc = docs.GetNextDocument(doc);

            }