• Login
• Become a member
• RSS
• Part of the TechTarget network

This script agent can be used as an alternative to COL files to parse and build Notes documents. This is especially appropriate when source data includes MEMO fields containing TABs and CR/LF characters. This agent contains a routine for exception processing. As with any load, the process should be tested and with a small, representative subset of source data until the kinks are worked out.

 
 Agent Information
Name: Load Delimited Text File
Type: LotusScript
Trigger: Manually From Agent List
Acts On: Run once 
(@Commands may be used)
LotusScript Code:

Options:
Option Public
Option Explicit
Option Base 1

Declarations:
Const datetimeField = "DATETIME"
Const richtextField = "RICHTEXT"
Const numberField = "INTEGER"
Const phonemask = "PhoneFormat"
Const ssnmask = "SsnFormat"
Const zipmask = "ZipFormat"
Dim db As NotesDatabase
Dim doc As NotesDocument, 
errordoc As NotesDocument 
Dim item As NotesItem Dim 
rtItem As NotesRichTextItem 
Dim positionStart As Integer, 
positionEnd As 
Integer, docLength As Integer, 
fieldnum As Integer, fieldcount 
As Integer Dim 
fileNum As Long, counter As 
Long Dim fulltext As Variant Dim
 inputfile As 
String, formname As String, delimeter 
As String, recordIndicator As String Dim 
fieldname As String, editmask As 
String, textString As String Dim fieldlist 
List As String

Sub Initialize
 Dim session As New NotesSession
 Set db = session.CurrentDatabase
 inputfile = |DriveLetter:
DirectorySubDirectoryTestData.txt|   
 'insert 
the drive,path, and name of the file to
 be read for import
       REM  If source data is generated 
from Excel, Access or FoxPro, you can 
export with a custom field delimiter.
       REM  If possible don't use TAB 
as field delimiter because source data 
may contain embedded TABs 
otherwise horizontal tab = Chr(9).  
 delimeter = "~"    'Privide the field 
delimeter used in the input file
       REM  If source data is generated 
from Excel, Access or FoxPro, you can 
insert append a column/field with a 
'known' string to delineate documents.
       REM  You can initialize 
recordIndicator with this string to delineate 
documents instead of using CR/LF. 
       REM  If this was not done leave 
'recordIndicator' commented to disable.
       REM  Common record delimiters 
Carriage Return/LineFeed; Chr(13) & Chr
(10), Custom; "~`^" 
 'recordIndicator = "~`^"   
 'provide string that indicates a new record
 formname = "FormName"   
'provide the name of the form for the
 document 
to be created in your NOTES database
 Call DefineFields    'Define input fields 
in this subroutine
 fileNum = Freefile()   'initialize file number 
 Open inputfile For Input As fileNum
 Do While Not Eof(fileNum)
  Line Input #fileNum, fulltext    
' Read each line of the file.
  docLength = Len(fulltext)
       ' If Left(fulltext, Len
(recordIndicator)) = recordIndicator Then
  If fieldcount = 0 Then
   Call CreateNotesDoc   
'indicates a new record
  Elseif fieldname <> "" 
And Not (doc Is Nothing) Then
   Call AppendToNotesDoc  
 'probably a partial record due 
to embedded cr/lf
  Else
   Call WriteErrorDoc
  End If
  If fieldcount = fieldnum  
Then fieldcount=0
  If Not(session.IsOnServer) 
Then Print counter
 Loop
 Close fileNum
End Sub

Sub CreateNotesDoc
 Set doc = db.CreateDocument
 doc.Form = formname
 positionStart = 1
 Forall f In fieldlist
  If positionStart>docLength 
Then Goto ProcessDocument    'no 
more fields to process, hit end of record
  fieldname = Listtag (f)
  If fieldname = "" Then 
Goto ProcessDocument  
  positionEnd = 
Instr(positionStart, fulltext, delimeter, 5)
  If positionEnd=0  Then 
positionEnd =docLength+1 
  Call BuildField
  positionStart = positionEnd+1
 End Forall
ProcessDocument:
 Call doc.ComputeWithForm
( False, False )   'this is optional if form 
does not contain other fields that need
 to be computed for a new document
 Call doc.Save( True, False )
 counter = counter + 1
End Sub

Sub BuildField
 textString = Trim(Mid$(fulltext,
positionStart, (positionEnd-
positionStart)))
 editmask = fieldlist(fieldname)
 Select Case editmask
 Case "" : Set item = New NotesItem
(doc, fieldname, textString)  
 Case "RICHTEXT"
  Set rtitem = New NotesRichTextItem 
(doc, fieldname)
  Call rtitem.AppendText(textString)
 Case "DATETIME"
  If textString = "" Then
   Set item = New NotesItem(doc, 
fieldname, Nothing)
  Else
   If Isdate(textString) Then
    Set item = New NotesItem(doc, 
fieldname, Cdat
(Format$(textString, "mm/dd/yyyy 
hh:mm am/pm")))
   Else
    Set item = New NotesItem(doc,
 fieldname, 
textString)
   End If    
  End If
 Case "INTEGER"
  If textString = "" Then
   Set item = New NotesItem(doc,
 fieldname, Nothing)
  Else
   If Isnumeric(textString) Then
    Set item = New NotesItem(doc, 
fieldname, Cint
(textString))
   Else
    Set item = New NotesItem(doc, fieldname, 
textString)
   End If
  End If
 Case "PhoneFormat"
  If Isnumeric(textString) Then
   Select Case Len(textString)
   Case 7 : Set item = New NotesItem
(doc, fieldname, 
Format$(textString, "000-0000"))
   Case 10 : Set item = New NotesItem
(doc, fieldname, 
Format$(textString, "(000) 000-0000"))
   Case Else : Set item = New NotesItem
(doc, fieldname, 
textString)
   End Select
  Else
   Set item = New NotesItem(doc, 
fieldname, textString)
  End If
 Case "SsnFormat"
  If Isnumeric(textString) And Len(textString)
 = 9 Then
   Set item = New NotesItem(doc, 
fieldname, 
Format$(textString, "000-00-000"))
  Else
   Set item = New NotesItem(doc, 
fieldname, textString)
  End If
 Case "ZipFormat"
  If Isnumeric(textString) And Len
(textString) = 9 Then
   Set item = New NotesItem(doc, 
fieldname, 
Format$(textString, "00000-0000"))
  Else
   Set item = New NotesItem(doc, 
fieldname, textString)
  End If
 Case Else : Set item = New 
NotesItem(doc, fieldname, textString)
 End Select
 fieldcount=fieldcount+1
End Sub

Sub WriteErrorDoc
       REM  This routine creates 
documents from strings that cannot be 
processed in normal processing.
       REM  Add a temporary form to
 your destination database named 
"ErrorDoc" 
with 1 field named "Body"
       REM  Add a temporary view to 
your destination database 
named "LoadErrors" with a selection 
formula for Form="ErrorDoc"
       REM  If the view contains any
 documents, then resolve them manually
       REM  When done, delete all 
documents of form name "ErrorDoc", the 
temporary form, and the temporary view
 Dim success As Variant
 Set errordoc = db.CreateDocument
 errordoc.Form = "ErrorDoc"
 Set rtitem = New NotesRichTextItem 
(errordoc, "Body" )
 Call rtitem.AppendText("INPUT 
FAILURE:  ")
 Call rtitem.Addnewline(1)
 Call rtitem.AppendText(fulltext)
 Call rtitem.Addnewline(2) 
 If Not(doc Is Nothing) Then
  success = doc.RenderToRTItem
( rtitem )
 End If
 Call errordoc.Save( True, False )
End Sub

Sub DefineFields
 REM Field names must be listed 
in the order they appear in the input 
file.  All fields must be accounted for.
 REM If a field is to be formated,
 the format code must be inserted as 
the value of the listtag
 REM Do not use format codes 
unless data content is known and 
consistent.  Leave null to create a 
generic text field
       REM syntax fieldlist("fieldname")
={format code}
 REM example using defined constant   
fieldlist("Comment_Field")
="RICHTEXT"   will create a richtext field
 REM example using defined constant  
 fieldlist("Date_Field")
="DATETIME"   will create a datetime field
        REM example using defined constant  
 fieldlist("Number_Field")
="INTEGER"   will create a INTEGER 
numeric field
 REM example using defined constant 
  fieldlist("SSN")=ssnmask  will 
create a text item formatted as 
"000-00-0000"
 fieldnum = 0
 fieldlist("RecordType")=""
 fieldlist("SeqNum")=""
 fieldlist("DtCreated")="DATE"
 fieldlist("Name")=""
 fieldlist("Address")=""
 fieldlist("City")=""
 fieldlist("State")=""
 fieldlist("ZipCode")=""
 fieldlist("HomePhone")="PhoneFormat"
 fieldlist("WorkPhone")="PhoneFormat"
 fieldlist("Fax")="PhoneFormat"
 fieldlist("DtLetterSent")="DATETIME"
 fieldlist("DtClosed")="DATETIME"
 fieldlist("ORIGIN_CODE")=""
 fieldlist("Body")="RICHTEXT"
 Forall f In fieldlist
  fieldnum = fieldnum +1  
 End Forall
End Sub

Sub AppendToNotesDoc
 Dim tempItem As NotesItem
 Dim processField As String    
'used to position field table for 
processing 
 positionStart = 1
 Forall f In fieldlist
  If fieldname = Listtag (f) Then 
processField = "Append"
  If processField <> "" Then
   If positionStart>docLength 
Then Goto 
ProcessDocument    'no more fields 
to process, probably hit CR/LF
   positionEnd = Instr(positionStart, 
fulltext, delimeter, 
5)
   If positionEnd=0  Then 
positionEnd =docLength+1      'CR/LF 
   Select Case processField
   Case "Add"
    fieldname = Listtag (f)
    Call doc.Removeitem(fieldname)
    Call BuildField
   Case "Append"
    textString = Trim(Mid$(fulltext,positionStart, 
(positionEnd-positionStart)))
    Select Case editmask
    Case "RICHTEXT"
     Set rtitem = doc.GetFirstItem(fieldname)
     Call rtitem.AddNewLine( 1 )
     If textString<>"" Then  Call 
rtitem.AppendText(textString)
    Case "DATETIME" : Call WriteErrorDoc
    Case "INTEGER" : Call WriteErrorDoc
    Case Else 
     Set tempItem = doc.GetFirstItem( 
fieldname)
     Set item = doc.ReplaceItemValue( 
fieldname, tempItem.Text  & Chr(10) 
& textString)
    End Select
    processField = "Add"
   End Select
   positionStart = positionEnd+1
  End If     'else loop for next field 
until last field written 
is found
 End Forall
ProcessDocument:
 Call doc.ComputeWithForm( False, 
False )
 Call doc.Save( True, False )
 counter = counter + 1
End Sub

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip exchange by member Becky Whidden. Please let others know how useful it is via the rating scale below. Do you have a useful Notes/Domino tip or code to share? Submit it to our monthly tip contest and you could win a prize and a spot in our Hall of Fame.

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.