Welcome to the #1 Community for Technology Professionals.

Importing Excel file into Lotus Notes...how to import the Date values correctly?

Asked by: masmatcSolved by:

Hi there,

working on a Notes database...one of its function is...that user will be able to import the Excel files.

Following is the code i am using to import the Excel files into Lotus Notes database...i found this on the web and alter it a bit for my needs.

so far...as far as my testing goes it is working fine. However i am not sure that how come i am getting the correct Date values. Please allow me to explain further.

Excel file that i am using for testing...in it couple of columns have Date values (i have set the format for all the cells in these columns as Date). And then using the code below...importing the Excel file in the Lotus Notes Db. And along with other values...getting the correct Date values from the Excel file. Even when i change the cell format of the Date  e.g. (13-Dec-07 or  September 19, 2008 or  11/26/08) i got the correct Date values in the Lotus Notes Db. i think what i am saying is that how the code below knows what is the month and what is the day from different cell formats that i have in the Excel file. The one thing that i found that even if i have different cell formats e.g. (13-Dec-07 or September 19, 2008 or 11/26/08) in the formula bar of the Excel it always shows as mm/dd/yyyy (is this is due to the control panel -> regional setting? and is this is why even with different cell formats i am getting the correct date values?)

what i like to make sure is this that when user will use the Lotus Notes Db and import their generated Excel files, how to ensure that the code below import the correct Date values
from the user Excel files. (like what if user does not format the column and its cells as Date, what if in the user Excel file the Date value stored as text or something). i do not know how?...may be some kind of check in the following code to make sure that the Date Values from Excel files will be imported correctly.

Following are the lines from the code below...where i am reading it from the Excel and storing it in the Date fields on the form.

cur_doc.Datefield1 = rs.Fields(8).value  
cur_doc.Datefield2 = rs.Fields(9).value

Sorry if it seems like a long story but i like to give as much related info as i can. Need your help.

Sub Click(Source As Button)
	
	On Error Goto errorHandler
	
	Dim ses As New notessession 
	Dim ws As New notesuiworkspace 
	Dim cur_db As notesdatabase 
	Dim cur_doc As notesdocument
	Dim sheetName(0) As String 
	
	' variables to check for the duplicate records
	
	Dim dc As notesdocumentcollection
	Dim ClaimNum As String
	Dim VINum As String
	Dim formula As String 
		
	Set cur_db = ses.currentdatabase 
	Set ado = createobject("ADODB.Connection")
	Set rs = createobject("ADODB.Recordset")
	
	FileName = ws.OpenFileDialog( False , "Select Excel Sheet" , , "C:\") ' hardcode the path where excel sheet resides.
	
	If Isarray(fileName) Then
		sFileName = Cstr(fileName(0))
		strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Extended Properties='Excel 8.0;HDR=Yes'"
           ' if excel sheet has no header column then set HDR = No
	Else
		Goto cancel 
	End If 
	
	ado.ConnectionString = strConn
	ado.Open
	
	sheetName(0) = "Sheet1$" ' if excel sheet has more sheets to process then increase the array and assign it with sheet name
	Dim count As Integer 
	Dim nCounter As Integer
	
	For count = Lbound(sheetName) To Ubound(sheetName)
		Set rs = ado.Execute("select count(*) from [" + sheetName(count) + "]") 
		total = rs.fields(0).value
		Set rs = ado.Execute("select * from [" + sheetName(count) + "]") 
		rowTotal = 1 
		While Not rs.EOF
			flag = True
			Set cur_doc = cur_db.createdocument
			cur_doc.form = "frm1"
			
			
			'to check for the duplicate record
			
			ClaimNum = rs.Fields(4).value   
			VINum = rs.Fields(2).value
			
			formula      =  "Form = " + Chr(34) + "frm1" + Chr(34) + " &  ClaimTX = " +  Chr(34) + ClaimNum  +  Chr(34) +  " & VINumTX = " +  Chr(34) + VINum + Chr(34) + "" 
			
			Set dc = cur_db.Search(formula, Nothing, 0)
			If dc.count > 0 Then
				
				Messagebox "A Record is already Exist.", 0, "Messagebox"
				
			Else
				
				cur_doc.field1 = rs.Fields(0).value
			            cur_doc.field2 = rs.Fields(2).value
				cur_doc.field3 = rs.Fields(4).value
                                                cur_doc.Datefield1 = rs.Fields(8).value  
				cur_doc.Datefield2 = rs.Fields(9).value
			
                                      ' have removed the other fields from here	 to make the code short			
				
                                                cur_doc.FormCreatedByTX = ses.CommonUserName
				cur_doc.CrDT = Now()
				
				
			'Set the User Name in the Names field and Author in the Authors field
				Dim ItemUserName As NotesItem 
				Dim ItemAuthor As NotesItem 
				Dim AuthorName As String 
				
				AuthorName = ses.UserName 
											
				Set ItemUserName  = New NotesItem(cur_doc , "ByNA", AuthorName, NAMES)
				Set ItemAuthor  = New NotesItem(cur_doc , "authorsAN", AuthorName, AUTHORS)
				
				If cur_doc.NumTX(0) = "" Then 
					cur_doc.NumTX =  GenerateSequentialNumber ("frm1" )
				End If	
				
							
				Call cur_doc.save(True,True) 
								
			End If
			rs.MoveNext
			
			
			Print "Processing "+Cstr(rowTotal) +" of "+Cstr(total) + " in Sheet Name " + Cstr(sheetName(count))
			rowTotal = rowTotal + 1
			
		Wend
		
	Next 
	Call ws.ViewRefresh
	ado.Close
	
	Messagebox "Excel File successfully imported." , 0, "Messagebox"
	Print "Done importing"
	Exit Sub
	
errorHandler:
	Messagebox "Error" & Str(Err) & ": " & Error$
	ado.Close
	Exit Sub
	
cancel: 
	
	
End Sub

                                  
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:

Select allOpen in new window

Asked On
2009-10-27 at 08:48:31ID: 24847641
Tags

Lotus Script

,

Importing Excel

,

Lotus Notes database

,

Lotus Notes

Topics

Lotus IBM

,

Microsoft Excel Spreadsheet Software

,

Scripting Languages

Comments
8

This solution is premium contentAccess this technology solution plus 4 million more as a premium member.

Become a Premium Member

  • Solve problems faster with premium solutions and 1-on-1 expert help
  • Advance your technical skills with access to training resources
  • Grow your career by networking with industry thought leaders

Try it Free

30 day free trial. Cancel anytime.

Experts Exchange is trusted by 44 of the top 50 Fortune 500 companies.

How It Works

Put the power of 100,000 technical experts to work for you. Whether you're stuck with a difficult problem, need to expand you skills, or want to network with others in your technical area, our global expert community will help you get your job done and become a better professional.

Michael Victors Independent IT Consultant California, USA

Jon McAdams Network Manager B.C., Canada

Chris Habina System Engineer London, England

Greg Reynolds IT Manager Texas, USA

Hear from Our Community

"Three years back we had a client requirement for developing an asset tracking solution that required data acquisition from different data sources. I was assigned the role of DBA. With no practical experience in database administration, I was clueless on how to proceed. Thanks to the guidance and support of Experts Exchange's vast talent pool, I was able to complete the data integration process in less than a week's time, which saved our company more than $2000 worth of man-hours and got me a promotion!"

Sherkar Bhurshan

RFID Consultant at Aplomb Global IT Consultants

"My Staff and I use EE on a daily bases to solve all kinds of issues. One answer can be worth the cost of a lifetime membership! For example, we ran into a bunch of problems when migrating our Exchange environment from 2003 to 2010. We kept running into issues and repeatedly the best and most detailed info was coming from Experts Exchange. One of the last issues was getting active sync and OWA published correctly and the partners we were using were struggling for hours. Finally I decided to go look for my own answers and was able to find what was looking for on Experts Exchange. It was a life saver!"

Jakub Hanson

Director of Infrastructure at Noel Group LLC

"A few years back, I worked for a British bank in compliance. When the bank sold its trading department to an American company, the compliance systems had to change rapidly to avoid managers being charged with not submitting compliance reports to the government, which would have been a criminal offence. Experts Exchange helped me get the job done on time."

Murray Brown

Programmer at Murbro Ltd

201401-VQP-093

Experts Exchange powers the
growth and success of technology
professionals worldwide.

Try it Free

30 day free trial. Cancel anytime.

Learn More about How It Works

Get Experts Exchange's

Career Builder Guide

Become successful in your tech career.

We will never share this with anyone.

Experts Exchange is the Network for
Technology Professionals

  • An essential resource for producing excellent work

    Experts Exchange is the tech professional’s trusted, on-demand resource for solving difficult problems, making informed decisions, and delivering excellent solutions.

  • A better way to learn technology

    With unparalleled access to technical experts, verified real-world solutions, and diverse educational content, Experts Exchange enables personalized development of technology skills.

  • The network for building your tech career

    Experts Exchange gives you the professional exposure and valued relationships key to building the career you want.

Experts Exchange powers the growth and
success of technology professionals worldwide.

Try it Free

30 day free trial. Cancel anytime.