Hello,
I have a database call GoldMine.nsf with a view call Clients and I have one excel file call ExtraClients.xls. Both file have the following information:
ClientName Email
Carlos R. carlosRJ@hotmail.com
Stephen P. stephenP@aol.com
I need to compare the existing information on the database against the .xls field. and create a new xls field with a new column with YES or NO if it is existing client.
Could you please help me? I have no clue how to do it.
Subject: Compare .xls file against notes database view
Sofia,
What you ask for is very involved and if you don’t have much experience writing agents in LotusScript, you might not be able to easily implement such a thing, but here is how I would approach it, philosophically.
Since you have documents in Notes that contain data that matches with excel, I would create either a user-defined data type or class that contains variables that can store your Client Name and Email data. I would probably create a class for reasons that will become evident. On top of that, you will probably want to create some kind of wrapper class that can hold multiple instances of your “Client” class. You can use this collection class to create the master lists of your “clients” from Notes and excel. In order to be able to use these collections, however, you would have to create GetFirstClient and GetNextClient methods to access the “client” objects. I would also create a method for my “client” class that can compare it to another “client” object that marks an internal (to the “client” class boolean variable for my True (YES) or False (NO) comparisons. You could then use a class property to help set the field in the excel sheet.
As for writing the data to Excel–you should search both this forum and the old 4/5 forum for advice on how to work with Excel via LS.
hth.
brandt
Subject: RE: Compare .xls file against notes database view
Thanks for the tip
Subject: RE: Compare .xls file against notes database view
Brandt, is correct a LotusScript solution will be very time consuming and complicated.
A far easier option would be to export the Notes view to excel, then do the comparison in there. Should be a lot quicker/easier!
In the view, select File | Export, select Lotus 1-2-3 as the file type, but give the file a .XLS extension.
Subject: RE: Compare .xls file against notes database view
No too much. I finish it yesterday. But thank you
Subject: Compare .xls file against notes database view
Sofia. Since your excel spreadsheet has only 2 columns (Client Name & email), make the 3rd column be the ‘existing client’ flag. Read in your excel file (much code available for your use in this discussion board), grab the Client Name from the first data row, go to a view you set up in your notes database who’s first column is also the client name. Do a getdocumentbykey query (again, much code available in this forum), and if there is no return, then go back to the excel spreadsheet and set column 3 of the first data row and set the flag to ‘Yes’. If there is a returned match for the name, then go and set the column 3 flag to ‘No’. Do this for each row of your excel data. As earlier posted, scripting this yourself will be a good learning experience…as it won’t be ‘easy’ for someone at your experience level. But, I will tell you that all the code you need is available in this forum, so you may just need to spend some time looking for it.
HTH
Subject: RE: Compare .xls file against notes database view
Thank you it really help me
Subject: Compare .xls file against notes database view
Assuming you realise that you have to loop through the view and the excel spreadsheet comparing values, then which part of that don’t you understand?
Do you know how to use Lotusscript? If so, some of the basics should be fairly easy, if not then things get a bit trickier of course.
You don’t give much info, and not sure whether you’ll get someone writing your code for you. If you give it a go and post some code with issues you’re likely to get some help though.
Alternatively search this forum, you may find something useful
Dan
Subject: RE: Compare .xls file against notes database view
Assuming you realise that you have to loop through the view and the excel spreadsheet comparing values, then which part of that don’t you understand? — IF I KNOWN THIS DO YOU THINK I WILL POST IT? GENIOUS
Do you know how to use Lotusscript? — OF COURSE NO If so, some of the basics should be fairly easy, if not then things get a bit trickier of course. — GENIOUS GENIOUS
You don’t give much info, and not sure whether you’ll get someone writing your code for you. — EXACTLY GENIOUS. I SAID “I DON’T HAVE A CLUE”.If you give it a go and post some code with issues you’re likely to get some help though. — AGAIN … SO GENIOUS
Alternatively search this forum, you may find something useful
Dan
THANK YOU FOR YOUR HELP
Subject: RE: Compare .xls file against notes database view
Sofia,
Open the file => loop through the rows
Within the loop, do either an FTSearch or a Search on the database to check if any documents have fields which match your values from the CSV. You can put these values into a collection and then display them in an email, folder, another document as you wish
Subject: RE: Compare .xls file against notes database view
Thank you. I did what you said and it solved the problem. You are a really good developer… =)
Subject: RE: Compare .xls file against notes database view
I have LS code where I import an excel sheet, than compare the records in the imported view to the view of another Lotus view (the Directory)- it logs documents in the imported view that aren’t in the directory- but, if you don’t write LS- that isn’t helpful- so, exporting the LS to excel would seem like the easier thing to do.
But the LS code is below - we have been bought by a large company and we are trying to normalize data that has not been kept in any one place (frustrating to me as an employee- like, what is that sales person’s phone #- oh, that is on this piece of paper over here…) now it is all in the Directory. Also, obviously the imported excel spreadsheet has the “correct” values for fields in the NAB- so, I just over write them.
Dim s As New NotesSession
Dim db As NotesDatabase
Dim LookupDB As New NotesDatabase( "Excalibur", "Apps\IT\DRSOutlookImport.nsf" )
Set db = s.CurrentDatabase
Dim SRSStaff As NotesView
Set SRSStaff = db.GetView("SRSUsers")
Dim LookupStaff As NotesView
Set LookupStaff = LookupDB.GetView("ImportView")
Dim Doc As notesdocument
Dim LookupDoc As notesdocument
'Set up items for replacing or updating
Dim JobTitle As NotesItem
Dim Department As NotesItem
Dim Location As NotesItem
Dim OfficeStreetAddress As NotesItem
Dim OfficeCity As NotesItem
Dim OfficeState As NotesItem
Dim OfficeZip As NotesItem
Dim EmployeeID As NotesItem
Dim OfficePhoneNumber As NotesItem
Dim CellPhoneNumber As NotesItem
Dim StreetAddress As NotesItem
Dim Comment As NotesItem
’ Dim City As NotesItem
’ Dim State As NotesItem
’ Dim Zip As NotesItem
Dim Phone As Notesitem
Dim CNName As String
Set LookUpDoc = LookUpStaff.GetFirstDocument
Do Until LookUpDoc Is Nothing
Set Doc = SRSStaff.GetDocumentByKey(LookUpDoc.CommonName(0))
Print "Working on " & lookupDoc.CommonName(0)
If Not Doc Is Nothing Then
' start process of iffing and setting
Call doc.ReplaceItemValue("JobTitle",LookupDoc.JobTitle(0))
Call doc.ReplaceItemValue("Department",LookupDoc.Department(0))
Call Doc.ReplaceItemValue("Manager",LookupDoc.Manager(0))
Call doc.ReplaceItemValue("OfficePhoneNumber",LookupDoc.OfficePhoneNumber(0))
Call doc.ReplaceItemValue("OfficeFaxPhoneNumber",LookupDoc.OfficeFaxPhoneNumber(0))
Call doc.ReplaceItemValue("CellPhoneNumber",lookupDoc.CellPhoneNumber(0))
Call doc.ReplaceItemValue("StreetAddress",LookupDoc.StreetAddress(0))
Call doc.ReplaceItemValue("City",LookupDoc.City(0))
Call doc.ReplaceItemValue("State",LookupDoc.State(0))
Call doc.ReplaceItemValue("Zip",LookupDoc.Zip(0))
Call doc.ReplaceItemValue("PhoneNumber",LookupDoc.PhoneNumber(0))
Call doc.ReplaceItemValue("DRSComEmail",LookupDoc.DRSComEmailAddress(0))
LookupEmployeeNumber = LookupDoc.employeeID(0)
Call Doc.ReplaceItemValue("EmployeeID",LookupEmployeeNumber)
’ Call doc.ReplaceItemValue(“Comment”,LookupDoc.FullLegalName(0))
doc.FullLegalName = LookupDoc.FullLegalName(0)
Call doc.Save(True,True)
Print "Done Working on " & Lookupdoc.CommonName(0)
Goto GetNextDoc
Else
Print "Failed at " & lookupdoc.commonname(0)
Dim logdoc As NotesDocument
Set logdoc = LookupDB.CreateDocument
logdoc.form = "NABImportLog"
'For some reason this is erroring out
LogDoc.Logdate = Now
logdoc.CommonName = LookupDoc.CommonName(0)
LogDoc.FirstName = LookupDoc.LegalFirstName(0)
LogDoc.MiddleInitial = LookupDoc.MI(0)
LogDoc.LastName = LookupDoc.LegalLastName(0)
Print "Logged " & LookupDoc.CommonName(0)
Dim AuthorsItem As New NotesItem(LogDoc,"Author",s.UserName,Authors)
Call Logdoc.Save(True,True)
End If
Goto GetNextDoc
GetNextDoc:
Set LookUpDoc = LookupStaff.GetNextDocument(LookUpDoc)
Loop
Exit Sub
Subject: RE: Compare .xls file against notes database view
Thank you for sending me your code. I just took your idea. =)
Subject: RE: Compare .xls file against notes database view
thank you, my head hurts from all the shouting now:-)