Seeking Lotus Notes Contacts export to Excel including Categories field

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.

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.

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.

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

also see

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

for a general purpose exporter.


I hope this helps !

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

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.

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.

You don't want to export categories<< but that's specifically the asker's question. :)

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.

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

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.

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

Very simple.
Have an administrator / designer create the views for you, and then you can export them.

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.

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.

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

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.

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

Just drag them over.

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

Rocks in my head is more like it ;)