masmatc
asked on
Importing Excel file into Lotus Notes...how to import the Date values correctly?
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
You can use like:
cur_doc.Datefield1 = Format(cdat(rs.Fields(8).v alue), "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.
cur_doc.Datefield1 = Format(cdat(rs.Fields(8).v
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.
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).v alue), "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.
You need to do below:
Dim tmpdate as Notesdatetime
'cur_doc.Datefield1 = Format(cdat(rs.Fields(8).v
tmpdate= New Notesdatetime(Format(cdat(
Dim tmpmnth as Number
dim tmpdt as number
dim tmpyr as number
tmpmnth=Mnth(tmpdate)
tmpmnthstr = Select(tmpmnth,"Jan","Feb"
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.
ASKER
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).
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).
So, this is more a question on Excel than Notes.
ASKER
Thank You!
ASKER
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?