Solved

Seeking Lotus Notes Contacts export to Excel including Categories field

Posted on 2007-03-22
23
2,694 Views
Last Modified: 2013-12-18
Looking for utility to export from Lotus Notes Contacts (Address Book) into Excel - but export fields must contain "Categories". The IBM download pnabexprev.ntf falls short.
Currently running Notes 6.5.4, Win XP Pro
0
Comment
Question by:oldunclemarc
  • 6
  • 5
  • 4
  • +3
23 Comments
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
You can either see if you can create a view with categories, or right a custom export  of the fields  you want.

I'll check some other options.

0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
Do you have access to a design client to write code or change views ?

If yes, you can create your own view and export it via the export function.

0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
Exactly, but you don't need to create your own view, just open the address book to the contacts by category view,

select File>>Export
When the dialog box comes up where to save the file, select: "Lotus 1-2-3" for Save As Type, and name the file.

When the Lotus 1-2-3 Export dialog box appears, select "All Documents"  and check "include View Titles"

It will then save the exact view, including categories to a  filename.wk4  file that you can open in Excel.

If you just want to export the categories, then collapse the view.
If you want to export only some category document, then collapse the view, and expand the categories where you want to include specific documents.   Or select those documents and  use the "selected documents" options.

0
 
LVL 3

Expert Comment

by:rd153
Comment Utility
Create a view with a single column and use a separator "#" between the values, the column formula would look somthing like this:
@Text(yval) +  "#"+  @Text(gval) + "#"+  Region + "#"+ Currency + "#"+ dval + "#" + ReqStatusText + "#"+ Traveller + "#"+ @Text(AccFare)

You then would need to write an agent to extract the information and enter it into an excel spreadsheet:
Something like this would do the trick:
Declarations
Class spreadsheet
      Public data As notesview
      Public titles As String
      Public savepath As String
      
      Function create
            Set data = Me.data
            titles = Me.titles
            
            Dim dc As notesdocumentcollection
            Dim doc As notesdocument
            
            Dim nav As NotesViewNavigator
            Dim entry As NotesViewEntry
            
            savepath = Me.savepath
            Dim col As notesviewcolumn
            Set xl = CreateObject("Excel.Application")
            xl.workbooks.add
            p=2
            Set nav = data.CreateViewNav()
            Set entry = nav.GetFirst
            Do Until entry Is Nothing
            ' There are 5 fields in the file, so we need to search for the commas.
                  Separator="#"      'Inform the search what to look for            
                  NumItems=0
                  
                  EnvString= entry.ColumnValues(0) ' get complete list...
                  If Isarray(EnvString) Then                        
                        For x = 0 To Ubound(EnvString)
                              NumItems=0
                              While Instr(EnvString(x),Separator) >0
                                    NumItems=NumItems+1
                                    Redim Preserve Values(1 To NumItems)
                                    Values(NumItems)=Left(EnvString(x),Instr(EnvString(x), Separator)-1)     ' get each value.
                                    EnvString(x)=Mid(EnvString(x),Instr(EnvString(x),Separator)+1)                ' remove the value and Separator
                              Wend            
                              Redim Preserve Values(1 To NumItems+1)
                              Values(NumItems+1)=EnvString(x)                                                   ' get remaining value
                              z=1
                              
                              Forall i In Values
                                    xl.ActiveWorkbook.ActiveSheet.Cells (p,z).Value=Values(z)
                                    z=z+1      
                              End Forall
                              p=p+1
                        Next
                  Else
                        NumItems=0
                        While Instr(EnvString,Separator) >0
                              NumItems=NumItems+1
                              Redim Preserve Values(1 To NumItems)
                              Values(NumItems)=Left(EnvString,Instr(EnvString, Separator)-1)     ' get each value.
                              EnvString=Mid(EnvString,Instr(EnvString,Separator)+1)                ' remove the value and Separator
                        Wend            
                        Redim Preserve Values(1 To NumItems+1)
                        Values(NumItems+1)=EnvString                                                    ' get remaining value
                        z=1
                        
                        Forall i In Values
                              xl.ActiveWorkbook.ActiveSheet.Cells (p,z).Value=Values(z)
                              z=z+1      
                        End Forall
                        p=p+1      
                  End If
                  Set entry = nav.GetNext(entry)
            Loop
            xl.ActiveWorkbook.ActiveSheet.Cells ((1),1).Value= "Year"
            xl.ActiveWorkbook.ActiveSheet.Cells ((1),2).Value= "Month"
            xl.ActiveWorkbook.ActiveSheet.Cells ((1),3).Value= "Region"
            xl.ActiveWorkbook.ActiveSheet.Cells ((1),4).Value= "Currency"
            xl.ActiveWorkbook.ActiveSheet.Cells ((1),5).Value= "Travel Date"
            xl.ActiveWorkbook.ActiveSheet.Cells ((1),6).Value= "Status"
            xl.ActiveWorkbook.ActiveSheet.Cells ((1),7).Value= "Traveller Name"
            xl.ActiveWorkbook.ActiveSheet.Cells ((1),8).Value= "Fare Cost"
            For a = 0 To z-1      
                  
                  xl.ActiveWorkbook.ActiveSheet.cells(1,1+a).Font.Bold = True
            Next
            
            xl.Columns("A:Z").EntireColumn.AutoFit
            xl.ActiveWorkbook.SaveAs(savepath  )
            xl.quit
      End Function
      
End Class

Initialize:
Sub Initialize
      Dim ws As New notesuiworkspace
      Dim sess As New NotesSession
      Dim db As NotesDatabase
      
      Dim view As notesview
      Set db = sess.CurrentDatabase
      Set view  =  db.GetView("TravelReport")
      Dim ss As New spreadsheet
      Dim k As String
      k = Cstr(Today)      
      
      pathtosaveto = ws.SaveFileDialog( False ,"Export  view to Excel", Curdir,"Travel Report" & k &".xls" ) 'added zero
      If Isempty(pathtosaveto) Then Exit Sub
      
      Set  ss.data = view
      ss.titles = True
      ss.savepath = pathtosaveto(0)  
      Call ss.create
End Sub
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 125 total points
Comment Utility
"Something like this.." LOL

If you're willing to get your hands that dirty with coding, it would be trivial to modify the exporter in pnabexprev.ntf.

If you are using Relase 6 or 7, and you have a view with all the oclumns you like, you don't need an agent, and you don't need to export to 1-2-3.  You can simply choose Edit->Copy As Table, then paste into Excel. Release 7 also has an export to CSV, which is probably cleaner than export to 1-2-3 for your purposes. Not sure if Release 6 has export to CSV.

But for anything in the previous paragraph, you still need the view, which is a Very Big Hassle to do, since you'll have to build all the columns. I'll make a suggestion that will allow you to reap the benefits of pnabexprev.ntf, and do a MINIMAL view to add categories, not a very big hassle.


Next, choose Create -> View, name it "Contact Category Export," and click "Save and Customize"
Click on the heading labelled "PHONES." Press the delete key to remove it.  Same for BUSINESS. Click Choose menu Create -> Append view column
In the lower section of the screen, change "Simple Function" to field, and pick the field Categories
File -> Save, File -> Close

Now you have a view you can use to merge with the export you got out of the NTF file! Open that view, and you shoud see the name, e-mail, and categories listed.

Edit -> Select All, Edit -> Copy as table

Open the Excel file you already created.  Paste it in to the right of the existing columns.  They should match up as-is.  If they don'e, here's how you can get around it...

Create a second tab in the Excel file.  Paste the categories view there instead of right next to the existing exported values.  Select all the pasted cells (minus the header), and create a range named ContactCategory

Go back to the exported data, and add a column with a VLOOKUP formula to bring the categories out of th second tab.  Voila, Excel shoves in the category values.
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
also see

http://searchdomino.techtarget.com/tip/0,289483,sid4_gci1246642,00.html

for a general purpose exporter.


I hope this helps !
0
 
LVL 3

Expert Comment

by:rd153
Comment Utility
If you want a more genric excel reporting agent then this would work with any view without having to do to much extra coding:
Class spreadsheet
      Public data As notesview
      Public titles As String
      Public savepath As String
      
      Function create
            Set data = Me.data
            titles = Me.titles
            
            Dim dc As notesdocumentcollection
            Dim doc As notesdocument
            savepath = Me.savepath
            Dim col As notesviewcolumn
            Set xl = CreateObject("Excel.Application")
            xl.workbooks.add
            p=1
            Set doc = data.getfirstdocument
            If titles = "True" Then      p=2
            Do Until doc Is Nothing
                  For q = 0 To data.columncount-1
                        j = doc.columnvalues(q)
                        xl.ActiveWorkbook.ActiveSheet.Cells (p,q+1).Value=doc.columnvalues(q)
                  Next      
                  p = p +1
                  Set doc =   data.getnextdocument(doc)
            Loop
            If titles = "True" Then
                  p=2
                  For a = 0 To data.columncount-1
                        Set col = data.columns(a)
                        k =   col.title            
                        xl.ActiveWorkbook.ActiveSheet.Cells (1,1+a).Value=col.title      
                        xl.ActiveWorkbook.ActiveSheet.cells(1,1+a).Font.Bold = True
                  Next
            End If
            xl.Columns("A:Z").EntireColumn.AutoFit
            xl.ActiveWorkbook.SaveAs(savepath  )
            xl.quit
      End Function
      
End Class

Sub Initialize
      Dim ws As New notesuiworkspace
      Dim sess As New NotesSession
      Dim db As NotesDatabase
      
      Dim view As notesview
      Set db = sess.CurrentDatabase
      Set view  =  db.GetView("GrantByStatusExcel")
      Dim ss As New spreadsheet
      
      pathtosaveto = ws.SaveFileDialog( False ,"Export  view to Excel", "Microsoft Excel WorkBook | *.xls", Curdir,view.name & ".xls" ) 'added zero
      If Isempty(pathtosaveto) Then Exit Sub
      
      Set  ss.data = view
      ss.titles = True
      ss.savepath = pathtosaveto(0)  
      Call ss.create
End Sub
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
qwaltee .. quite right and I agree with your post.    All export to .csv but I think my  original post to wk4 was because of previous versions of excel.  It all depends on what you want to see when you open the file.

Copy Selected as Table (R6) doesn't do the job right, since it confuses the columns.  So, you lose the categories, and the stepped columns end up appearing in the first excel column.

Export to CSV works fine, in R6 and Office XP.

0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
Copy as table is really no worse than exports.  You don't want to export categories, and you don't want to copy-as-table on categories.

SysExpert, that code looks very familiar. But it still requires a view to have all the columns needed, which is the same Very Big Hassle I wouldn't want the user to have.
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
You don't want to export categories<< but that's specifically the asker's question. :)
0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
OK, let me clear up t hat confusion.  You don't want to export "sort-categorized" columns, the data of which can be called "categories."  You DO want to export the value of the field named "Categories."

Your comment about how copy-as-table messes up the columns and you "lose the categories" is what I was responding to.  That only applies if you are NOT using a flat view, i.e., using a sort-categorized view will lead to the problem you describe.  None of the existing views contains the field Categories EXCEPT the "Contacts By Category" view, which uses the sort-categorized method, which will be usless for copy-as-table AND useless for exports for this purpose of this question.

That's why I suggested creating a very simple view with just three key columns -- name, e-Mail, and categories -- which can then be used by Excel for lookup.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 7

Expert Comment

by:hladamjr
Comment Utility
All you need to do is:
1) Open your contacts in Notes
2) Then  on te menu bar select File then Export
3) It opens a window in the Svae as File tpe Field select Comma Seperated File
3 Name it wahat ever you want  *.CSV and browse to where you want to save it and select Export
4) Open Excel and browse to the file ( changing to File type CSV or all file types so you can see it.
5) Select it and open it.
It will open and Row A  will be all your categories with your contact info in the respective Colums
0
 
LVL 7

Expert Comment

by:hladamjr
Comment Utility
Forgot to mention that you need to set your View to By Category and then expand each Category and Select All  Documents when prompted what to Export.
0
 

Author Comment

by:oldunclemarc
Comment Utility
I want to thank all who've taken the time to respond.
Marilyng: Your suggestion came close, but the resulting file lacked defined fields which I'd need for this project (Firstname, Lastname, Title, Business, Address1,Address2, City, State, Zip, Phone, Email, Category)
Sysexpert: The export utility on searchdomino looked promising, but there are no instructions accompanying the download. The dowload went fine, but what to do after that is anyone's guess.
Qwaletee: You seem to be on the right track as well, except that I'm running running Notes in a corporate environment and don't seem to have appropriate rights to be able to make the changes you recommend.
Hladamjr: Your solution worked nicely, but resulted in a very simplistic spreadsheet, with only the columns that I can view in my contacts (and...as above...my ability to customize the view isn't there).
My hopes are starting to fade on this one, and it is bumming me out since I know Notes can do this. Outlook can, and Notes is fundamentally a database...right? It should be able to do this no problem.
Well....keep em coming. I'll try anythng (except the code writing stuff). Aaaaak!
Oldunclemarc
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
Very simple.
Have an administrator / designer create the views for you, and then you can export them.

0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
Even in a locked down corporate environment, you should be able to do as described.  You don't need the designer client.  Just open your contacts, choose CREATE -> VIEW form the menu, and continue as I described.
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
Question, is this the CORPORATE address book, or your personal address book?  The corporate one will probably not allow you to create a view, but your personal one will, as qwaletee describes.
0
 

Author Comment

by:oldunclemarc
Comment Utility
Qwaletee: Thx for replying. Conceptually, I get what you're telling me. I've even got the "Contact Category Export" view created. It is the customization of that view that is now giving me trouble. You write:
"Next, choose Create -> View, name it "Contact Category Export," and click "Save and Customize"
Click on the heading labelled "PHONES." Press the delete key to remove it.  Same for BUSINESS. Click Choose menu Create -> Append view column
In the lower section of the screen, change "Simple Function" to field, and pick the field Categories
File -> Save, File -> Close"
I can find "Click Choose menu Create > Append view column." So My Contact Category Export view is just a copy of my normal contacts view.
OUM
0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
That option (append column) is only present when you are editing the design of the view.  You can't do it while just looking at the view. In some versions of Notes, the APPEND COLUMN choice may be named differently.  You can always do the equivalent -- while editing the view design, locate the last column header visually, look for the empty space in the column header to the right of it, and double-click the empty space.  That will also create a new column.

In current versions of Notes, you can switch to editing the view design by:

Opening the view
Choosing menu ACTIONS -> VIEW OPTIONS -> DESIGN
...if you do not have that choice, just run through the view creation steps again, using a different name for the new view, and click SAVE AND CUSTOMIZE. (The customzie option takes you immediately into an edit of the view design.) If you don't have SAVE AND CUSTOMIZE, and you aslo don't have VIEW OPTIONS->DESIGN, then you may have a very rare setup for Notes that is missing some fo the menus.

MarilynG: There is no such thing as an inability to crate a view.  The ACL can lock you out from creating a personal view that lives on server, but the worst that will happen is that the view is created in your Desktop file instead.  The ACL never locks you out of that. For example, in my current corporate directory, I have only Author rights with no priate views, yet I just went in to start creating a view there.
0
 

Author Comment

by:oldunclemarc
Comment Utility
Hot Dog !! Things are starting to click.
I've got a view called Contact Category Export, and I created additional columns by:
1. Double clicking on the space
2. Naming the new column
3. Choosing "fields" and selecting one (then saving)......Worked fine!
I did an export to CSV and it worked fine.
Last question....I've hidden the original "Name" column and have created a "Firstname" and a "Lastname" column. Those two new columns are at far right of my view. How do I "move" them so they are permanently at the far left or begining of my view?
OUM
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
Just drag them over.


0
 

Author Comment

by:oldunclemarc
Comment Utility
I tried that the first time and it didn't work (hence the posting of the question)
Now it works!! Thx Sysexpert
Well....I think that's a wrap
Again...many thanks to all who helped on this one. Qwaletee....you rock!
OUM
0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
Rocks in my head is more like it ;)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now