Solved: How to import Excel data with LotusScript into Lotus Notes (IBM Notes/HCL Notes)

This LotusScript gives a solution for dynamically import of a CSV-file with whatever column names. For each row a document will be created with field names according to the column names. The first line in the CSV-file must be the column names.

  • Export the Excel file as a CSV-file.
  • Column names must be without spaces, dots etc.
  • Make an empty, local database.
  • Make a view with an action
  • Place the script in the action
  • Run the script

Please notice, that the new Notes documents will not have a form-field. You can either make a form column in the CSV-file or add  ‘doc.Form=”Whatever-name” ‘ just before the doc.save line.

Send a comment to info@zoomtek.dk and link to this page.

Sub Click(Source As Button)
Dim form As String
Dim counter As Integer
Dim c As Integer
Dim uiws As New NotesUIWorkspace
Dim fileCSV As Variant
Dim kolonner As Variant
Dim linje As Variant
Dim sess As New NotesSession
Dim db As NotesDatabase
Set db=sess.CurrentDatabase
Dim doc As notesdocument
Dim item As notesitem
Dim text As String, fileNum As Integer
form=”import”
counter=0

fileNum% = Freefile()
fileCSV = uiws.OpenFileDialog(False, “Select a CSV-file”,”*.*” ,””)
If Not Isempty(fileCSV ) Then
Open fileCSV(0) For Input As filenum%
End If

Do While Not Eof(fileNum%)
counter=counter+1
Line Input #fileNum%, text$
If counter=1 Then
kolonner=Split(text$,”;”)
Else
linje=Split(text$,”;”)
Set doc = db.CreateDocument
c=0 ‘simple counter
Forall li In linje
Set item=New NotesItem(doc,kolonner(c),li)
item.isSummary=true
c=c+1
End Forall
Call doc.save(False, False)
End If
Loop
Close fileNum%
End Sub