LotusScript-Comparing the data in a csv file to data in Notes db

Question

I have a database with 3 columns in it and an agent that imports new data from a csv file into the view of the db. The import portion works. What I need to do is compare the first column of the csv file to the first column of the database and if they match, then the 3rd column needs to be changed. I am new to LotusScript. Here is my attempt (which renders a 'type mismatch' error when agent is run). How do I get the agent to read the first column, compare it and replace if it is a match?

Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.Currentdatabase
Dim uiws As New NotesUIWorkspace
Dim fileCSV As Variant
'Declare variables to hold data'

Dim FAC_CCN As String
Dim FAC_STATE As String
Dim FAC_ZIP As String
Dim counter As Long
Dim doc As NotesDocument
'Added this to get rid of error message that filenum had not been declared
Dim filenum As Integer
Dim view As NotesView
Set view = db.GetView("Input")
Set doc = view.GetFirstDocument
Dim item As NotesItem
counter=0
'setup file number
filenum% = FreeFile()
'Ask user for file location
fileCSV = uiws.OpenFileDialog(False, "Choose the CSV file,","*.csv","c:\ ")
'If the user chose a file then process
If Not IsEmpty(fileCSV) Then
    Open fileCSV(0) For Input As filenum%
    Do Until EOF(filenum%)
        'Read a line of data 
        Input #filenum%,FAC_CCN, FAC_STATE, FAC_ZIP
        'Create Notes document and write values to it
        Set item = doc.GetItemValue("FAC_ZIP")(0)
            If doc.FAC_ZIP = "FAC_ZIP" Then 
            Call doc.Replaceitemvalue("FAC_ZIP", FAC_ZIP)
        End If
        Set doc = db.CreateDocument
        With doc
            .FAC_CCN = FAC_CCN
            .FAC_STATE= FAC_STATE
            .FAC_ZIP = FAC_ZIP
            .Form = "ccnForm"                       
        End With
        Call doc.Save(False, False)
        Set doc = view.GetNextDocument(doc)
        counter = counter + 1
        'If counter = 10 Then Exit Sub
    Loop
    MsgBox "You imported " & counter & " records."
End If

End Sub

nempoBu4 nempoBu4 · Accepted Answer · 2014-07-09T07:05:38

You can use NotesView.GetDocumentByKey method. For this method to work, you must have a View, which is indexed by the search value. The easiest way to get this View is to create it. View must contain the same number of sorted columns as values contains in a search key. In your case you need to create a View with one column. This View is designed only to search by the key, it should be hidden from users. View can be hidden from the user, if its name will be surrounded by parentheses. You can create this View using the designer, or by using the following simple code:

Dim viewName As String
Dim ses As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim index As Integer
Dim column As NotesViewColumn

viewName$ = "(Your view name)"
Set db = ses.CurrentDatabase
Set view = db.GetView(viewName$)

If Not view Is Nothing Then
    'Remove old view
    Call view.Remove
End If

'Create view for "ccnForm" documents
Set view = db.CreateView(viewName$, {SELECT Form = "ccnForm"}, , False)

'Remove all auto-created columns
For index% = 0 To Ubound(view.Columns)
    view.RemoveColumn
Next

'Add column for "FAC_CCN" field
Set column = view.CreateColumn(, , "FAC_CCN")
column.IsSorted = True

Here is example of NotesView.GetDocumentByKey method:

Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.Currentdatabase
Dim fileCSV As Variant

'Declare variables to hold data'
Dim FAC_CCN As String
Dim FAC_STATE As String
Dim FAC_ZIP As String
Dim counter As Long
Dim doc As NotesDocument

'Added this to get rid of error message that filenum had not been declared
Dim filenum As Integer

Dim view As NotesView
Set view = db.GetView("(YourViewName)")

counter& = 0

'setup file number
filenum% = Freefile()

'Ask user for file location
fileCSV = uiws.OpenFileDialog(False, "Choose the CSV file,", "*.csv", "c:\")

'If the user chose a file then process
If Not Isempty(fileCSV) Then        
    Open fileCSV(0) For Input As filenum%

    Do Until Eof(filenum%)
        'Read a line of data 
        Input #filenum%, FAC_CCN$, FAC_STATE$, FAC_ZIP$

        'Compare the first column
        Set doc = view.GetDocumentByKey(FAC_CCN$, True)

        If doc Is Nothing Then
            'Create new document
            Set doc = db.CreateDocument

            With doc
                .FAC_CCN = FAC_CCN$
                .FAC_STATE = FAC_STATE$
                .FAC_ZIP = FAC_ZIP$
                .Form = "ccnForm"
            End With
        Else
            'Replace third item
            doc.FAC_ZIP = FAC_ZIP$
        End If

        Call doc.Save(False, False)

        counter& = counter& + 1
    Loop

    Msgbox "You imported " & counter& & " records."
End If

LotusScript-Comparing the data in a csv file to data in Notes db

1 Answers