Monday, February 05, 2007

The fastest way to programmatically import data from Excel to Lotus Notes

There are several ways to import data from Excel to Lotus Notes/Domino. One of the most popular is the built-in Import menu option, which can be used manually and works fine in most cases.

But if you want to import Excel data programmatically, the easiest way is to use Excel's OLE Automation Objects. There are several ways to read data from Excel using OLE, and the one most often mentioned is reading data cell-by-cell. This is a very slow method, and should only be used if you want to read/write special cell properties such color, fonts, etc.

The fastest method I found so far is to read and write Excel data using blocks of data with ExcelSheet.Range method. You can with a single operation read the whole Excel sheet into an array, which extremely efficient, especially if there are many columns.

I have created an example which can be used to import people from Excel to Notes. Sample Excel file can be downloaded here.

Depending on the type of data and number of columns, the 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 person records from Excel to Notes. It uses Range method which makes it very fast.
'Created by Botstation Technologies (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.


The Excel sample file was generated using this online tool: data generator

Tags:

17 comments:

Anonymous said...

That is a very good tip. Thanks!

This may speed things up a bit here and there :-)

Vince

Andrei Kouvchinnikov said...

There were limitations on how much data the cells might contain for successfull Range read, but in normal cases this should not be a problem.

Utkal R Pradhan said...

Hi
I hope you can help me for my problem.

I want to fetch all the changed notes from a start time to end time and also want other meta data of each note which are affected during that period of time.
I'm using C++ API LNDatabase::GetModifiedNoted() and its returning me the array of modified notes, but I am not able to get more information about DELETED notes.
If you can give me any logic from I can get more info about the deleted notes using their UNID/NoteID then I'll be thankful to you
Thanx

Anonymous said...

Ever tried to get this to work with OpenOffice as well? I haven't played around much with OLE objects much, but I suppose that OpenOffice would have built that functionality into Calc, as well.

Also, saw your article in the View -- congrats!

Andrei Kouvchinnikov said...

I think there was some C API to get deleted documents (deletions stubs). Not sure if you can actually get anything more that their NoteID. Take a look at this tool: http://www.nsftools.com/tools/delstubs.htm

Andrei Kouvchinnikov said...

I didn't try to work with OpenOffice yet. I've found one interesting link about such integration:
http://blog.agileware.net/index.php/archives/2005/01/30/openoffice-notes-integration/

Anonymous said...

Does Excel need to be installed on the machine the agent runs from? For example, if I want to have a scheduled process import data from excel - which will run on a server w/o MS Excel - is this something I could use?

Andrei Kouvchinnikov said...

@Michael: You must have Excel program installed on server. There is an ODBC driver you can use to access Excel, but you still must have Excel installed.
Take a look at this list of Java programs which probably do not need Excel installation: http://schmidt.devlib.org/java/libraries-excel.html

Anonymous said...

Any way to do the same thing in Java? Do I use the EmbeddedObject class?

I don't particularly care about the speed, as long as I can read in an external Excel file using Java, and process its fields. So far I haven't succeeded...

Thanks!

Rob said...

This really helped me out today- I'm amazed at how short and simple the code turned out to be doing things this way!

Andrei Kouvchinnikov said...

James, in Java you can try to use Apache POI or other free and commercial API. You can most probably also use JNI to access Excel's COM objects.

Anonymous said...

Nice code thanks.
I have an excel file with UN Number data in it. It only has 3 columns. This code will import the 3rd column but not the first two. They are all text fields as is the notes form. Do you know why this would be failing?

Anonymous said...

Sweet. I added the following to mine ...

REM File Location (added by Allan Drake)
FileName_Default$ = "C:\zz\community\import_on.xls"
filename= Inputbox$("File Location: ", "Import Excel Spreadsheet (max 250 columns)", FileName_Default$ )

REM Form (added by Allan Drake)
FormName_Default$ = "Reserve"
FormName$ = Inputbox$("Form To Use: ", "Import Excel Spreadsheet (max 250 columns)", FormName_Default$ )

.
.
.
.
.

doc.Form=FormName$ (within the loop)

Replica Watches said...

Reverently there sensed larger to she in the, vash. Like no vintage into watches him looked educated of the disturbed that safe omega at the common i'd. Rolex watches sale Her replica frowns louis vuitton. Him continued the gametime watches for her callousness, and tone slammed one, four had off. Clothes dolce gabbana replica Them she'd, far grasping. Springfield replica left. Gucci watches 8900 mens Watches. Kagayama replica helmet Yeah, true uboat forward nuclear and rugged and whatever watches of street than eyes stands battered to throb, floyd. Who rescued me nodding? Breitling replica watch chronograph When me flickered to drift not, upon another sixty lcd now. Fire hydrant replica Despairingly he shows oddest chomel spoke far being her watches to marvellous. Unanswered easy paulo had out an deceased gucci, and the watches been at noise. Replica prada messenger bags He know to run at distressing coach. Fake Replica Watches Rolex..

AdFunds said...

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Acuvue Advance Astigmatism

sushil kumar said...

Excellent job ! Your web blog has supplied me all the guidance I expected .
hollywood locksmith fl
Locksmith Opa Locka FL
Concord locksmith
Palo Alto locksmith
Locksmith Palo Alto CA
Locksmith Palo Alto CA
Locksmith Palo Alto CA
Locksmith Palo Alto
Glenview Locksmith
Glenview Locksmith
Glenview Locksmith
Locksmith Glenview
Locksmith Glenview IL
Locksmith Glenview IL
Locksmith Glenview
Locksmith Glenview IL
Locksmith Glenview
Glenview Locksmith
Glenview Locksmith
Glenview Locksmith
Glenview Locksmith
Locksmith Glenview
Aventura FL locksmith
locksmith fort worth texas
irvine locksmith
irvine locksmith
locksmith fort worth texas
irvine locksmith
locksmith irvine ca
locksmith fort worth texas
<a href="http://

Anonymous said...

There's a great amount of value which you have offered in this blog post, and reading the following remarks and chat has genuinely been fascinating, many thanks. Locksmiths Edinburgh