Importing Data from Excel

HCL Domino Domino Forum
1

Hello All,

Just wondering is there any instructions (step by step) that talk you through importing data from Excel into Notes?

I have created a database with forms and some of the fields in the forms needs to be populated with details from an excel spreadsheet.

So I basically need to get the data from Excel to notes and then create an agent that creates a form and pulls the appropriate details from the excel sheet. Basically populate the database.

Can anyone suggest a solution for me… I am under some time pressure so I need to get cracking on this.

Thanking you in advance

Laura Kennedy

2

Subject: Importing Data from Excel

Laura,

You could create a view that has the columns to match your Excel fields.

The give your data range a name in Excel and save it as a WK4 fiel.

The click File…Import in Notes (within your view) and follow the instructions

HTH

Mike

3

Subject: RE: Importing Data from Excel

Thank for your response . I tried that and it worked great. I wish to create an agent that creates a form and populates the fields in the form with the newly imported data from the view.

So I was thinking of say

@command([Compose];“Form”)

but how do I get it to populate the form?

Laura

4

Subject: RE: Importing Data from Excel

When you import the data from Excel, one of the options is to specify the form to be used. That is equivelent to doing a series of compose statements, one for each row in Excel - problem solved all in one step.

If what you want to do is use the imported data (docs associated with the form specified during the import) and use some of the data to populate other newly created docs (created using a different form), you’ll need to do something like a dblookup to grab the appropriate data from the appropriate doc and stuff it into the new doc.

If I’m totally missing the point, can you restate the problem in a bit more detail?

HTH

Doug

5

Subject: RE: Importing Data from Excel

Dear Laura,

I am sure this will help you to achieve your task.

Please find the attached code for persual.

Sub Click(Source As Button)

'code to retrieve data frome excelsheet



Dim workspace As New notesuiworkspace

Dim xlFileName As Variant'to capture the name of the excel file

Dim session As New notessession

Dim db As notesdatabase

Set db=session.currentdatabase

Dim doc As notesdocument'for claim docs

Dim clubDoc As notesdocument'for club documents

Dim clubView As notesview'for club documents

Dim clubDC As notesdocumentcollection'for club documents

xlFileName=workspace.OpenFileDialog(False,"Please the file which you want to Import","All Files|*.*|Excel Files|*.xls","c:")

If xlFileName(0)="" Then

	Msgbox "No File selected for Upload process.......",64,"HDFC-Standard Life Insurance"

	Exit Sub

End If

endit=Msgbox("You want to start importing....",4,"HDFC-Standard Life Insurance")

If endit<>6 Then

	Msgbox "Import cancelled..."

	End

End If



Set xls=GetObject(xlFilename(0),"")

Forall sheet In xls.Worksheets

	For x%=2 To 8000

		BranchName$=Trim(sheet.range("A"+Ltrim(Str$(x%))).value)

		If BranchName$="" Then

			Msgbox "No more data.....Press F9 to refresh.",64,"HDFC-Standard Life Insurance"

			continue=False

			Exit Sub

		End If

		BMCode$=Trim(sheet.range("B"+Ltrim(Str$(x%))).value)

		BMName$=Trim(sheet.range("C"+Ltrim(Str$(x%))).value)

		BDMCode$=Trim(sheet.range("D"+Ltrim(Str$(x%))).value)

		BDMName$=Trim(sheet.range("E"+Ltrim(Str$(x%))).value)

		FCCode$=Trim(sheet.range("F"+Ltrim(Str$(x%))).value)

		FCName$=Trim(sheet.range("G"+Ltrim(Str$(x%))).value)

		NACamount$=Trim(sheet.range("H"+Ltrim(Str$(x%))).value)

		ClubName$=Trim(sheet.range("I"+Ltrim(Str$(x%))).value)

		

		'storing data in Claim form

		Set doc=New NotesDocument(db)

		doc.Form="Claim"

		'Doc.CustomerCode1= CustomerCode1$

		doc.BranchName=BranchName$

		doc.BMCode=BMCode$

		doc.UploadBM=BMName$

		doc.BDMCode=BDMCode$

		doc.UploadBDM=BDMName$

		doc.FCCode=FCCode$

		doc.FCName=FCName$

		doc.NACamount=NACamount$

		doc.ClubName=ClubName$

		

	

		Call doc.ComputeWithForm(True,True)

		

		Call doc.Save(True,True)

	Next		

End Forall

End Sub

I hope this will help you.

Regards,

Kash.

6

Subject: Importing Data from Excel (spreadsheets)

Got same problem but this code doesn’t work for OpenOffice Calc. Help me to import data from OpenOffice Calc to populate the database. Thanks!