Solved

Lotus Script to Export a view and attachments to Excel

Posted on 2013-05-17
18
4,510 Views
Last Modified: 2013-12-18
I am the owner of around 80 databases.  These database where very important to the company to communicate with vendor companies.  My company like many has changed our email platform to something else.  My company also has a data retention policy that has to be observed.  All my database is no longer active and I need to archive them, so they can take down the servers.  

All my databases include a "all documents" view.  I would like to write a lotus script to parse through all the documents including the attachments and export it to a Excel Spread sheet.

Here is a simple one I used sometime ago.

Sub Click(Source As Button)
	
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim WS As New Notesuiworkspace
	Dim v As NotesView
	Dim UiView As notesuiview
	Dim ViewString As String
	Dim xl As Variant
	Dim xlSheet As Variant
	
	Const View$="User Info\By Name"
	Const ExportItem$="Export"
	Const xlsSave$="False"
	Const xlsFileName$="c:\temp\temp Export.xls"
	
	Set db=session.CurrentDatabase
	Set v=db.GetView(View$)
	Set xl=CreateObject("Excel.Application")
	xl.Workbooks.Add
	Set xlSheet = xl.Workbooks(1).Worksheets(1)
	
	col!=1
	Forall vColumn In v.Columns
		xlSheet.Cells(1, col!)=vColumn.Title
		col!=col!+1
	End Forall
	
	Dim docX As NotesDocument
	Set docX=v.GetFirstDocument
	row!=2
	While Not docX Is Nothing
		col!=1
		Set item=docX.GetFirstItem(ExportItem$)
		Process=True
		'If item Is Nothing Then
		'	Process=True
		'Elseif item.Text="" Then
		'	Process=True
		'End If
		If Process=True Then
			Forall cValue In docX.ColumnValues
				xlSheet.Cells(row!, col!)=cValue
				col!=col!+1
			End Forall
			row!=row!+1
		End If
		Call docX.ReplaceItemValue(ExportItem$, Now)
		Call docX.Save (True, True)
		Set docX=v.GetNextDocument(docX)
	Wend
	
	If xlsSave$="True" Then
		Call xl.SaveAs(xlsFileName$)
		Call xl.Close
	Else
		xl.Visible=True
	End If
	
End Sub   

Open in new window


The above code does to grab attachments.  
Also instead of using a click, I want to run it from the Actions Menu.

Platform = Lotus Notes 7.0.1
Designer = 7.0.1

Export View to Excel along with Attachments

Thanks For Your Help
0
Comment
Question by:jkteater
  • 9
  • 7
  • 2
18 Comments
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39177672
it depends what your databases do but is the data fully contained in the fields shown in the views, what about these attachments, are they in rich text fields, they could contain embedded documents, attachments, tables, images etc. etc.

such things we tend to handle in different ways for different db's, sometimes combination of excel or text exports of view levels, one file per document, rich text exports of document body, attachments exported alomgside etc.

So unless these are all very similar design with very structured data with few important fields the export can be difficult.

i have a few sample excel exports done from the excel side to pull the data from Notes in my web page http://www.dragon-it.co.uk ..

on the phone at the mo, give us some more specifics and might
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39177676
it depends what your databases do but is the data fully contained in the fields shown in the views, what about these attachments, are they in rich text fields, they could contain embedded documents, attachments, tables, images etc. etc.

such things we tend to handle in different ways for different db's, sometimes combination of excel or text exports of view levels, one file per document, rich text exports of document body, attachments exported alomgside etc.

So unless these are all very similar design with very structured data with few important fields the export can be difficult.

i have a few sample excel exports done from the excel side to pull the data from Notes in my web page http://www.dragon-it.co.uk ..

on the phone at the mo, give us some more specifics and might
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39177678
sorry that obviously decided to submit twice... you get the idea.
0
 

Author Comment

by:jkteater
ID: 39181283
First thank for the response.  I appreciate your time.
Now let me try and answer your questions.

A Simple Sample of a Document Form :

Name      [     ]
Number  [     ]

Issue       [     ]
Fix           [     ]

Date        [     ]

Attachments  [       ]  Rich Text Field

Sample View

Number    Name     Issue      Fix      Date

This is a very simple example data, there are more data fields.  But the setup is about the same.

I am just wanting to export each document to a row in a Excel Spreadsheet and hopefully embed the attachments of the document in a cell at the end of the row.

Not sure this is possible, that is what I am needing.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39181317
I think unless the attachments are very specific in size and quantity you would be much better off extracting them to files to store alongside the Excel doc.
0
 

Author Comment

by:jkteater
ID: 39181340
Can you help me with the above.  Once a I have something I can show the users.  I will be able to see if not embedded the attachment is a show stopper
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39181678
I think you need to consider the data better than this, and we still have no idea of what might be in this attachments fields, i.e. only attachments or what.  Exporting into Excel is not going to happen realistically.... how big are your databases, the excel file would be the 100's of Mb or Gb that the db's are?!

There are plenty of ways of getting to the contents of rich text fields but it does depend what the attachments/embedded objects/images etc. are in there. e.g.

http://searchdomino.techtarget.com/tip/Extracting-attachments-from-a-Lotus-Notes-rich-text-field

You could use code like that as you get a handle onto each document and then export the attachments or other contents into, e.g. a file or directory names based on the document't reference number if it has one, in a directory based on a field, e.g. a company name, or using the document unique id.

I think you probably need someone to work with you on this one having been able to look at the database contents rather than a script thrown together from guess work on here.

Steve
0
 

Author Comment

by:jkteater
ID: 39182132
I now understand what you mean about needing to know more about the databases and attachment sizes and number of.

Looking at some of the documents in one database, there can be multiple attachments in 1 rich text field.  It would be a nightmare to try and use the same agent for all 55 databases.

In your opinion, what do you think would be the best way to back up all these databases?

Soon all Lotus Notes Servers are leaving the business.  But the data in the databases have a retention life span that has to be observed.  So they are wanting to backup the databases and store the data as a Excel, Word Doc, PDF in a data management application.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39182458
Why don't you move all databases to one single server and shut the others down? It could even be a VM, and strictly speaking you don't even have to upgrade. Most databases can be made accessible using a browser. It'll buy you the time to think about a better solution, and it'll cost you almost nothing.

PS I regret to say that what you describe is just another poorly planned migration, most likely for the wrong reasons... Do they really know what they threw out?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 43

Expert Comment

by:Steve Knight
ID: 39182570
Exactly.... also depending upon the apps you could also just keep a Notes client and the databases on there for your data retention and/or see if they are web accessible etc.

People think "oh notes, hate it that old email thing.  get rid of it and put exchange in."  They put exchange in, then they realise people use document libraries and put in sharepoint, sql, and another couple of Microsoft servers, then they realise it does various workflows and approval cycles and get someone in to setup sharepoint to do things like that.  Then they get to the customised applications which run their business, probably looking old because they have run for the last 15 years without changing while the users changed from their Windows 3.1 through to Windows 8...

Anyway, rant over (nearly)...

Perhaps I could put it another way.... I've got a couple of hundred tables of data in a SQL server, linked with Sharepoint and Exchange, I'd like to export it all into Excel please.   Just because it is Notes it is no harder or easier, it depends what data you want and how you want it.

If this was me doing it, and I have done many such projects both into and out of Notes you have to build generic exports then tailor them for each category of database having first analysed what is in there, i.e if you have user accessible attachments fields they WILL have text, attachments, images, embedded documents etc. in.

Click on dragon-it link for contact details if needed.

Steve
0
 

Author Comment

by:jkteater
ID: 39184442
All the databases are built off the same form template.  The only thing I am not sure of, is what the users put in the Rich Text Fields.  

sjef_bosman:  What would have to be on the VM?  Would just a web server be able to serve up the database in a browser using the nsf file?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39184507
To answer your question aimed at Sjef. No you can't, sorry - well you can in the same way that you could put MDB Access files on a web server.  When accessed from a web browser they would then be downloaded to a client PC and opened in Access...

Domino IS a web server too, you would need Domino on there then it could be accessed by a Notes client or web browser.  You could also just keep all the NSF files and open them directly with a Notes client, but things to watch there are:

1. The ID file you keep to access with might expire before you come to need it.
2. The databases might need to be on a server, or refer to certain server names etc.
3. The database NSF files could be encrypted on the server so would need to be created as replicas on the client
4. There may be reader names fields controlling access to particular documents which means you might not even be able to see them as a particular user.

Good luck!
0
 

Author Comment

by:jkteater
ID: 39184669
Sorry about the going back and worth.  Just got out of a team meeting and this is what is requested.  

They want to export the database to Excel - In Excel they want to have a column for attachment's, etc... names.  Just the name of file or what ever that was placed in the rich text field.  Then copy all the attachments to a folder on the c:\.  That way they can see what attachment name they need to find in the bucket of files.

Is this possible?  All the databases are created from the same template, so field names will be the same.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39184759
They want this, they want that, they don't want Notes, they want Outlook, and you're at the receiving end (of the blows).

What are you supposed to do when there are multiple different files with the same name? What to do with access rights currently imposed on the databases and documents? Will the Yearly Turnover document be available to all?? What if there are multiple documents with the same name, in different databases?

Excel... really...

As Steve correctly explained: create a VM, Windows or Linux, install Domino, add all databases, add a web interface to the databases so users can open the databases more or less the way they were used to in order to download any document.
0
 

Author Comment

by:jkteater
ID: 39185159
We went to a Google shop.  I explained the VM, Domino idea.  They do not want to have to maintain anything like that.  All they are wanting is a file, either a PDF or Excel file that they can store in a Data Management Application.  They do not believe there will be much need or any activity with the stored databases files.  I tried to explain the integrity of the data will be affected, if we don't leave the database intact.  

So I am looking to take a small database and export it, as explained above.  That will show them what the issues may be using this method.

I just need to get to the point of being able to export the database with copying the attachments to a folder.
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 400 total points
ID: 39185933
OK, as it stands your code is using integers (row!) which is limited to 32,767 lines, though standard Excel XLS will be limited to 64k lines anyway.

Your code is also "touching" each document as it is adding a field "Export" with the current time & date and saving the document so your databases will all have documents edited "today".  This may mean any dates in there for "last modified" or the like are therefore meaningless.

This is an example of a way of extracting all different attachment types etc:
http://www-10.lotus.com/ldd/nd85forum.nsf/0/1910c62e9f340824852577cb0056c882?OpenDocument

You need to add your code in where shown, and I would consider whether you want the bold bits.. I have added a few lines which might do what you want -- extract based on the filename and add the filename to each next cell in the sheet.

You will need ways of dealing with duplicate names (in different documents or the same document) or other types of rich text data etc.

row!=2
      While Not docX Is Nothing
            col!=1
            Set item=docX.GetFirstItem(ExportItem$)
            Process=True
            If Process=True Then
                  Forall cValue In docX.ColumnValues
                        xlSheet.Cells(row!, col!)=cValue
                        col!=col!+1
                  End Forall

REM Add code in here to work on docX
Set rtitem = doc.GetFirstItem("Body")
Forall o In rtitem.EmbeddedObjects
   xlSheet.Cells(row!, col!)=o.name
   Call o.ExtractFile( o.name )
   col!=col!+1
End Forall
                  row!=row!+1
            End If
           Call docX.ReplaceItemValue(ExportItem$, Now)
            Call docX.Save (True, True)

            Set docX=v.GetNextDocument(docX)
      Wend
0
 

Author Comment

by:jkteater
ID: 39194822
I am going to close this question.  I do believe you guys did your best to answer a not very well asked question.  I have at least 5 to 7 agents all doing different things.  All a little confusing to be honest.  I am going to try and ask a new question with much better detail.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39194834
Ok, thanks.  I think breaking it into different parts might be a good idea as there are a lot of "it depends" and guesswork in what we have done here.

Hopefully you have a few pointers to start with and good luck with it.

Steve
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

18 Experts available now in Live!

Get 1:1 Help Now