asked on

Lotus Script to Export a view and attachments to Excel

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

Steve Knight

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

Steve Knight

sorry that obviously decided to submit twice... you get the idea.

jkteater

ASKER

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.

Steve Knight

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.

jkteater

ASKER

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

Steve Knight

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

jkteater

ASKER

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.

Sjef Bosman

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?

Steve Knight

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

jkteater

ASKER

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?

Steve Knight

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!

jkteater

ASKER

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.

Sjef Bosman

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.

jkteater

ASKER

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.

ASKER CERTIFIED SOLUTION

Steve Knight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jkteater

ASKER

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.

Steve Knight

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