Integra for Notes Banner
  QuickReports Integra4Notes Integra Family of Products

Does Integra allow export & reimporting of data?


At times data contained in Notes is best manipulated using other tools such as Excel. Integra for Notes can be used to export data from a Notes database or databases into an Excel spreadsheet where the data can be further manipulated and then reimported into Notes.

To do this, an Integra Excel Export profile would need to be created in addition to a separate Integra Excel Import profile.The import profile would need to have some LotusScript placed into the Advanced tab of the Integra profile to identify the data to be updated.

The best practice approach would be:
  • when exporting the data to Excel, add a column to be exported linked to a computed field (Selected Field List) that returns the document unique ID of the Notes document as data type Text using a formula like @Text(@DocumentUniqueID).
  • when importing the data from Excel, the Advanced tab would read each line from the Excel spreadsheet, identify the Unique Doc ID of the line and look-up this Unique Doc ID in the target Notes database to obtain the individual Notes document in the target database.
  • On location of the document, the fields can be overwritten with the values from the spreadsheet row.

Below is a sample script to perform this task:

Const CB_INITIALISE        = 3
Const CB_BFORREADCOM       = 7
Const CB_TERMINATE         = 4

select case stats.cbstatus

  Dim db  As NotesDatabase
  Dim doc As NotesDocument

  Set db = stats.impdoc.ParentDatabase

   ' DocID is a field in the Selected Field List referring to a column
   ' in the Excel spreadsheet containing the Document Unique ID of the
   ' existing document that is to be updated.
   ' A blank ID means it is a new document that is to be created

  If stats.ImpDoc.DocID(0) = "" Then     ' New Document

     stats.impdoc.Type = "Person"

  Else                                   ' Existing Document

     Set doc = db.GetDocumentByUNID(stats.impdoc.DocId(0))

     doc.FirstName            = stats.impdoc.FirstName(0)
     doc.MiddleInitial        = stats.impdoc.MiddleInitial(0)
     doc.LastName             = stats.impdoc.LastName(0)
     doc.Title                = stats.impdoc.Title(0)
     doc.Suffix               = stats.impdoc.Suffix(0)
     doc.OfficePhoneNumber    = stats.impdoc.OfficePhoneNumber(0)
     doc.OfficeFaxPhoneNumber = stats.impdoc.OfficeFaxPhoneNumber(0)
     doc.CellPhoneNumber      = stats.impdoc.CellPhoneNumber(0)
     doc.PhoneNumber          = stats.impdoc.PhoneNumber(0)
     doc.HomeFaxPhoneNumber   = stats.impdoc.HomeFaxPhoneNumber(0)
     doc.PhoneNumber_6        = stats.impdoc.PhoneNumber_6(0)
     doc.MailAddress          = stats.impdoc.MailAddress(0)

     set stats.impdoc = doc

  End If

end select

The code line...

Set doc = db.GetDocumentByUNID(stats.impdoc.DocId(0))

...can and will throw an exception if the document id pass as parameter does not exist in the database. We therefore need to add some script in the Script Error Handler field to cover such exceptions:

If instr(ucase(import.errortext),"INVALID UNIVERSAL ID") > 0 then

   ' UNID of existing document not found

   msgbox "Document with UNID " & stats.impdoc.DocId(0) & " not found. Entry ignored."

   import.errortext = ""
   import.iserror   = False
   import.continue  = True

   stats.procstat = 1

end if


FAQ# 0019
Bookmark and Share



   Shireburn Software Ltd. 2008 - 2022