Using Python to extract data from Lotus Domino

I love working with Python.  Whenever I need to script a task, it can easily be done using Python. In this case I found that you can use Python and a module to extract data from Lotus Domino to migrate to a new system.

You need a:

  • Microsoft Windows computer
  • Python 2.7
  • Module win32com for Python (Windows only)
  • A Lotus Notes client with User ID

I followed the excellent guides found in the resources section at the bottom of this post to get me started. The process I followed was:

  1. Create a new copy of the database from the server to my local development computer
  2. Connect to the database using Python and print out the document ID to check that it is processes correctly.
  3. Use the NSFTOOLS application to get a list of all the fields within the database
  4. Modify the code get the values of the fields I am interested in.
  5. Write the information extracted to CSV, a text file or even detach attachments.

Example

I used the process to extract data from our CRM system hosted in Lotus Notes and wrote the output to files for importation into SalesForce.com.

import win32com.client
import csv # imports the csv module
import sys # imports the sys module
import re

notesServer = "NotesServer/Certifier"
notesFile = "NotesFolder\NotesDatabase.nsf"
notesPass = "NotesPassword"

#Connect to notes database on server
notesSession = win32com.client.Dispatch('Lotus.NotesSession')
notesSession.Initialize(notesPass)
notesDatabase = notesSession.GetDatabase(notesServer,notesFile)
notesView = '(All Documents)'

notesView = notesDatabase.GetView(notesView)
notesDoc = notesView.GetFirstDocument()

#Code block used from another blog.See resources section
def makeDocumentGenerator(folderName):
 # Get folder
 folder = notesDatabase.GetView(folderName)
 if not folder:
 raise Exception('Folder "%s" not found' % folderName)
 # Get the first document
 document = folder.GetFirstDocument()
 # If the document exists,
 while document:
 # Yield it
 yield document
 # Get the next document
 document = folder.GetNextDocument(document)

#Loop through documents extracting data from listed fields and view.
for document in makeDocumentGenerator(notesView):
 try:
 crm_iencompany = document.GetItemValue('IENCompany')[0].encode('utf-8').strip()
 docID = str(document.UniversalID.strip()) #Get the document ID to make filename unique
 crm_principle = document.GetItemValue('Principle')[0].encode('utf-8').strip()
 crm_iensubject = document.GetItemValue('IENSubject')[0].encode('utf-8').strip()
 crm_ienviewphones = document.GetItemValue('IENVIEWPHONES')[0].encode('utf-8').strip()
 crm_ienviewfullname = document.GetItemValue('IENVIEWFULLNAME')[0].encode('utf-8').strip()
 crm_ienviewaddress = document.GetItemValue('IENVIEWADDRESS')[0].encode('utf-8').strip()
 crm_iensalesrep = document.GetItemValue('IENSalesRep')[0].encode('utf-8').strip()
 crm_entersendto = document.GetItemValue('EnterSendTo')[0].encode('utf-8').strip()
 crm_entercopyto = document.GetItemValue('EnterCopyTo')[0].encode('utf-8').strip()
 crm_from = document.GetItemValue('From')[0].encode('utf-8').strip()
 crm_body = document.GetItemValue('Body')[0].encode('utf-8').strip()

#Remove any characters from filename which are not valid.
 fileName = crm_iencompany + "-" + docID + ".txt"
 fileName = fileName.replace("/"," ")
 fileName = fileName.replace("\\"," ")
 fileName = fileName.replace(":"," ")
 fileName = fileName.replace("\r"," ")
 fileName = fileName.replace("\n"," ")
 fileName = fileName.rstrip('\r\n')
 fileName = fileName.rstrip('\n\r')
 fileName = fileName.rstrip('\n')
 fileName = fileName.rstrip('\r')
 cleaned_up_filename = re.sub(r'[/\\:*?"<>|]', '', fileName)

#Write to txt file.
 with open(cleaned_up_filename, 'w') as f:
 f.write("iencompany:" + crm_iencompany + "," + "\n")
 f.write("principle:" + crm_principle + "," + "\n")
 f.write("ienviewphones:" + crm_ienviewphones + "," + "\n")
 f.write("ienviewfullname:" + crm_ienviewfullname + "," + "\n")
 f.write("ienviewaddress:" + crm_ienviewaddress + "," + "\n")
 f.write("iensalesrep:" + crm_iensalesrep + "," + "\n")
 f.write("entersendto:" + crm_entersendto + "," + "\n")
 f.write("entercopyto:" + crm_entercopyto + "," + "\n")
 f.write("from:" + crm_from + "," + "\n")
 f.write("subject:" + crm_iensubject + "," + "\n")
 f.write("body:" + crm_body + "," + "\n")
 except:
 print document
 pass

Resources