Real-World Example 1: Importing a Delimited Text File

Real World Example 1 Importing a Delimited Text File

This script will come in handy when you have an automated application that sends an email nightly to a robot containing updates or stores updated files on a host drive somewhere to be imported into a database. This routine can be set to run on a scheduled basis, for example, to seek out all the latest incidents submitted to a customer incident-tracking mail-in database. It probes each email document to get its attached delimited text file, detaches the file, and then imports each delimited record into the incident database as a new incident record. When the script completes, it sends an email notification to the administrator about how many records were retrieved, processed , and created in the database by the agent. What's nice about this example is that you can use some or all of its parts . If you need only the import function, you can use just a subset of the code. If you need to use the email subroutine, you can also pull that out and reuse it wherever necessary. Let's get started.

The options area of the script module, as shown in Listing 15.1, is where the developer states preferences for the script module, such as Public , Declare , Base , and Compare . Option Public , as used here, unequivocally allows any variables dimensioned ( DIM ) within the script module to be declared as publicly usable to any routine or subroutine throughout the entire module. Option Private can be used to override Option Public . Option Explicit or Option Declare sets the module so that all variables must be declared ( DIM 'd). If you use undeclared variables on the fly, when you go to compile your script, syntax errors will be generated. This rule does not apply to arrays; you can define an array implicitly using the ReDim declaration method.

Listing 15.1 Option Preferences

1. Option Public
2. Option Explicit

The block of global declarations in Listing 15.2 sets up constants and variables for the module to run. The text delimiter , view names, form names , and database IDs are declared globally so that they can be used (called) anywhere inside the module. It's generally good practice to declare all your dimensioned variables in the (Declaration) area. However, this doesn't prevent you from declaring them in the Initialize event or any other event in the module.

Listing 15.2 Global Declarations

1. Const gFieldDelimiter=""
2. Const gListViewColumn=1
3. Const gIncidentIDFieldPosition = 1
4. Const gFormName="Incident"
5. Const gIncidentNumViewName="vINLU"
6. Const gMailDBRepID = "85256C2F004CB691"

7. 'Now declare most of the variables we need for this module
8. Dim gFieldList List As Integer
9. Dim currentDb As NotesDatabase
10. Dim MailView As NotesView
11. Dim MailDoc As NotesDocument
12. Dim TempDoc As NotesDocument
13. Dim rtitem As Variant
14. Dim fileCount As Integer
15. Dim FileName As String
16. Dim EMailDoc As NotesDocument
17. Dim Body As NotesRichTextItem
18. Dim BodyTextA As Variant
19. Dim BodyTextB As Variant
20. Dim ret As Variant
21. Dim ImportRecordCounter As String
22. Dim Intro As String
23. Dim errormsg As String

Depending upon your needs, the gFieldDelimeter constant as shown in Listing 15.3 can be changed to any ASCII character code of your choice. The delimiter separates each record in the target text file. The gUniqueIDFieldPostion variable on line 3 determines the position in the import record where a unique number exists. The position must be declared as an integer. Obviously, the view names, replication IDs, and form names can all be changed to work within your applications.

Listing 15.3 The Initialize Subroutine

1. Sub Initialize
2. Dim session As New NotesSession
3. Dim MailDB As New NotesDatabase("","")
4. Dim sID as String

5. On Error Goto ERRORHANDLER

6. Set currentDB = session.CurrentDatabase
7. Call session.SetEnvironmentVar("ImportRecordCounter", "0")
8. fileCount = 0

9. 'Check to see if the database is on a server and add currentDB.server otherwise use ""
10. If session.IsOnServer Then
11. sID = currentDB.Server
12. Else
13. sID=""
14. End If
15.
16. If MailDB.OpenByReplicaID ( currentDB.Server,gMailDBRepID ) Then
17. ' go get the new mail message in the $All view
 i. Set Mailview = MailDB.GetView( "($POP3)" )
 ii. Set MailDoc = Mailview.GetFirstDocument
 iii. While (Not MailDoc Is Nothing)
 1. Set rtitem = MailDoc.GetFirstItem( "Body" )
 2. If ( rtitem.Type = RICHTEXT ) Then
 a. If MailDoc.HasEmbedded Then
 i. Forall file In rtitem.EmbeddedObjects
 1. If ( File.Type = EMBED_ATTACHMENT ) Then
 a. fileCount = fileCount + 1
 b. file.ExtractFile "d:INCDNTIMPT" & Cstr(Month(

Today) &_
 c. Day(Today) & FileCount) & ".txt"
 d. 'Now process the dettached file here
 e. gFieldList("RequestID") = 1
 f. gFieldList("CompanyName") = 2
 g. gFieldList("ContactName") = 3
 h. gFieldList("Address") = 4
 i. gFieldList("City") = 5
 j. gFieldList("State") = 6
 k. gFieldList("ZipCode") = 7
 l. gFieldList("Country") = 8
 m. gFieldList("Phone") = 9
 n. gFieldList("EmailAddress") = 10

 o. Filename = "d:INCDNTIMPT" & Cstr(Month(

Today) &_
 p. Day(Today) & Cstr(FileCount)) & ".txt"
 q. 'Go and run the main routine to process the

file
 r. Call main(session, currentDB, filename)
 s. 'Remove the importfile we're done with it
 t. Kill "d:INCDNTIMPT" & Cstr(Month(Today) &

Day(Today) &_
 u. Cstr(FileCount)) & ".txt"
 2. End If
 ii. End Forall
 b. End If
 3. Else
 a. Goto PROCESSNEXT
 4. End If

18. PROCESSNEXT:
 1. Set TempDoc = Mailview.GetNextDocument(MailDoc)
 2. MailDoc.Remove True
 3. Set MailDoc = TempDoc
 ii. Wend
19. End If

20. 'Now send a mail memo telling how many files and records were processed today.
21. Call sendMail(session, currentDb, ImportRecordCounter, fileCount)
22. Exit Sub

23. ERRORHANDLER:
24. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in LS agent: " &_
25. "ImportRecords in Incident Import App at " & Erl()
26. Msgbox errormsg
27. Resume Next
28. End Sub

The purpose of the Initialize subroutine in Listing 15.3 is to get and extract each text attachment from each email that resides in the Email Incident Bucket database, send it off to be processed by the Main subroutine, clean up the file that it extracted, and delete the email after it has been processed. The Initialize subroutine also keeps track of how many files have been processed by the agent and sets up the environment variable placeholder ImportRecordCounter to keep track of how many records are eventually processed. After all the files and records have been processed by the agent's subroutines, the Initialize subroutine finally sends an email to the person who ran the agent about how many files and records have been processed.

The Initialize subroutine also provides an error-handling routine so that if an error is encountered in the routine when run, the agent is halted and an error message is invoked that is captured in the log.nsf of the server where the agent is run.

Code lines 17e “17n in Listing 15.3 use the gFieldList declared array to set up the imported file's field placeholders. The imported file must contain data in the exact order as what appears in the array. This means that the incident's ID number must be stored in the number 1 position in the delimited text file, the company name value must be stored in the number 2 position in the delimited text file, and so forth, as shown in Listing 15.4. You can change the field names and array names; just make sure that they match the order in the delimited file to the labels in the array. The field names on the form that you are importing to also must match the names of the array labels exactly. If not, the import might not work as expected.

Listing 15.4 Delimited Text File Example

12541MyCompany1MyContact1MyAddress1MA11111USA111-111-1111 [e-mail]
12542MyCompany2MyContact2MyAddress2MA22222USA222-222-2222 [e-mail]
12543MyCompany3MyContact3MyAddress3MA33333USA333-333-3333 [e-mail]

The kill statement in line 17.t of Listing 15.3 deletes the delimited file where it was extracted in line 17.b. You could use variables and other LS commands to get and set the file-extraction path programmatically. However, for the sake of clarity in this example, the file path is explicitly named in this routine.

The routine in Listing 15.5 first calls the FreeFile() function to get a file handle on the import file and assign it an internal file number so that it can be identified as an object in the back end. An Open statement is then called to actually open the import file. The incident view object is then set as declared in the Declarations area. Next, a unique number list (an array), made up of all the incident numbers in the file, is built so that when the processFile subroutine is called, the routine can check to make sure that duplicate incidents are not processed but are overwritten. After the NumberList array is built, each record in the file is processed thereafter. If the incident number is not found in the NumberList array, a new document is created for the unique record in the Incident Import App database. If the incident does exist, the agent updates the existing document with the new information. When the agent has finished and no errors are generated, it closes the import file by clearing its file handle. It then is returned to the Initialize subroutine to complete the agent's next course of action.

Listing 15.5 The Main Subroutine

1. Sub main (session As NotesSession, CurrentDb As NotesDatabase, ImportFileName As

String)
2. Dim view As NotesView
3. Dim importFile As Integer
4. Dim success As Integer
5. Dim NumberList List As String

6. On Error Goto ERRORHANDLER

7. If (Not ImportFileName = "") Then
 i. ImportFile = Freefile()
 ii. Open ImportFileName For Input As ImportFile

 iii. 'Get the Number List
 iv. Set view = currentDB.getView(gIncidentNumViewName)
 v. If dbColumn(CurrentDB, view, gListViewColumn, NumberList) Then
 1. Call processFile(session, view, currentDb, importFile, NumberList)
 vi. End If
 vii. Close importFile
8. End If
9. Exit Sub
10. ERRORHANDLER:
11. errormsg = " * * Agent Error: " & Err & " - " & Error() &_
12. " in the main sub-routine of LS agent " &_
13. "ImportRecords in Incident Import App at " & Erl()
14. Msgbox errormsg
15. Resume Next
16. End Sub

The purpose of the function in Listing 15.6 is to get each sequential line in the file, parse each delimited position in the line, and either place the string content of each position in its respective field on a new incident or overwrite the field contents of an existing incident. Line 12.i calls the Line Input # statement to read the line, grabs the string for the position of the record label being processed, and places it into the record String variable. This results in the record string looking something like this: 12345 MyCompany1 MyContact ... .

Listing 15.6 The processFile Function

1. Function processFile(session As NotesSession, view As NotesView, currentDb As

NotesDatabase, Byval inputFile As Integer, IncidentList List As String) As Integer
2. Dim IncidentInList As Integer
3. Dim record As String
4. Dim IncidentID As String
5. Dim OldIncidentID As String
6. Dim key As Variant
7. Dim IncidentDoc As NotesDocument
8. Dim Counter As Integer

9. On Error Goto ERRORHANDLER

10. processFile = True
11. Counter = Cint(session.GetEnvironmentString("ImportRecordCounter"))

12. Do While Not Eof(inputFile)
 i. Line Input # inputFile,record
 ii. record=record & gFieldDelimiter
 iii. IncidentID = getField(record, gIncidentIDFieldPosition, gFieldDelimiter)
 iv. IncidentInList = Iselement(IncidentList(IncidentID))
 v. If IncidentInList Then
 1. key = IncidentID
 2. Set view = currentdb.GetView(gIncidentNumViewName)
 3. Set IncidentDoc = view.GetDocumentByKey(Key)
 4. If (Not IncidentDoc Is Nothing) Then
 a. Call OverwriteIncident(currentdb,session, IncidentDoc, record, IncidentID)
 5. End If
 vi. Else
 1. Call createIncident(currentdb, session, record, IncidentID)
 vii. End If

 viii. IncidentList(IncidentID) = IncidentID
 ix. Counter = Counter + 1
 x. OldIncidentID = IncidentID
13. Loop

14. Call session.SetEnvironmentVar( "ImportRecordCounter", Cstr(Counter) )
15. Exit Function

16. ERRORHANDLER:
17. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the processFile

sub-routine of LS agent: " &_
18. "ImportRecords in Incident Import App at " & Erl()
19. Msgbox errormsg
20. Exit Function
End Function

Next, the getField function is called to extract the record's unique incident number string in position 1, up to the delimiter in the record. The routine then determines whether the incident number exists in the NumberList array. If the incident number exists, the processFile function retrieves the existing incident document object by a key in a view, as shown in line 12.v.3, and calls the OverwriteIncident subroutine to process the incident in the database. If it does not exist in the NumberList array, line 12.vi.1 shows that the CreateIncident subroutine is called to create a new incident in the database for the current record.

The processFile function then goes on to complete its tasks for the current record, as shown in lines 12.vii “12.x, and loops through each record until all the records in the file have been processed likewise. After each record is processed, the NumberList array is reassigned, the record counter is incremented by 1, and the current incident ID is assigned to the OldIncidentID variable. This allows the IncidentID variable to be reset when the routine loops back to the DoWhile() statement in line 12 once again, to process the next record in the file.

When all records have been processed, the file counter environment variable is incremented by 1, and the function exits and returns 1 (true) to the Main subroutine, telling it that it successfully completed.

The function in Listing 15.7 creates a new incident (line 2) in the Incident database and populates its fields based on the string values in each position inside the current record (lines 8 “9). The ListTag function is used in line 8.i to return the name of the gFieldList list position currently being processed by the ForAll statement. The routine then steps through each position and replaces the position value with that of the corresponding record string for the current position.

Listing 15.7 The CreateIncident Function

1. Function CreateIncident(db As NotesDatabase, session As NotesSession, record As String,

IncidentID As String) As Integer
2. Dim IncidentDoc As New NotesDocument(db)

3. On Error Goto ERRORHANDLER

4. IncidentDoc.docKey = IncidentDoc.universalid & session.UserName
5. IncidentDoc.Form = "Incident"
6. IncidentDoc.LastImportDate=Now
7. IncidentDoc.DateImported = Now

8. Forall positions In gFieldList
 i. IncidentDoc.ReplaceItemValue (Listtag(positions)), (getField(record, positions,

gFieldDelimiter))
9. End Forall

10. IncidentDoc.Save True,True
11. createIncident = True
12. Exit Function

13. ERRORHANDLER:
14. errormsg = " * * Agent Error: " & Err & " - " & Error() &_
15. " in the CreateIncident sub-routine of LS agent: " &_
16. "ImportRecords in Incident Import App at " & Erl()
17. Msgbox errormsg
18. Exit Function
19. End Function

When all the fields have been populated for the new incident, the document is saved to the database and the function returns 1 (true) back to the processFile function, telling it that it successfully completed.

The purpose of the function in Listing 15.8 is to parse the record at each position in the record or the position requested , remove its delimiter, and return the stripped string value back to the routine that called the getField function. If the routine starts with a string that looks like MyString1MyString2MyString3... and position 2 is requested by the calling routine, it returns only MyString2 .

Listing 15.8 The getField Function

1. Function getField(record As String, Byval position As Integer, delimiter As String) As

String
2. Dim i As Integer
3. Dim fieldStart As Integer
4. Dim fieldEnd As Integer

5. getfield=""

6. On Error Goto ERRORHANDLER

7. fieldStart = 0
8. fieldEnd = 0
9. For i=1 To position
 i. fieldStart = fieldEnd + 1
 ii. fieldEnd = Instr(fieldStart, record, delimiter)
 iii. If fieldEnd=0 Then Exit For
10. Next
11. If fieldEnd>0 Then
 i. getfield = Mid(record, fieldStart, (fieldEnd - FieldStart))
12. End If
13. Exit Function

14. ERRORHANDLER:
15. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the getField
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " &

Erl()
16. Msgbox errormsg
17. Exit Function
18. End Function

The function in Listing 15.9 actually mimics the dbColumn @Function using LotusScript by building a list of values from each document found in a specified view. The NumberList array, found in the Main subroutine, is created using the dbColumn function to build the list of incident numbers that currently reside in the database already. It builds the list of incident number values in each document, builds the columnValues array, and then returns 1 (true) to the routine that called it, to let it know that it successfully completed. You can reuse this repeatedly throughout your applications.

Listing 15.9 The dbColumn Function

1. Function dbColumn(DB As NotesDatabase, view As NotesView, Byval columnNumber As

Integer, columnValues List As String) As Integer
2. 'Variable Declarations
3. Dim value As Variant
4. Dim doc As NotesDocument

5. On Error Goto ERRORHANDLER

6. Erase columnValues 'Clears the columnValues list

7. Set doc = view.getFirstDocument
8. While Not (doc Is Nothing)
 i. value = doc.columnValues(0)
 ii. If Isnumeric (value) Then
 1. value = Cstr(value)
 iii. End If
 iv. columnValues(value) = value
 v. Set doc = view.getNextDocument(doc)
9. Wend
10. dbColumn = True
11. Exit Function

12. ERRORHANDLER:
13. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the dbColumn
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
14. Msgbox errormsg
15. Exit Function
16. End Function

NOTE

You can also use the NotesViewColumn method of the NotesView class to get the values of a particular column, as in this example:

Dim column as NotesViewColumn 
Set column = view.Columns( 0 )

 

However, the dbColumn function is more than handy when you need to retrieve only certain values from a subset of docs in a view or pull only those that meet a certain criteria.

The OverwriteIncident function in Listing 15.10 is very similar to the CreateIncident function because it doesn't set the form field, the docKey field, and the dateImported field values in the Incident document. Its purpose is to solely parse each position value in the record and update the field values with the new data from each. It then goes on to save the document and return 1 (true) to the processFile function, telling it that it successfully completed.

Listing 15.10 The OverwriteIncident Function

1. Function OverwriteIncident(db As NotesDatabase, session As NotesSession, IncidentDoc

As NotesDocument, record As String, IncidentID As String ) As Integer
2. OverwriteIncident = True

3. On Error Goto ERRORHANDLER

4. IncidentDoc.LastImportDate=Now

5. Forall positions In gFieldList
 i. IncidentDoc.ReplaceItemValue (Listtag(positions)), (getField(record, positions,

gFieldDelimiter))
6. End Forall

7. IncidentDoc.Save True,True
8. Exit Function

9. ERRORHANDLER:
10. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the

OverwriteIncident
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
11. Msgbox errormsg
12. Exit Function
13. End Function

The last subroutine for this example is shown in Listing 15.11. It can be reused repeatedly throughout all of your applications to automate email generation or automatic workflow notifications. Its purpose is self-explanatory. The power in this routine is that it enables agents to send emails summarizing its activities, successes, and failures.

Listing 15.11 The OverwriteIncident Function

1. Sub sendMail(session As NotesSession, Currentdb As NotesDatabase, ImportRecordCounter

As String,fileCount As Integer)

2. On Error Goto ERRORHANDLER

3. 'Now send a mail memo telling how many files and records were processed today.
4. Set EMailDoc = New NotesDocument(Currentdb)
5. Set Body = New NotesRichTextItem(EMailDoc, "Body")

6. ImportRecordCounter = session.GetEnvironmentString( "ImportRecordCounter")
7. EMailDoc.sendTo = session.Username
8. ret = Evaluate("@unique(SendTo)",EMailDoc)
9. EMailDoc.SendTo = ret

10. EMailDoc.Form = "Memo"
11. EMailDoc.principal = "ImportIncidents*Robot"
12. EMailDoc.Subject = "Incident Import Activity Report"

13. intro$ = "This is an automated notification. The incident Import Agent last ran at "

& Time$() & " on " & Format(Now(), "Long Date")
14. BodyTextA = Cstr(fileCount) & " File(s) have been processed."
15. BodyTextB = ImportRecordCounter & " Record(s) have been processed."

16. Call Body.appendText(Intro$)
17. Call Body.AddNewLine(2)
18. Call Body.appendText(BodyTextA)
19. Call Body.AddNewLine(2)
20. Call Body.AppendText(BodyTextB)

21. 'Don't save the mail message in the Email Import incident db after it's sent
22. EMailDoc.SaveMessageOnSend = False

23. 'Send the new mail notification for this import run
24. Call EMailDoc.Send(False)

25. Exit Sub

26. ERRORHANDLER:
27. errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the main

sub-routine of LS agent: " &_
28. "ImportRecords in Incident Import App at " & Erl()
29. Msgbox errormsg
30. Resume Next
31. End Sub

The complete ImportIncidents agent code listing is shown in Listing 15.12.

Listing 15.12 The ImportIncidents Agent Complete Script Listing

'-ImportIncidents LotusScript Agent

'Options:
Option Public
Option Explicit

'Declarations:
Const gFieldDelimiter=""
Const gListViewColumn=1
Const gIncidentIDFieldPosition = 1
Const gFormName="Incident"
Const gIncidentNumViewName="vINLU"
'this is the RepID of the mail file where the import files reside.
Const gMailDBRepID = "85256C2F004CB691"

Dim gFieldList List As Integer
Dim currentDb As NotesDatabase
Dim MailView As NotesView
Dim MailDoc As NotesDocument
Dim TempDoc As NotesDocument
Dim rtitem As Variant
Dim fileCount As Integer
Dim FileName As String
Dim EMailDoc As NotesDocument
Dim Body As NotesRichTextItem
Dim BodyTextA As Variant
Dim BodyTextB As Variant
Dim ret As Variant
Dim ImportRecordCounter As String
Dim Intro As String
Dim errormsg As String
'Routines:
Sub Initialize
 Dim session As New NotesSession
 Dim MailDB As New NotesDatabase("","")
 Dim sID As String

 On Error Goto ERRORHANDLER

 Set currentDB = session.CurrentDatabase
 Call session.SetEnvironmentVar("ImportRecordCounter", "0")
 fileCount = 0

 'Check to see if the database is on a server and add currentDB.server otherwise use

""
 If session.IsOnServer Then
 sID = currentDB.Server
 Else
 sID=""
 End If

 If MailDB.OpenByReplicaID ( sID,gMailDBRepID ) Then
' go get the new mail message in the $All view
 Set Mailview = MailDB.GetView( "($POP3)" )
 Set MailDoc = Mailview.GetFirstDocument
 While (Not MailDoc Is Nothing)
 Set rtitem = MailDoc.GetFirstItem( "Body" )
 If ( rtitem.Type = RICHTEXT ) Then
 If MailDoc.HasEmbedded Then
Forall file In rtitem.EmbeddedObjects
 If ( File.Type = EMBED_ATTACHMENT ) Then
 fileCount = fileCount + 1
 file.ExtractFile "d:INCDNTIMPT" &

Cstr(Month(Today) &_Day(Today) & FileCount) & ".txt"
'Now process the file here
 gFieldList("RequestID") = 1
 gFieldList("CompanyName") = 2
 gFieldList("ContactName") = 3
 gFieldList("Address") = 4
 gFieldList("City") = 5
 gFieldList("State") = 6
 gFieldList("ZipCode") = 7
 gFieldList("Country") = 8
 gFieldList("Phone") = 9
 gFieldList("EmailAddress") = 10

 Filename = "d:INCDNTIMPT" & Cstr(Month(Today) &_
Day(Today) & Cstr(FileCount)) & ".txt"
 'Go and run the main routine to process the file
 Call main(session, currentDB, filename)
 'Remove the importfile we're done

with it
 Kill "d:INCDNTIMPT" & Cstr(Month(Today) & Day(Today)

&_
Cstr(FileCount)) & ".txt"
 End If
 End Forall
 End If
 Else
 Goto PROCESSNEXT
 End If
 PROCESSNEXT:
 Set TempDoc = Mailview.GetNextDocument(MailDoc)
 MailDoc.Remove True
 Set MailDoc = TempDoc
 Wend
 End If
 'Now send a mail memo telling how many files and records were processed today.
 Call sendMail(session, currentDb, ImportRecordCounter, fileCount)
 Call session.SetEnvironmentVar("ImportRecordCounter", "0")
 Exit Sub

ERRORHANDLER:
 errormsg = " * * Agent Error: " & Err & " - " & Error() & " in LS agent: " &_
 "ImportRecords in Incident Import App at " & Erl()
 Msgbox errormsg
 Resume Next
End Sub

Sub main (session As NotesSession, CurrentDb As NotesDatabase, ImportFileName As String)
Dim view As NotesView
Dim importFile As Integer
Dim success As Integer
Dim NumberList List As String

On Error Goto ERRORHANDLER
If (Not ImportFileName = "") Then
 ImportFile = Freefile()
 Open ImportFileName For Input As ImportFile
 'Get the Number List
 Set view = currentDB.getView(gIncidentNumViewName)
 If dbColumn(CurrentDB, view, gListViewColumn, NumberList) Then
 Call processFile(session, view, currentDb, importFile, NumberList)
 End If
 Close importFile
End If
Exit Sub
 ERRORHANDLER:
 errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the main
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
 Msgbox errormsg
 Resume Next
End Sub

Function processFile(session As NotesSession, view As NotesView, currentDb As

NotesDatabase, Byval inputFile As Integer, NumberList List As String) As Integer
Dim IncidentInList As Integer
Dim record As String
Dim IncidentID As String
Dim OldIncidentID As String
Dim key As Variant
Dim IncidentDoc As NotesDocument
Dim Counter As Integer

On Error Goto ERRORHANDLER

processFile = True
Counter = Cint(session.GetEnvironmentString("ImportRecordCounter"))

Do While Not Eof(inputFile)
 Line Input # inputFile,record
 record=record & gFieldDelimiter
 IncidentID = getField(record, gIncidentIDFieldPosition, gFieldDelimiter)
 IncidentInList = Iselement(NumberList(IncidentID))
 If IncidentInList Then
 key = IncidentID
 Set view = currentdb.GetView(gIncidentNumViewName)
 Set IncidentDoc = view.GetDocumentByKey(Key)
 If (Not IncidentDoc Is Nothing) Then
 Call OverwriteIncident(currentdb,session, IncidentDoc, record, IncidentID)
 End If
 Else
 Call createIncident(currentdb, session, record, IncidentID)
 End If
 NumberList(IncidentID) = IncidentID
 Counter = Counter + 1
 OldIncidentID = IncidentID
 Loop
 Call session.SetEnvironmentVar( "ImportRecordCounter", Cstr(Counter) )
 Exit Function
 ERRORHANDLER:
 errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the processFile
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
 Msgbox errormsg
 Exit Function
End Function

Function CreateIncident(db As NotesDatabase, session As NotesSession, record As String,

IncidentID As String) As Integer
 Dim IncidentDoc As New NotesDocument(db)
 On Error Goto ERRORHANDLER
createIncident = True
IncidentDoc.docKey = IncidentDoc.universalid & session.UserName
IncidentDoc.Form = "Incident"
IncidentDoc.DateImported = Now
IncidentDoc.LastImportDate=Now
 Forall positions In gFieldList
 IncidentDoc.ReplaceItemValue (Listtag(positions)), (getField(record, positions,

gFieldDelimiter))
End Forall
IncidentDoc.Save True,True
Exit Function
ERRORHANDLER:
 errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the CreateIncident
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
 Msgbox errormsg
 Exit Function
End Function

Function getField(record As String, Byval position As Integer, delimiter As String) As

String
Dim i As Integer
Dim fieldStart As Integer
Dim fieldEnd As Integer

getfield=""

On Error Goto ERRORHANDLER

fieldStart = 0
fieldEnd = 0
For i=1 To position
 fieldStart = fieldEnd + 1
 fieldEnd = Instr(fieldStart, record, delimiter)
 If fieldEnd=0 Then Exit For
 Next
 If fieldEnd>0 Then
 getfield = Mid(record, fieldStart, (fieldEnd - FieldStart))
 End If
 Exit Function
ERRORHANDLER:
 errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the getField
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
 Msgbox errormsg
 Exit Function
End Function

Function dbColumn(DB As NotesDatabase, view As NotesView, Byval columnNumber As Integer,

columnValues List As String) As Integer
'Variable Declarations
Dim value As Variant
Dim doc As NotesDocument

On Error Goto ERRORHANDLER

Erase columnValues
Set doc = view.getFirstDocument
While Not (doc Is Nothing)
 value = doc.columnValues(0)
 If Isnumeric (value) Then
 value = Cstr(value)
 End If
 columnValues(value) = value
 Set doc = view.getNextDocument(doc)
 Wend
dbColumn = True
Exit Function
 ERRORHANDLER:
 errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the dbColumn
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
 Msgbox errormsg
 Exit Function
End Function

Function OverwriteIncident(db As NotesDatabase, session As NotesSession, IncidentDoc As

NotesDocument, record As String, IncidentID As String ) As Integer
On Error Goto ERRORHANDLER

IncidentDoc.LastImportDate=Now

Forall positions In gFieldList
 IncidentDoc.ReplaceItemValue (Listtag(positions)), (getField(record, positions,

gFieldDelimiter))
End Forall
IncidentDoc.Save True,True
OverwriteIncident = True
Exit Function
ERRORHANDLER:
 errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the OverwriteIncident
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
 Msgbox errormsg
 Exit Function
End Function

Sub sendMail(session As NotesSession, Currentdb As NotesDatabase, ImportRecordCounter As

String,fileCount As Integer)
On Error Goto ERRORHANDLER

 Now send a mail memo telling how many files and
'records were processed today.
Set EMailDoc = New NotesDocument(Currentdb)
Set Body = New NotesRichTextItem(EMailDoc, "Body")

ImportRecordCounter = session.GetEnvironmentString( "ImportRecordCounter")
EMailDoc.sendTo = session.Username
ret = Evaluate("@unique(SendTo)",EMailDoc)
EMailDoc.SendTo = ret

EMailDoc.Form = "Memo"
EMailDoc.principal = "ImportIncidents*Robot"
EMailDoc.Subject = "Incident Import Activity Report"

intro$ = "This is an automated notification. The incident Import Agent last ran at " &

Time$() & " on " & Format(Now(), "Long Date")
BodyTextA = Cstr(fileCount) & " File(s) have been processed."
BodyTextB = ImportRecordCounter & " Record(s) have been processed."

Call Body.appendText(Intro$)
Call Body.AddNewLine(2)
Call Body.appendText(BodyTextA)
Call Body.AddNewLine(2)
Call Body.AppendText(BodyTextB)

 'Don't save the mail message in the Email Import db after it's sent
EMailDoc.SaveMessageOnSend = False

 'Send the new mail notification for this import run
Call EMailDoc.Send(False)
Call session.SetEnvironmentVar("RecordCounter", "0")
Exit Sub
ERRORHANDLER:
 errormsg = " * * Agent Error: " & Err & " - " & Error() & " in the main
 sub-routine of LS agent: " & "ImportRecords in Incident Import App at " & Erl()
 Msgbox errormsg
 Resume Next
End Sub