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
