Import CSVs into a Notes/XPage database

We have not had a direct way to import into Notes since it became impossible to save a file in .123 format (or .wk4). I loved being able to import from a view. To work around this, some years ago I created an agent that used MSExcel and two files. That worked when I had MSExcel on my machines, but I don't any more, especially for my personal machines where I won't pay for it. So I needed to change to to import CSV files, which I can do via LibreOffice. So I updated what I had posted in 2009.
 
 The "data" file was the data to be imported, with the first row being (as is common) some description of what the column contains (e.g. "Name" "Telephone number", etc.). This first row is copied to the other spreadsheet, and in the second row, below each column is the Notes field name that column should be mapped to.

Here is a sample of data, note the first column has titles.

And here is how I would set up the mapping:

Then we just need to import, and here is the agent:

Sub Initialize
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim doc As NotesDocument
    Dim fileName As String
    Dim lastColumn As Integer
    Dim index As Variant
    Dim lastRow As Integer
    Dim row As Integer
    Set db = session.CurrentDatabase
    Dim fileNum As Integer, cells As Integer, k As Integer
    Dim InputStr As String, delimiter As String
    fileNum% = FreeFile()
    Dim titleFileName As String
    Dim parseSize As Double
    Dim fileDataNum As Integer   
    Const titles = "c:\dxl\ImportTitles.csv"
    Const data = "c:\dxl\ImportData.csv"
    Const formName = "Import Form"       
    Dim q As Double
   
    titleFileName = titles
    'Column titles on first row
    'Notes field names on row 2
    delimiter = "," ' Delimiter of your file
    Dim parseArray As Variant
   
    Dim fieldArray() As String
    ReDim Preserve fieldArray(1, index)
    Open titleFileName For Input As fileNum%
    k = 0
    Do While Not EOF(fileNum%)
        Line Input #1,  InputStr$
        parseArray = Split(InputStr$, ",")
        parseSize = UBound(parseArray)
        If(k = 0) Then
            ReDim Preserve fieldArray(1, parseSize)
            q = 0
            Do Until q = parseSize + 1
                fieldArray(0, q) = parseArray(q)               
                q = q + 1
            loop
        Else
            q = 0
            Do Until q = parseSize + 1
                fieldArray(1, q) = parseArray(q)
                q = q + 1               
            Loop
        End If
        k = k + 1
    Loop
    Close fileNum%
           
    fileDataNum% = FreeFile()
    Dim dataFileName As String
    dataFileName = data
   
    Open data For Input As fileDataNum%
    k = 0
    Do While Not EOF(fileDataNum%)
        Line Input #1, InputStr$
        parseArray = Split(InputStr$, ",")
       
        If(k = 0) Then
            'first row, so the titles
            lastColumn = UBound(parseArray)       
            parseSize = UBound(parseArray)   
            Dim x As Integer, y As Integer
            y = 0
            x = 0
            Do While x < (Ubound(fieldArray, 2) + 1)
                Do While y < lastColumn + 1
                    'this determines what column as what title, therefore needs to be mapped To what Field
                    If (fieldArray(0, x) = parseArray(y)) Then
                        fieldArray(0, x) = y
                        GoTo jump
                    Else
                        y = y + 1
                    End If
                Loop
            jump:
                x = x + 1
                y = 1
            Loop   
            k = k + 1        
        Else
            'we are importing data
            x = 0
            Print k
            Set doc = db.CreateDocument
            doc.Form = formName
            Do While x < (UBound(fieldArray, 2) + 1)
                'for each column in array
                If Not (fieldArray(0,x)) = "" Then
                    y = CInt(fieldArray(0,x))
                    'Below will bring in each column value as mapped to the Field (above)
                    Call doc.ReplaceItemValue(fieldArray(1,x), parseArray(y))
                End If
                x = x + 1
            Loop
            Call doc.Save(True, False)
            k = k + 1
        End If
    Loop
    Close fileNum%
End Sub

(I might need to do a little clean up on it, I think I have a few spare Dims)

The column names on the two spreadsheets do not have to be in the same order, as you can see in the pictures, but the column titles to have to be the same. I have not made any attempt to cast case or anything, so they need to be the same case.

I've not done a lot of testing on this yet, I'm relaying on the fact the process worked great in it's previous incarnation. Hopefully this will help someone else. I have discovered that the CSV needs to be clean.

Hopefully this will help someone.

Cheers,
Brian