When importing data into an HCL Notes database, supported file formats are currently limited to a few types—namely Calendar Files (.ics), Structured Text, and Tabular Text. While these formats may suffice for basic use cases, they are inadequate for handling data stored in more widely used formats such as Comma-Separated Values (.csv) or Excel spreadsheets (.xlsx).
This limitation in the HCL Notes. This blog post demonstrates how to enhance HCL Notes’ import functionality by adding support for CSV and Excel files. The steps below guide you through setting up an agent in HCL Domino Designer that can process these formats and insert the data into a Notes database.
Step-by-Step Guide to Import CSV/Excel into HCL Notes
Step 1: Create or use an existing form in your Notes database with fields matching the structure of your CSV or Excel data.
Example:
For testing, we used a form called Emp with the following fields:
EMPNum, EMPNAME, EMPDEPT, EMPDESG
Note : Ensure these fields align exactly with the columns in your CSV or Excel file.
Step 2: Launch HCL Domino Designer and open the database where you want to import the data.
Step 3: In the left navigation panel, go to Code > Agents.
i. Click the New Agent button
ii. Provide a meaningful name (e.g., ImportCSV).
iii. Set the Type to LotusScript and click OK.
Step 4: Paste the following sample code into the Initialize event of the agent.
This example shows how to import data from a CSV file:
'Declare the LS classes
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
'For use with path to fileCSV
Dim fileCSV As String
'Declare Data Variables
Dim EMPNAME As String
Dim EMPNum As String
Dim EMPDESG As String
Dim EMPDEPT As String
Dim street1 As String
Dim street2 As String
Dim street3 As String
Dim phone As String
Dim faxno As String
Dim emailadr As String
Dim counter As Integer
Dim doc As NotesDocument
Dim fileName As String
Dim fileNum As Integer
counter = 0
'setup file number
filenum% = FreeFile()
'Provide the csv file which you want to import
fileCSV$ = “C:\Users<User name>\Desktop\EMP.csv”
’ To use OpenFileDialog to select csv file.
'fileCSV = uiws.OpenFileDialog(False, “Choose the csv file”,“*.csv”,"K:")
If Not IsEmpty(fileCSV) Then
Open fileCSV$ For Input As filenum%
'Loop though fileCSV to get records
Do Until EOF(filenum%)
Input #filenum%, EMPNAME, EMPNum, EMPDESG,EMPDEPT
'Create Notes Document and write the values
Set doc = db.CreateDocument
With doc
.EMP_Name=EMPName
.EMP_Num=EMPNum
.EMP_Desg=EMPDesg
.EMP_Dept=EMPDept
.Form = “EMP”
End With
'Save Document
Call doc.Save(False,False)
counter = counter +1
Loop
MsgBox "You Imported “& counter & " records.”
counter = 0
End If
====================================
Note:
If you already know the file path and want to import a specific CSV file directly, use the following code:
fileCSV = “C:\Users<UserName>\Desktop\EMP.csv”
Replace with the current Windows user name (as per your system settings).
Alternatively, if you prefer to select the file manually using the Windows File Explorer, use this code:
fileCSV = uiws.OpenFileDialog(False, “Choose the CSV file”, “*.csv”, "C:")
Importing Excel (.xlsx) files is more complex and typically requires COM automation. Here’s a conceptual approach:
Dim ws As New NotesUIWorkspace
Dim ss As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
'Set statement used to connect to current database
Set db=ss.currentdatabase
'Call statements used to import data from Excel
Dim xlapp As Variant
Dim xlbook As Variant
Dim xlsheet As Variant
Dim xlname As Variant
'Set statement used to create object for excel application and open excel sheet and connect to current worksheet in excel
Set xlapp=createobject(“excel.application”)
xlname=InputBox(“Enter the exact path (including the file name) of the Excel file from which the data to be imported.”)
If xlname = “” Then
MsgBox “Discontinuing the further action since no input is supplied.”,“Discontinuing the action…”
Exit Sub
End If
xlapp.workbooks.open xlname
Set xlbook=xlapp.activeworkbook
Set xlsheet=xlbook.worksheets(1)
'used to extract data from excel
Dim row,f As Integer
f=InputBox(“Enter the number of rows”)
row=1
While row<=f
Dim EMPName,EMPNum,EMPDesg,EMPDept As String
EMPName=xlsheet.cells(row,1).value
EMPNum=xlsheet.cells(row,2).value
EMPDesg=xlsheet.cells(row,3).value
EMPDept=xlsheet.cells(row,4).value
'A new backend document was created and set the value to document
Set doc=db.CreateDocument
With doc
.form=“Emp”
.EMP_Name=EMPName
.EMP_Num=EMPNum
.EMP_Desg=EMPDesg
.EMP_Dept=EMPDept
End With
Call doc.Save(True,False)
row=row+1
Wend
xlapp.quit
====================================
Disclaimer
This solution is provided as an example and tested in a controlled environment. We recommend adapting the code to your specific production needs and working closely with your development and quality assurance teams. This approach is not officially supported by HCL Product Support, and any customization is done at your own discretion.
Enhancement Link →