?
Solved

Seeking Lotus Notes Contacts export to Excel including Categories field

Posted on 2007-03-22
23
Medium Priority
?
2,711 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +3
23 Comments
 
LVL 63

Expert Comment

by:SysExpert
ID: 18776025
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
ID: 18776068
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
ID: 18777180
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Expert Comment

by:rd153
ID: 18777796
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 500 total points
ID: 18779621
"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
ID: 18779824
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
ID: 18780519
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
ID: 18781926
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
ID: 18781984
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
ID: 18782169
You don't want to export categories<< but that's specifically the asker's question. :)
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 18783016
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
 
LVL 7

Expert Comment

by:hladamjr
ID: 18811415
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
ID: 18811485
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
ID: 18811948
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
ID: 18812128
Very simple.
Have an administrator / designer create the views for you, and then you can export them.

0
 
LVL 31

Expert Comment

by:qwaletee
ID: 18813030
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
ID: 18813078
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
ID: 18815371
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
ID: 18815655
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
ID: 18816295
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
ID: 18816507
Just drag them over.


0
 

Author Comment

by:oldunclemarc
ID: 18817029
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
ID: 18819515
Rocks in my head is more like it ;)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

764 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