Export Domino Database 6.5 to excel 1

[frankygee]

Hi,
is there a way to export domino data to an excel spreadsheet.
The database contains client contact details i.e. name address phone and email.
The database is in .nsf format.
I don't need all the data from the database just the contact details.
Sorry I have never used Lotus so if a script was required (as I have seen recommended) I would need advice on how to use it.

Cheers

 

[pmonett]

You can export a view to a text file, which can then be opened in Excel.

Open the view that contains the data you wish to export, then go to File/Export and choose where to save and under what format.

That should do the trick for what you want.

Pascal.

I've got nothing to hide, and I'd very much like to keep that away from prying eyes.

 

[frankygee]

Hi Pascal,
Sorry for the delay in replying to your advice.
I had to get a copy of 123 so I could test the various export options.
The problem is that I cannot open a view with all the data I want to export.
If I open the database listing all the records I can only export the list of records not the data contained within.
If I open a record I can only export the data in that one record.
There are about 5000 records in the database.
I only want to export the data in the basic tab ie. Name address, phone and email.
I have tried 123, structured text and tabular text.
I am working Notes 6.5.

 

[pmonett]

Well I'm the late one now.

Don't know where you are at with your export, but you clearly need another view with the data you wish to export.

Either that or you will need a script that goes doc by doc and creates a csv file that you can then import into whatever spreadsheet you are using.

Pascal.

I've got nothing to hide, and I'd very much like to keep that away from prying eyes.

 

[frankygee]

A bit beyond me.
Was hoping there would be a way to export from within the app.
However thanks for pointing me in the right direction.
Will se if I can get someone with the skills to write a script to do the extract.

 

[pmonett]

Here is a simple file export code I wrote a while ago. It iterates through all the documents in a view and calls a sub for each doc. That sub contains the export logic, ie. writes a line of text for all the fields you wish to export.

You (or the developer) will have to fill in the export logic and redefine the constants, but this framework will achieve the export once that is done.

Hope it can help.

Option Public
Option Declare

Dim outStream As NotesStream
Dim linecount As Integer

Sub Initialize()
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim parmdb As NotesDatabase
	Dim parmview As NotesView
	Dim view As NotesView
	Dim doc As NotesDocument
	Dim olddoc As NotesDocument
	Dim iter As Integer
	Dim outPath As String
	Const filename = "export.csv"
	Const filepath = "D:\"
	Const file_encoding = "UTF-8"
	Const exportView = "FileData"
	
	Set db = session.Currentdatabase
	outPath = filepath & filename
	Set outStream = session.CreateStream
	If Not outStream.Open(outPath, file_encoding) Then
		Print "ERROR : cannot open file at " & outpath
		Exit Sub
	End If
  
	Set view = db.Getview(exportView)
	Set doc = view.Getfirstdocument()
	iter = 0
	linecount = 0
	Do While Not(doc Is Nothing)
		If Not(doc.Hasitem("flatfiledate")) Then
			doc.flatfiledate = Now
			iter = iter + 1
			If doc.Hasitem("body")then
				Call createFlatData(doc)
			End If
			Call doc.save(False,False,False)
		End If
		Set olddoc = doc
		Set doc = view.Getnextdocument(olddoc)
		Delete olddoc
	Loop
	Call outStream.Close()
	Print "wrote " & linecount & " lines"
End Sub

Function formatDate(wdate As String) As String
	Dim docdate As NotesDateTime
	Dim result As String
	Dim w As String
	
	Set docdate = New NotesDateTime(wdate)
	result = Year(docdate.Dateonly)
	w = "0" & Month(docdate.Dateonly)
	w = Right(w,2)
	result = result & w
	w = "0" & day(docdate.Dateonly)
	w = Right(w,2)
	result = result & w
	formatDate = result
End Function

Sub createFlatData(doc As NotesDocument)
	Dim eline As String
	Dim sline As String
	Dim notesdbname As String
	Dim username As String
	Dim eventID As String
	Dim intro As Integer
	Dim middle As Integer
	Const sep = ","
	
	eline = ""
	sline = doc.fieldname(0)
	eline = sline
	sline = formatDate(doc.datefield(0))
	eline = eline & sep & sline
	etc...
	
	'committing to file
	Call outStream.Writetext(eline, EOL_CRLF)'EOL_CRLF = 0 for return+feed
	linecount = linecount + 1
End Sub

I've got nothing to hide, and I'd very much like to keep that away from prying eyes.