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

    Question by:
    On

    Topics:

    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
    

    Open in new window

    Good Question?
    0
     

    ?

    The member who asked this question verified this comment provided the solution that solved their problem.

    Accepted Solution on 2009-10-27 at 09:02:14ID: 25673999

    The short answer is "You don't need to worry about the date format being used".

    When you enter a date in an Excel spreadsheet, Excel uses your current date settings from the operating system to store the date.  The date is always stored in a date format.  When you format a cell, you are telling Excel to format the stored date in a particular way.  The data does not change, just the display of the data.  That is why you always get the correct date regardless of the format used to display it.

    verified

    answer

    Enjoy your free answer!

    Join now to get more tech resources.

    Become a member

    Comments

      

    Accepted Solution

    2009-10-27 at 09:02:14ID: 25673999Best
    Bill-Hanson earned 500 total points
    The short answer is "You don't need to worry about the date format being used".

    When you enter a date in an Excel spreadsheet, Excel uses your current date settings from the operating system to store the date.  The date is always stored in a date format.  When you format a cell, you are telling Excel to format the stored date in a particular way.  The data does not change, just the display of the data.  That is why you always get the correct date regardless of the format used to display it.
    Good Solution?
    0
     
    masmatc

    Author Comment

    2009-10-27 at 10:05:38ID: 25674827
    Thanks for your reply Bill.

    And i like your short answer.

    One thing sure though, user need to make sure that the Date column in the imported Excel file is set to Date regardless of the format as you have confirmed. Because if it is set to text (and evne if user storing the date values in it) then i tested it that in the Lotus Notes even if i am storing the values in the Date type field in the property window field tab it shows data type of text.

    So please verify that as long as the Date column in the Excel file is set to Date, the imported dates values should be correct. Regardless of how i am displaying the date values in the Notes view as well.

    I think i am struggling to understand this, that lets say in the Excel file i enter date as mm/dd/yyyy and
    my current date settings from the operating system is also mm/dd/yyyy. so as you have already mentioned that that's how it will get stored...right. Now when using the lotus script when i import this value into Notes Db...how it get stored in the Notes Db, who tell that Date field on the form that what is the month, day and year is?
    Good Comment?
    0
     
    masmatc

    Author Comment

    2009-10-27 at 12:52:50ID: 25676655
    Let say i have two different users with different Date settings from their operating system (Control Panel -> Regional and Language Options). one is United States(MM/DD/YYYY) and the other is Canada(DD/MM/YYYY).  

    One of the views in the system based on Date value will display the results for users differently.

    The requirement is to display the results in the Lotus Notes database based on a custom format (Oct 27, 2009) of the Date. so regardless of the user Date setting...All the users will view the results in the same format. I think this can be done by just changing the display properties.

    The other thing i need your help with is this...when i am reading the record set from the Excel file in the above code i like to format the Date value that i am getting from the Excel and then pass it to Lotus Notes database and the format that i like to have is United States(MM/DD/YYYY). So first get the Date value from the Excel file -> convert its format to (MM/DD/YYYY) then assign this value to one of the
    field on the Lotus Notes form and then reformat it to a custom format (Oct 27, 2009). Does any one now how?
    Good Comment?
    0
     
    madheeswar

    Expert Comment

    2009-10-27 at 18:22:06ID: 25679245
    You can use like:
    cur_doc.Datefield1 = Format(cdat(rs.Fields(8).value), "mm/dd/yyyy")

    Pls check the Format function in Designer help for exact code. But you need to follow above way to get the exact formats.

    Hope it helps.
    Good Comment?
    0
     
    madheeswar

    Expert Comment

    2009-10-27 at 18:29:18ID: 25679279
    For converting into this format Oct 27, 2009
    You need to do below:
    Dim tmpdate as Notesdatetime
    'cur_doc.Datefield1 = Format(cdat(rs.Fields(8).value), "mm/dd/yyyy")
    tmpdate= New Notesdatetime(Format(cdat(rs.Fields(8).value), "mm/dd/yyyy"))
    Dim tmpmnth as Number
    dim tmpdt as number
    dim tmpyr as number
    tmpmnth=Mnth(tmpdate)
    tmpmnthstr = Select(tmpmnth,"Jan","Feb",....,"Dec") ' Pls check the exact format in help.
    tmpyr=Yr(tmpdate)
    tmpdt = Date(tmpdate)
    dim finalfr as string
    finalfr = tmpmnthstr & " " & cstr(tmpdt) &", " & cstr(tmpyr)
    cur_doc.Datefield1 = finalfr 'I assume DateField1 will be Text field, else it won't take the assigned format.

    Apart from the above solution, there is one property you can use from notesdatetime class to convery to local regional settings.
    I hope it helps.

    Good Comment?
    0
     
    masmatc

    Author Comment

    2009-10-28 at 08:14:04ID: 25684146
    Thanks for your reply madheeswar.

    Requirement is...in the above Lotus Script code, change the format of the Date value (cell) in the Excel (so to know exactly what we are getting from Excel - Change the format to (mm/dd/yyyy), its like before getting the value from the Excel, format the Date value in the Excel and then get and assign the value to Notes database.

    The above formatting is just after we are getting the value from the Excel file. (But in order to make sure what we are getting from Excel, need to format the value in the Excel  before getting it).
    Good Comment?
    0
     
    madheeswar

    Expert Comment

    2009-10-28 at 18:04:53ID: 25689795
    So, this is more a question on Excel than Notes.
    Good Comment?
    0
     
    masmatc

    Author Closing Comment

    2009-11-02 at 13:30:51ID: 31646516
    Thank You!
    Good Comment?
    0

    Featured Post

    Course: Foundations of Front-End Development

    Jump-start a lucrative career in front-end web development, with zero previous coding experience required. This course covers the basic programming concepts and languages required for creating engaging websites from scratch.

    Ask Your Tech Question. Get Expert Solutions.We will email you as soon as we have your answer.

    We will never share this with anyone.

    Select topics

    You may select up to five topics.

    Top Expert Contributor

    Essential articles and videos from the Experts

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    More valuable questions with Expert answers

    Question: Hi guys, I need to write a simple script that will do the following: perform tar with the save permissions flag (p) on /path/to/folder1 and then tar another folder with the same flag on /path/to/folder2 That's the easy part. Then I need...

    Answer: For creating folders and removing older ones: (CODE) You can test it first commenting the line with "rm" command

    Question: I had this question after viewing Data-Updating-VBA-V5 (https://www.experts-exchange.com/questions/28955341/Data-Updating-VBA-V5.html). Like Above Question Code use sheet LumSum Calculation & Dash Board LumSum ...like same need to do it for Sheet...

    Answer: V7 with code for Dashboard SIP. Formula adjusted in calculation sheet. Forgot to mention that this line in FindNAV returns an error when NAV is 0. y(j, 4) = Range("SipAmt") / x(i, 3) 'Units I have changed to ignore (skip) by If x(i,...

    Question: So Excel 2010 is crashing when saving, but not always. First it only does it on certain files. Nothing special about the files. They are pretty basic. If I save them as .xls they always work, but crash when saved as .xlsx or .xlsb Also if I...

    Answer: i w ould start by uninstalling / reinstalling Excel, or Office if that does not help, run a diagnostic on your disk; i use the UBCD for this: Hardware diagnostic CD UBCD --------------------------------------------------- go to the...

    Question: Hello everyone. I've found a link that shows a user-defined function that allows me to perform nslookup on a domain and recieve the IP Address of said Domain (Namely, the A record). I need however a way to modify this UDF or create one from...

    Answer: Look at sample. Function: (CODE) return first result from nslookup (you can test it with boeing.com, where all MX records have same priority).

    201607-LO-Qu-086

    Extend your technology team with the Experts Exchange community.

    — trusted by —

    Who answers my questions?Our community has technology experts around the world.

    Rob Hutchinson

    11

    Articles

    623

    Solutions

    Expert in:

    • Components
    • Hardware-Other
    • Laptops/Notebooks
    • Networking-Other
    • Outlook

    amilie wilson

    5

    Articles

    26

    Solutions

    Amaira Sahni

    5

    Articles

    jvutechnc

    1

    Articles

    1

    Solutions

    Sudhakar Kumar

    1

    Articles

    209

    Solutions

    Expert in:

    • BlackBerry Prog

    MouseCaptain

    1

    Articles

    6

    Solutions

    Emmanuel Gleizer

    1

    Articles

    43

    Solutions

    RELATED TOPICS view all topics

    1. Exchange
      (194,638)
    2. Email Clients
      (18,363)
    3. Outlook
      (93,748)
    4. MS Excel
      (127,071)
    5. Email Servers
      (23,370)
    6. BlackBerry
      (7,366)
    7. Email Protocols
      (13,753)
    8. MS Access
      (214,536)
    9. Visual Basic Classic
      (163,312)
    10. Databases-Other
      (53,214)
    Receive Monthly Emails of Tech News and Trends from Experts Exchange