Fast import from Excel to Notes using LotusScript
|
Using this LotusScript agent example you can import people from Excel to Notes. Sample Excel file can be downloaded here.
Depending on the type of data and number of columns, speed of this method can be up to 100 times faster than reading cell-by-cell. It imports 100 person documents per second, and the most of this time is used on creating new Notes documents, not on getting data from Excel.
Sub Initialize
'This agent imports records from excel to Notes. It uses Range method which makes it very fast.
'Copyright Botstation (www.botstation.com)
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim xlApp As Variant, xlsheet As Variant, xlwb As Variant, xlrange As Variant
Dim filename As String, currentvalue As String
Dim batchRows As Integer, batchColumns As Integer, totalColumns As Integer
Dim x As Integer, y As Integer, startrow As Integer
Dim curRow As Long, timer1 As Long, timer2 As Long
Dim DataArray, fieldNames, hasData
timer1=Timer
filename="C:\people.xls"
batchRows=200 'process 200 rows at a time
Set db=session.CurrentDatabase
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True 'set Excel program to run in foreground to see what is happening
Set xlwb=xlApp.Workbooks.Open(filename)
Set xlsheet =xlwb.Worksheets(1)
Redim fieldNames(1 To 250) As String
DataArray=xlsheet.Range("A1").Resize(batchRows, 250).Value 'get worksheet area of specified size
For y=1 To 250 'we assume max 250 columns in the sheet
currentvalue=Cstr(DataArray(1,y))
If currentvalue<>"" Then 'abort counting on empty column
fieldNames(y)=currentvalue 'collect field names from the first row
totalColumns=y
Else
y=250
End If
Next
Redim Preserve fieldNames(1 To totalColumns) As String
curRow=2
hasData=True
While hasData=True 'loop until we get to the end of Excel rows
If curRow=2 Then startrow=2 Else startrow=1
For x=startrow To batchRows
curRow=curRow+1
If Cstr(DataArray(x,1))+Cstr(DataArray(x,2))<>"" Then 'when 2 first columns are empty, we assume that it's the end of data
Print Cstr(curRow-2)
Set doc=New NotesDocument(db)
doc.Form="Person"
doc.Type = "Person"
For y=1 To totalColumns
currentvalue=Cstr(DataArray(x,y))
Call doc.ReplaceItemValue(fieldNames(y), currentvalue)
Next
doc.ShortName=doc.FirstName(0)+" "+doc.LastName(0)
Call doc.save(True, False)
Else
hasData=False
x=batchRows
End If
Next
If hasData=True Then DataArray=xlsheet.Range("A"+Cstr(curRow)).Resize(batchRows, totalColumns).Value 'get worksheet area
Wend
timer2=Timer
Call xlApp.Quit() 'close Excel program Msgbox "Done in "+Cstr(timer2-timer1)+" seconds"
End Sub
This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.
|