We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How to export the user lists of mail database to excel format?

jonathanwong
jonathanwong asked
on
Medium Priority
290 Views
Last Modified: 2013-12-18
There a about 600 mail user databases in the mail\ director of domino v5.0.13, how can i export the information (title) of the database to excel format file?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Of hand i'm not sure this is possible.

If you have some designing skill you would be able to import it into a db view and then export to excel or print.

otherwise if you just need the filenames and not the title you can add a column to a new view with the mailfile field specified, and this will then be exportable.

Hope this helps
there should be catalogue.ntf

Please check that

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2007

Commented:
As mentioed, I would simply add a private view in the Domino directory ( addresss book ), and use the export to Lotus 123. This can be imported into Excel.

I hope this helps !

Author

Commented:
Hi, rrabie

How can do this? Could you please explain furter. Thanks!
CERTIFIED EXPERT
Top Expert 2007

Commented:
Did you try to acsess the database called "catalog" on any Domino Server ?

It has lots of views and info on all databases that can easily be exported.

I hope this helps !

Commented:
The catalog.nsf does contain a list of databases, but traditionally, mail databases are not listed in the catalog.  The mail template defaults to "do not list".  You can check the box on the template and the server will update the mail designs to be listed, and then update the catalog so you can export the view to excel.

Or you can create an agent on a server database that does the collection and mails you the results:)

I have a test database where I ran this scheduled, with full administration permissions, on the server.  It sends you a "comma delimited file" that will open in Excel.   Be sure to add your email address, server name, and the correct path where indicated.  This will collect *.nsf files.


Sub Initialize
      Dim session As New NotesSession
      Dim db As NotesDatabase
      
      Set db = session.CurrentDatabase
   'ENTER YOUR SERVER NAME HERE-------------------------------
      Const str_SERVER = "YOUR SERVER NAME"
  'ENTER THE FULL PATH OF THE MAIL DIRECTORY HERE ---------
      Const str_STARTDIR="E:\Domino\Data\mail\"
  'THIS IS THE FILE THAT WILL BE MAILED TO YOU ---------------
      Const str_FILENAME="databasedir.csv"
      
    'YOUR EMAIL ADDRESS HERE--------------------------------------
      Const str_SENDTO ="YOUR SEND TO ADDRESS"
      
      If db.Server = "" Then
            Print "Sorry, this agent needs to run on a server"
      End If
      
      Dim chkdb As NotesDatabase
      Dim processed As Long, failed As Long
      Dim sendLog As Boolean
      sendLog = False
      Dim msg As String
      Dim tb As String
      tb = Chr(9)
      
      If str_SENDTO <>"" Then
            sendLog = True
      End If
      
      msg= "Starting the Db Collection agent on " + str_STARTDIR
      Print msg
      
      If sendLog Then
            Dim maildoc As New NotesDocument(db)
            With maildoc
                  .SendTo = str_SENDTO
                  .From = session.UserName
                  .Principal = session.UserName
                  .subject = "Database Directory File:  " +  str_FILENAME
            End With
            Dim rtItem As New NotesRichTextItem(maildoc, "Body")
            
            With rtItem
                  .AddNewline 1
                  .appendText  msg
            End With
      End If
      
      Dim fileNum As Integer
      Dim cdr As String
      cdr = Curdir() +  "\" + str_FILENAME      
      fileNum = Freefile
      Open cdr  For Output As  fileNum      
      
      Write #filenum, "Title","Filename","Filepath", "ReplicaID","Server", "TemplateName" ,"FileFormat", "DesignTemplateName"
      Dim tmp As String
      tmp = Curdir()
      On Error Goto DBDirectoryError
            
      Dim fileName As String
      fileName = Dir$(str_STARTDIR + "*.nsf", 0)
      
      Do While fileName <> ""      
            Set chkdb = New NotesDatabase(str_SERVER,str_STARTDIR + fileName)            
            If Not (chkdb Is Nothing) Then                  
                  If Not chkdb.IsOpen Then Call chkdb.Open("","")
                  If Not chkdb.IsOpen Then
                        msg= "Unable to open " + chkdb.Title
                        Print msg
                        If sendLog Then
                              rtitem.addnewLine 1
                              rtitem.appendText  tb + msg
                        End If
                        failed = failed +1
                        Goto Next_Database
                  End If
                  
 'Collect your information here.............
                  With chkdb                        
                        Write #filenum, .title , .filename , .Filepath, .ReplicaID , .Server ,.TemplateName , Cstr(.FileFormat ), .DesignTemplateName
                        processed =       processed +1
                  End With
                  If sendLog Then                        
                        rtItem.addnewLine 1
                        rtItem.appendText  tb + "Collected: " + chkdb.title                        
                  End If
                  Set chkdb = Nothing
            End If
            
Next_Database:
            fileName = Dir$()
            
      Loop
      
      Close #filenum
      msg = "Finished processing " + Cstr(processed) + " databases.  Failed to process: " + Cstr(failed)
      Print msg
      
ExitHere:      
      If sendLog Then
            Dim object As NotesEmbeddedObject
            With rtItem
                  .addnewline 2
                  .appendText msg
                  .addnewLine 2
                  .appendText "Below find CSV tab-delimited Excel File: "  + cdr
                  Set object = rtItem.embedObject( EMBED_ATTACHMENT, "",cdr)
            End With
            maildoc.send False            
      End If
      
      If Not chkdb Is Nothing Then Set chkdb = Nothing
      
      Exit Sub
      
DBDirectoryError:
      On Error Goto 0
      msg = "Error processing databases: " + Error + "-" + Str(Err) + "  Line: " + Str(Erl)
      Print msg
      If sendLog Then
            rtItem.addnewline 2
            rtItem.appendText msg
      End If
      Err=0      
      Resume Next
End Sub
Commented:
Another way that I have used is to install a printer in windows as  a generic text printer on FILE:

Then drag the coloum across so that you can see all the TITLE and print to the text printer.  You can then open this in excel delimiting bu a fixed width

Simple but it works
Commented:
sg405222 - nice workaround..!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.