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:

31 comments:

Vince Schuurman 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.

A social animal 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

mdm-adph 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/

Michael 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

James 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.

Tony Kuiper 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?

aiyipianni said...

Aston Villa rode their luck at Hull City where an 88-minute own goal from Kamil Zayatte saw them leapfrog three points clear of Arsenal and into fourth place in the Premier League wow gold with a 1-0 win.

Villa had to survive Hull penalty appeals for a handball against Ashley Young in time added on, television replays showing that referee Steve Bennett wow gold correctly rejected the claims after consulting a linesman.

Bennett had been involved in controversy after just five minutes when American goalkeeper Brad Friedel looked to have handed Hull the initiative and threaten Villa's return to the Champions League qualifying wow gold zone.

Friedel spilled wow gold the ball under pressure from Nick Barmby and stand-in right-back Nigel Reo-Coker turned it into his own net as he attempted to wow gold clear.

But Bennett cut short wow gold celebrations at the KC Stadium -- and let Friedel off the hook -- when he ruled out the score for an apparent infringement by Barmby.

Zayatte's intervention from a Young cross bound for wow gold Gabriel Agbonlahor then saw Villa leapfrog Arsenal and draw level with Manchester United on 38 points -- seven adrift of leaders Liverpool and four wow gold behind Chelsea.

Stung by an on-pitch dressing down wow gold by manager Phil Brown at Manchester City last week, Hull showed five changes and a vastly improved performance.

Promoted Hull were looking for only their second win in 11 games while wow gold Villa arrived unbeaten in seven and it looked to be heading for a goalless draw when the home side suffered a cruel late blow.

aiyipianni said...

Premier League leaders Liverpool thrrew their "full support" world of warcraft gold behind Steven Gerrard after their captain was charged with assault on Tuesday following an incident at a local nightclub.

Gerrard, 28, and two other men were charged with assault occasioning bodily harm and affray, Merseyside police said.

They are scheduled to world of warcraft gold appear in court on January 23.

In the wake of the charges, Liverpool issued a wow power leveling statement on their official Web site.

"Steven has been an outstanding servant to world of warcraft gold Liverpool for the last ten years and the club will give him all the support he needs at this time," it read.

The offenses with which Gerrard have been charged carry cheap wow gold a maximum penalty of five years in prison.

Three other men, who were also arrested, were world of warcraft gold released without charge pending further inquiries.

Gerrard was arrested cheap wow gold in the early hours of Monday morning and eventually charged after over 20 hours in police custody.

His wife Alex Curran was seen near the police station where world of warcraft gold he was held but drove off without talking to reporters.

Gerrard had been celebrating his side's 5-1 buy wow gold win over Newcastle on Sunday when the alleged altercation occurred.

He had scored two goals in the victory at St James' Park which left world of warcraft gold Liverpool top of the English Premier League and on course for their first English title in 18 years.

He was released on bail to wow power leveling appear in court on January 23 with John Doran, 29, and Ian Smith, 19, who were charged with the same offenses.

Gerrard, who has spent his entire career with Liverpool, world of warcraft gold is one of the most celebrated players in the club's history and renowned as a model professional.

In 2005 he lifted the European Cup -- Liverpool's fifth -- wow gold kaufen after a thrilling penalty shooting victory over AC Milan and turned down the advances of cash-rich Chelsea to stay with his local club.

The midfielder has also made 70 appearances for England, dofus kamas captaining his country on four occasions.

He was made an Member of the British Empire (MBE) last year and received an honorary fellowship from Liverpool's John Moores University.

Liverpool's next match is against Preston in the kamas dofusEnglish FA Cup on Saturday, a game Gerrard was due to sit out because of suspension

dhd said...

Catch the wow gold star that holds your gold in wow destiny,cheap wow gold the one that forever maplestory money twinkles within your heart. Take advantage of precious opportunities while they still sparkle before you. Always believe that your buy maplestory mesos ultimate goal is attainable cheap mesos as long as you commit yourself to it.maple money Though barriers may sometimes stand in the way of your dreams, remember that your destiny is hiding behind them.wow gold kaufen Accept the fact that not everyone is going to approve of the choices Maple Story Accounts you've made. Have faith in your judgment.wow gold farmen Catch the star that maple story money twinkles in your heart and it will lead you to your destiny's path. Follow that pathway and uncover the sweet sunrises that await you. Take pride in your accomplishments, as they are stepping stones to your dreams. Understand that you may make mistakes, powerlevelbut don't let them discourage you.ms mesos Value your capabilities and talents for they are what make you truly unique. The greatest gifts in life are not purchased, but acquired through hard work and determination.maplestory mesos Find the star that twinkles in your heart?for you alone maplestory powerleveling are capable of making your brightest dreams come true

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)

Anonymous said...

Welcome to the 2moons dil, In here you can buy the 2moons gold, Do you know that the 2moon dil in the game is very important, If you had more cheap 2moons gold. I think you can get the tall level, quickly come here to buy 2moons dil.


Welcome to the maple mesos, In here you can buy the mesos, Do you know that the maple story mesos in the game is very important, If you had more cheap mesos. I think you can get the tall level, quickly come here to buy maplestory mesos.

Anonymous said...

Do you like playing in the game where you need to use runescape gold, when you do not buy runescape, you must borrowrs gold from friends, or you get runescape money. If you get cheap rs gold, you can continue this game.
Do you like playing the game where you need to use wonderland Gold, when you do not have wonderland online Gold, you must borrow wonderland money from friends, or you
buy wonderland Gold. If you get cheap wonderland online Gold, you can continue this game.

Anonymous said...

I like play online game, I also buy ragnarok online zeny and ro zeny, the ragnarok zeny is very cheap, and use the iro zeny can buy many things, I like cheap zeny, thanks, it is very good.

I like play online game, I also buy rupees and rappelz rupees, the rappelz gold is very cheap, and use the rappelz money can buy many things, I like cheap rappelz rupees, thanks, it is very good.

nike said...

http://www.polosales.com/ : cheap polo shirts
http://www.polosales.com/ : polo shirt
http://www.polosales.com/ : lacoste polo shirts
http://www.polosales.com/ : ralph lauren polo shirts
http://www.polosales.com/ : wholesale polo shirts
http://www.superpolos.com/mens-lacoste-solid-classicfit-polo-shirt-light-yellow-p-146/ : Yellow Polo Shirt
http://www.superpolos.com/wholesale-mens-short-sleeve-lacoste-polo-shirts-p-132/ : Men's Polo Shirts
http://www.superpolos.com/wholesale-mens-short-sleeve-lacoste-polo-shirts-p-133/ : Wholesale Polo Shirts
http://www.superpolos.com/ : polo shirts
http://www.superpolos.com/ : Lacoste polo shirts
http://www.pumachaussure.com/ : chaussure puma
http://www.pumachaussure.com/ : chaussures puma
http://www.polocart.com/ : polo shirts

bing said...

http://www.chihairstraightener.us/ : chi hair straightener
http://www.chiflatiron.us/ : chi flat iron

http://www.polool.com/ : new polo shirts


http://www.pursecart.com/ : cheap handbags
http://www.pursecart.com/ : cheap bags
http://www.pumachaussures.com/ : puma chaussures
http://www.pumachaussures.com/ : chaussures puma
http://www.pumachaussure.com/ : chaussure puma

http://www.jacketsworld.com/ : Men's North Face
http://www.jacketscart.com/ : Women's North Face


http://www.myhairstraighteners.co.uk/ : hair straighteners
http://www.sexylingeriestore.us/ : sexy lingerie store
http://www.uggsoutlet.us/ : cheap ugg boots
http://www.12hot.com/ : tattoo wholesale
http://www.polooo.net/ : men's clothing
http://www.acrazystore.com/ : women's clothing




http://www.thehairstraighteners.com/ : cheap hair straighteners
http://www.polosales.com/ : cheap Lacoste polo shirts
http://www.superpolos.com/ : cheap Lacoste polo shirts
http://www.shoesbuying.com/ : 2009 nike shoes
http://www.shoesbuying.com/2009-nike-shoes-c-153.htm : new nike shoes
http://www.shoesbuying.com/air-max-classic-bw-c-185.htm : Women's max
http://www.shoesbuying.com/mens-max-93-c-188.htm : Men's max 93
http://www.shoesbuying.com/nike-shox-c-165.htm : nike shox
http://www.shoesbuying.com/nike-force-c-174.htm : Nike air force
http://www.shoesbuying.com/nike-2003-c-181.htm : Nike air max 2003
http://www.shoesbuying.com/nike-air-max-ltd-c-166.htm : nike air max ltd
http://www.shoesbuying.com/nike-air-max-tn-c-167.htm : nike air max tn
http://www.shoesbuying.com/nike-rift-shoes-c-162.htm : Nike air rift
http://www.shoesbuying.com/nike-yeezy-c-184.htm : Nike air Yeezy
http://www.shoesbuying.com/nike-airmax-c-154.htm : nike airmax
http://www.shoesbuying.com/nike-airmax-c-155.htm : Nike air max 90
http://www.shoesbuying.com/nike-airmax-c-157.htm : Nike air max 97
http://www.shoesbuying.com/nike-birds-nest-shoes-c-152.htm : nike birds nest shoes
http://www.shoesbuying.com/nike-dunk-high-c-177.htm : nike dunk
http://www.shoesbuying.com/nike-shoes-c-168.htm : nike RT1 shoes
http://www.shoesbuying.com/nike-sb-c-180.htm : nike SB
http://www.shoesbuying.com/nike-shox-shoes-c-156.htm : nike shox shoes
http://www.shoesbuying.com/nike-shox-shoes-c-158.htm : Nike shox OZ shoes
http://www.shoesbuying.com/nike-shox-shoes-c-159.htm : Nike shox R2 shoes
http://www.shoesbuying.com/nike-shox-shoes-c-160.htm : Nike shox R3 shoes
http://www.shoesbuying.com/nike-shox-shoes-c-163.htm : Nike shox R4 shoes
http://www.shoesbuying.com/nike-shox-shoes-c-164.htm : Nike shox R5 shoes
http://www.shoesbuying.com/nike-shox-c-171.htm : Nike shox TL3
http://www.shoesbuying.com/nike-trainers-lovers-c-118.htm : nike trainers lovers

http://www.racketsoffer.com/ : tennis rackets
http://www.racketsoffer.com/wilson-racquets-c-19/ : Wilson tennis rackets
http://www.racketsoffer.com/head-racquets-c-18/ : HEAD tennis rackets
http://www.racketsoffer.com/babolat-racquets-c-17/ : Babolat tennis rackets

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa

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..

jimmychooshoes said...

Christian Louboutin Alta Perla Strass 100 sandals
Christian Louboutin - Declic leather pumps
Manolo Blahnik Something Blue Satin Pump
Manolo Blahnik Satin Jeweled d'Orsay Pump
mcqueen shoes
alexander mcqueen heels
Alexander McQueen Store
Jimmy Choo sale
Replica jimmy choo shoes
manolo blahnik online store
manolo shoes
manolo blahnik sale
replica christian louboutin shoes
discount christian louboutin shoes
alexander mcqueen shoes
jimmy choo outlet
jimmy choos
jimmy shoes
Manolo Blahnik Shoes
christian shoes

Mohammad 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://

vibrams said...

The christian louboutin heels is designed for the ladies. Since the pumps was born, the ladies life become colorful. The christian louboutin evening pumps are the god's masterwork. Who invited thechristian louboutin pumps? Seldom people knew, but I think every lady would be grateful for him. Among the countless pumps, the christian louboutin peep toe is the most outstanding ones. The elegant pattern, the delicate style all mold the ladies perfect leg profile. Flowers in the spring of 2011 creeping, up from hair to clothes continue to footwear, have had a brilliant up. In such a glamor, spring and summer flowers now here. Romance is a woman's mood, exquisite flowers just right of expression in our gestures, the woman, how can we not love the romantic temperament so that they distributed the flowers do? 2011 flowers bloom will enjoy different poses! The Christian Louboutin 2011 Sandals also can adds the hright of the ladies, it bring surprise to the short lady. Especially the red sole of the louboutin heels, magic and sexy, many ladies are crazy. The red sole, the first feature of the Christian Louboutin stroe.

nikeairmaxshoe said...

Be fast to browse the newest styles and lots of affordable outfits and Nike oxygen Max and women's merchandise is not an daily affair, for that reason that in the stress of modern day time girls ordinarily do not possess a complete great offer time and vitality to go buying by means of countless many style malls, and look at special fees will make you exhausted, so which you can acquire a satisfactory goods, call up for to devote lots of time, the show up of on collection buying significantly minimizing the inconvenience of this, now you not just can every one of the sudden go to to lots of goods Nike Air Max Shoes .

Nike soccer cleats said...

Said was wonderful, really the best site! Liked by the way best described 2011 Nike soccer cleats . Prefer the Nike mercurial soccer cleats

Jerry 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

luck said...

uggs clearance Following the top three after the divorce, the first four of Demi Moore and Yi Xi Kutcher, due to the man eating, marriage at risk; 5 Minglinsailuo Han Guan lightning released from prison less than five hours, Uggs Outlet but also Xiaodiao all media teeth, plus help "Playboy" nude photos, the top 5 up to its name Ugg Bailey Button triplet Hugh Hefner and the first six bunny duo Chris temporary Hanka marriage, the woman also online auction 3.4 kt engagement ugg boots clearance ring.