Field is too large (32K) error on specific documents when exporting data

I'm trying to export Lotus Notes Data to csv and especially the universal id's with the username.
I've already managed to export the documents and also managed to export many of the fields through other agents but i'm now trying to export the unique id's of the documents with the shortname if available or else the reader of the document.
It works: The data is exporting, but the problem i'm facing is that after about 4 hours of exporting the agent stops with an error:
"Notes error: Field is too large (32K) or View's column & selection formulas are too large"

And I can't find a way to skip the specific document and suppress this warning.
I've also tried to log the universal id of the document, but the universal id's that were last logged (and are next to it) don't give me an error when I open the documents.
I've already done a compact & fixup on the database, and also tried to copy the database to local and run it again (as some posts say it should help), but all have not lead to a solution.

Can someone tell me how i can find the related document and fix the field (ideally)?
Or else: Is there a way to just skip the document if this error occurres and go on to the next document?
I expected that 'On error resume nextdoc' would suppress the error, but this didn't make any sense and the error is still popping up.

This is the Agent i'm currently using (I'm not an expert in Lotusscript so it may be a bit messy):

Private Const MODULE 	= "Agent: Export-document-unique-ids-with-shortname"

Sub Initialize()
	
	On Error GoTo ErrorHandler
	Set AppLog 	= New ErrorLog(MODULE)
	Call AppLog.LogAction("Agent has started")
	
	Dim ses		As New NotesSession
	Dim dbProj As New NotesDatabase("SERVER/ORG","Apps\Application.nsf")	
	Dim colProj	As NotesDocumentCollection
	Dim docProj	As NotesDocument
	Dim strSearch	As String
	Dim fileNum As Integer
	Dim fileName As String
		
	fileNum% = FreeFile()
	fileName$ = "c:\temp\export_universalids_with_shortname.csv"
	
	strSearch	= {(Form="fmDocument" | Form="fmEmployee") & (DocDeleted <> "1" & DocDeleted <> "2") & !@IsAvailable($Conflict)}
	Set colProj	= dbProj.Search(strSearch,Nothing, 0)
	Set docProj	= colProj.GetFirstDocument()

	Dim LineToWrite As String
	Dim row As Integer
	row = 0
	
	Open fileName$ For Output As fileNum%
	
	LineToWrite = "DocUniversalID;ShortName;UserName"
	Print #fileNum%, LineToWrite
	LineToWrite = ""
	Do While Not docProj Is Nothing
		If(docProj.HasItem("ShortName")) Then
			Call AppLog.LogAction(docProj.UniversalID & CStr(docProj.getItemValue("ShortName")(0)))
			LineToWrite = CStr(docProj.UniversalID) & ";" _
			& CStr(docProj.getItemValue("ShortName")(0)) & ";"
			If(docProj.HasItem("DocReaders")) Then
				LineToWrite = LineToWrite & CStr(docProj.getItemValue("DocReaders")(0)) & ";"
			Else
				LineToWrite = LineToWrite & ";"
			End If
		ElseIf(docProj.HasItem("DocReaders")) Then
			Call AppLog.LogAction(docProj.UniversalID & CStr(docProj.getItemValue("DocReaders")(0)))
			LineToWrite = CStr(docProj.UniversalID) & ";" _
			& CStr(docProj.getItemValue("DocReaders")(0)) & ";"
		Else
			If (docProj.HasItem("UniversalID")) Then
				Call AppLog.LogAction(docProj.UniversalID & "- No fields available")
				LineToWrite = CStr(docProj.UniversalID) & ";" _
				& ";"
			Else
				Call AppLog.LogAction("Nothing available")
				LineToWrite = ""
			End If	
			
		End If
		
		If (LineToWrite <> "")Then
			Print #fileNum%, LineToWrite
		End If
		
		Set docProj	= colProj.GetNextDocument( docProj)
NextDoc:		
	Loop
	
	Close fileNum%
	
	Call AppLog.LogAction("File saved in: " & fileName)
	Call AppLog.LogAction("Agent has ended")
	
Terminator:
	'Close fileNum%
	Exit Sub
	
ErrorHandler:
	Call AppLog.LogError(Err, Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	
	Resume NextDoc	
End Sub

Open in new window

LVL 8
Darude1234Asked:
Who is Participating?
 
Sjef BosmanGroupware ConsultantCommented:
Did you try to run without logging? Do consider the possibility that your logging library is the cause of the problem.

You could continue in the next run where you stopped in the previous run when you use a view, and you order the documents by NoteId, and you save the last document handled in some permanent place (e.g. a profile document, or in the Environment). In that case, you can use GetDocumentByKey to get the previous document and take it from there.

Suggested code:

Use "llsErrorLog"
Use "dbOpenByKey"
Use "rtfExporter"

Private Const MODULE 	= "Agent: Export-richttext"

Sub Initialize
	On Error Goto fatal
	Set AppLog 	= New ErrorLog(MODULE)
	Call AppLog.LogAction("Agent has started")
	
	Dim session As New NotesSession
	Dim dbProj As New NotesDatabase("SERVER/ORG","Apps\Application.nsf")
	Dim colProj	As NotesDocumentCollection
	Dim docProj	As NotesDocument
	Dim docNext As NotesDocument
	Dim strSearch	As String
	
	strSearch	= {(Form="fmDocument" | Form="fmEmployee") & (DocDeleted <> "1" & DocDeleted <> "2") & (Status <> "Closed")& !@IsAvailable($Conflict)}
	Set colProj	= dbProj.Search(strSearch,Nothing, 0)
	Set docProj	= colProj.GetFirstDocument()
	Dim fileNum As Integer
	Dim fileName As String
	
	Do Until docProj Is Nothing
		Set docNext = colProj.GetNextDocument(docProj)
		fileNum = FreeFile()
		fileName = "c:\temp\rtf_fields\" & docProj.UniversalID & ".rtf"
		Open fileName For Output As fileNum

		On Error GoTo catch

		Call WriteRTFHeader(fileNum, docProj.Title(0), LETTER_PORTRAIT)
		
		Call WriteRTFText(fileNum, docProj.Title(0), FONT_SANS, TEXT_BOLD & TEXT_ITALIC, 14)
		Call WriteRTFNewline(fileNum)
		Call WriteRTFField(fileNum, docProj, "Body")
			
		Call AppLog.LogAction(docProj.Noteid & " | " & docProj.UniversalID & " | " & docProj.Notesurl)
		If Not (docNext Is Nothing) Then
			Call WriteRTFPageBreak(fileNum)
		End If
retry:					
		Call CloseRTFFile(fileNum)		
		Set docProj= docNext
		On Error Goto 0 ' reset error trapping
	Loop
	Exit Sub

catch:
	Call AppLog.LogError(Err, Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	Resume retry	

fatal:
	Call AppLog.LogError(Err, "Fatal " & Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	Exit Sub	

End Sub

Open in new window

0
 
Sjef BosmanGroupware ConsultantCommented:
The code doesn't look that bad, not at all.

Some observations:
- the Resume nextdoc is okay, but the label isn't placed at the correct line; the agent will handle the same document again, and again...
- I guess the contents of LineToWrite grows over 32K; I assume you forgot one line,
     60.5: LineToWrite= ""
- line 48: do the documents have their UniversalId in a field?? Usually, it is a property of a document, and it's not supposed to be stored in a document. Every document has this property, so the test on line 48 only makes sense if you want to see if there's a field (i.e. item) with that name.
0
 
Sjef BosmanGroupware ConsultantCommented:
PS Sorry for the late reply, I was away (familiebezoek in N-Brabant)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Darude1234Author Commented:
Thanks for your reply, no problem ;)
I've changed Resume Nextdoc to just Resume Next, a label is not necessary, it should just skip the document. Or are there alternatives to skip items in a loop?
I've also added LineToWrite="" in ErrorHandler and right before the getnextdocument()
I've also changed line 48 to docProj.UniversalID, I tried to get the UniversalID of the document not specific a stored DocID (althought most documents have, but probably not all), because the agent i'm using to export the attachments is also using the UniversalID as foldername for the documents i'm exporting the attachments from.

I'm running the export now, but it takes a couple of hours, so I will let you know when it is ready (or when it generated an error again :) )
Thanks for your help.

So it now looks like this:
Option Public
Option Declare

Use "llsErrorLog"
Use "dbOpenByKey"

Private Const MODULE 	= "Agent: Export-document-unique-ids-with-shortname"

Sub Initialize()
	
	On Error GoTo ErrorHandler
	Set AppLog 	= New ErrorLog(MODULE)
	Call AppLog.LogAction("Agent has started")
	
	Dim ses		As New NotesSession
	Dim dbProj As New NotesDatabase("SERVER/ORG","Apps\Application.nsf")	
	Dim colProj	As NotesDocumentCollection
	Dim docProj	As NotesDocument
	Dim strSearch	As String
	Dim fileNum As Integer
	Dim fileName As String
		
	fileNum% = FreeFile()
	fileName$ = "c:\temp\export_universalids_with_shortname.csv"
	
	strSearch	= {(Form="fmDocument" | Form="fmEmployee") & (DocDeleted <> "1" & DocDeleted <> "2") & !@IsAvailable($Conflict)}
	Set colProj	= dbProj.Search(strSearch,Nothing, 0)
	Set docProj	= colProj.GetFirstDocument()

	Dim LineToWrite As String
	Dim row As Integer
	row = 0
		
	Open fileName$ For Output As fileNum%
	
	LineToWrite = "DocUniversalID;ShortName;UserName"
	Print #fileNum%, LineToWrite
	LineToWrite = ""
	
	Dim skipped As Boolean
	skipped = False
	
	Do While Not docProj Is Nothing
		If(docProj.HasItem("ShortName")) Then
			Call AppLog.LogAction(docProj.UniversalID & CStr(docProj.getItemValue("ShortName")(0)))
			LineToWrite = CStr(docProj.UniversalID) & ";" _
			& CStr(docProj.getItemValue("ShortName")(0)) & ";"
			If(docProj.HasItem("DocReaders")) Then
				LineToWrite = LineToWrite & CStr(docProj.getItemValue("DocReaders")(0)) & ";"
			Else
				LineToWrite = LineToWrite & ";"
			End If
		ElseIf(docProj.HasItem("DocReaders")) Then
			Call AppLog.LogAction(docProj.UniversalID & CStr(docProj.getItemValue("DocReaders")(0)))
			LineToWrite = CStr(docProj.UniversalID) & ";" _
			& CStr(docProj.getItemValue("DocReaders")(0)) & ";"
		Else
			If (docProj.UniversalID) Then
				Call AppLog.LogAction(docProj.UniversalID & "- No fields available")
				LineToWrite = CStr(docProj.UniversalID) & ";" _
				& ";"
			Else
				Call AppLog.LogAction("Nothing available")
				LineToWrite = ""
			End If	
			
		End If
		
		If (LineToWrite <> "")Then
			Print #fileNum%, LineToWrite
		End If
		
		LineToWrite = ""
		Set docProj	= colProj.GetNextDocument( docProj)				
	Loop
	
	Close fileNum%
	
	Call AppLog.LogAction("File saved in: " & fileName)
	Call AppLog.LogAction("Agent has ended")
	
Terminator:
	'Close fileNum%
	Exit Sub
	
ErrorHandler:
	Call AppLog.LogError(Err, Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	If skipped = True Then
		Call AppLog.LogAction("Doc ID: " & docProj.UniversalID & " skipped")
	End If
	LineToWrite = ""
	
	Resume Next
End Sub

Open in new window

0
 
Sjef BosmanGroupware ConsultantCommented:
Resume Next isn't the right way to do it, especially if there are other errors in your code. Let's hope there aren't any. The better way would be to place your label Nextdoc on line 72, and use Resume Nextdoc on line 93. You wouldn't even have to set LineToWrite to "" in the ErrorHandler code...

And what's skipped for? It isn't used, as far as I can see.

PS This smells like a migration... :-(
PS2 Why don't you use the standard File/Export... and the CSV format? Create the view you need, open it, and export it. It's a lot faster!
0
 
Darude1234Author Commented:
Sorry, to come back to you so lately, but I finally made some progress.
The export is running again so I can let you know something when its done. But it will take a couple of hours

I know Resume Next isn't the right way and I should handle the error, but the because it kept firing me the 32k error I hoped it would skip the document and prevented the 32k error, which it didn't. I've fixed it.

I've removed the skipped variable, it isn't used anymore, it used it earlier to decide when the fields are available or not and it should write to the file, but later changed it to just check if LineToWrite is empty or not.

PS It's indeed a migration and I'm also not happy with it :-(. I'm just the IT guy who has to follow management decisions.
PS2 Well I tried, but it, for some reason some fields wouldn't show up in the view, tried everything even compact/fixup ect. And because I also wanted to export the rich-text fields including the documents I still needed to write a little lotusscript, so made the decision to write the whole thing in lotusscript.
0
 
Sjef BosmanGroupware ConsultantCommented:
Export finished? ;-)

Re PS: I suppose you're one of the last companies in the Netherlands that uses Domino. There are a few more die-hards, I sometimes work for one of them, and they're very happy to have their entire business environment in Domino/Notes. It has run flawlessly for over 20 years now, with the inevitable maintenance of course, and some developments. Management...

Re PS2 Rich text, yes that's often a show stopper. There are a few ways to export rich text, but none of them are easy and perfect.
You could try in pure LotusScript, using the NotesRichText classes, but it seems impossible to get all the details and elements.
Then you could try to open the document in a browser, where Domino converts the rich text to HTML, and you export from the browser, or using a site dump utility.
You could also use the Domino internal calls to convert a document to MIME, and fetch the HTML there. Another possibility would be to use DXL, where you get all the details but it's quite complicated.
And you could also get (i.e. buy) a library that handles the conversion (e.g. Ben Langhinrichs' company, the Midas library).
Finally, there seem to be tools that convert a database to PDF, but that's mainly for mail I think.

I wonder if the new environment is that easy to work with.
0
 
Darude1234Author Commented:
Well yes, but the error popped up again ;). I think (or hope) I just found the problematic document by logging the @noteid and @documentuniqueid
I've backupped the specific document to another database & removed it from the database. The specific document was not needed, because it wouldn't included in the final export.
The export is running again now, so it will take some time before I know more.

Rich text: Well I found a class what does the job very well in my opinion. It's not perfect off course, but all information is more than readable. I can post a link when the export is finished.
0
 
Sjef BosmanGroupware ConsultantCommented:
Ok, interesting! :-)
0
 
Darude1234Author Commented:
It's this library: http://www.nsftools.com/tips/NotesTips.htm#rtfexport (Rtftest.zip)

Progress: The error is still popping up, but the problematic note id's are now logged correctly so i can find them.
So I now run the export and everytime the export stops the last noteid & docid are logged and i'm able to backup and remove the corrupted documents.
But it's time consuming because everytime the error pop-upped i need to start the export all over.

I see that the export is running on alphabetic order of the NoteID. Is there a possibility to start the document search from a specific NoteID? If I can change the search query to something like docProj.Noteid > "4C9A" that would save a lot of time.
Or off course something that I can do to prevent that the 32K error is popping up, but until now I haven't found such possibility.

I currently use the following code for the export of the documents:

Use "llsErrorLog"
Use "dbOpenByKey"
Use "rtfExporter"

Private Const MODULE 	= "Agent: Export-richttext"

Sub Initialize
	On Error GoTo ErrorHandler
	Set AppLog 	= New ErrorLog(MODULE)
	Call AppLog.LogAction("Agent has started")
	
	Dim session As New NotesSession
	Dim dbProj As New NotesDatabase("SERVER/ORG","Apps\Application.nsf")
	Dim colProj	As NotesDocumentCollection
	Dim docProj	As NotesDocument
	Dim strSearch	As String
	
	strSearch	= {(Form="fmDocument" | Form="fmEmployee") & (DocDeleted <> "1" & DocDeleted <> "2") & (Status <> "Closed")& !@IsAvailable($Conflict)}
	Set colProj	= dbProj.Search(strSearch,Nothing, 0)
	Set docProj	= colProj.GetFirstDocument()
	Dim fileNum As Integer
	Dim fileName As String
	
	Do Until (docProj Is Nothing)
		fileNum = FreeFile()
		fileName = "c:\temp\rtf_fields\" & docProj.UniversalID & ".rtf"
		Open fileName For Output As fileNum

		Call WriteRTFHeader(fileNum, docProj.Title(0), LETTER_PORTRAIT)
		
		Call WriteRTFText(fileNum, docProj.Title(0), FONT_SANS, TEXT_BOLD & TEXT_ITALIC, 14)
		Call WriteRTFNewline(fileNum)
		Call WriteRTFField(fileNum, docProj, "Body")
			
		Set docProj = colProj.GetNextDocument(docProj)
		Call AppLog.LogAction(docProj.Noteid & " | " & docProj.UniversalID & " | " & docProj.Notesurl)
		If Not (docProj Is Nothing) Then
			Call WriteRTFPageBreak(fileNum)
		End If
					
		Call CloseRTFFile(fileNum)		
	Loop

ErrorHandler:
	Call AppLog.LogError(Err, Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	
	Resume Next	
End Sub

Open in new window

0
 
Darude1234Author Commented:
Update:
- I tried without logging but that didn't help.
- Then I tried your suggested code, but the error still pop-upped.
- Then I tried again with the view method as you mentioned earlier to select the documents after a specified noteID and I finally got the selected documents after a specific note id in the view (still don't understand why that didn't work earlier).
So I selected all note id's in the view after the note id where things go wrong and the agent is running again.
That will save a lot of time :)

The agent is running again...
0
 
Sjef BosmanGroupware ConsultantCommented:
Fingers crossed...
0
 
Sjef BosmanGroupware ConsultantCommented:
And...?
0
 
Darude1234Author Commented:
Well after a lot of exporting again and again and removing the corrupt documents I managed to finish the export.
I will post my updated code tomorrow.
I hoped to make it a more all-in-one solution but separated some things (agent for csv fields, agent for rtf fields and agent for attachments) to make it easier.
0
 
Sjef BosmanGroupware ConsultantCommented:
Great! And unfortunate... ;-)
0
 
Sjef BosmanGroupware ConsultantCommented:
Tomorrow? ;-)
0
 
Darude1234Author Commented:
Yeah, i'm very sorry, it's very busy at the moment. The Lotus Export, a release schedule for our core application and in the mean time we had to switch the virusscanner on all our PC's because the license was about to expire on the end of this week (tomorrow).
But that's no excuse to react to you faster ;) Thanks for all your help, I really appreciate it.

The agent I used to extract the attachments is the following:
Option Public
Option Declare

Use "llsErrorLog"

Private Const MODULE 	= "Agent: Export-documents-attachments2"

Sub Initialize
	On Error Resume Next
	
	Set AppLog = New ErrorLog(MODULE)
	Call AppLog.LogAction("Agent has started")
	
	Dim sess As New NotesSession
	Dim dbProj As New NotesDatabase("SERVER/ORG","Apps\Application.nsf")
	Dim colProj	As NotesDocumentCollection
	Dim docProj	As NotesDocument
	Dim nextdoc As NotesDocument
	Dim rtitem As Variant
	Dim filename As String
	Dim i As Integer
	Dim strname As String
	
	Set colProj = dbProj.Alldocuments
	Set docProj = colProj.getfirstdocument()
	
	While Not docProj Is Nothing
		On Error GoTo nobody
		Set nextdoc = colProj.getnextdocument(docProj)
		
		If Not docProj.hasItem("LetterBody") Then
			GoTo Body
		Else
			Set rtitem = docProj.GetFirstItem( "LetterBody" )
			If Not rtitem Is Nothing Then
				If IsArray(rtitem.EmbeddedObjects) Then
					ForAll o In rtitem.EmbeddedObjects
						If ( o.Type = EMBED_ATTACHMENT ) Then
							MkDir("C:\temp\export_documents2\" & docProj.Universalid)
							If o.name <> o.source Then
								filename = "C:\temp\export_documents2\" & docProj.Universalid & "\" & o.source & +1
							Else
								filename = "C:\temp\export_documents2\" & docProj.Universalid & "\"& o.source
							End If
							Call o.ExtractFile( filename)
						End If
					End ForAll
				End If
			End If
		End If

Body:		
		If Not docProj.hasItem("Body") Then
			GoTo nobody
		Else
			Set rtitem = docProj.GetFirstItem( "Body" )
			If Not rtitem Is Nothing Then
				If ( rtitem.Type = RICHTEXT ) Then
					If IsArray(rtitem.EmbeddedObjects) Then
						ForAll o In rtitem.EmbeddedObjects
							If ( o.Type = EMBED_ATTACHMENT ) Then
								MkDir("C:\temp\export_documents2\" & docProj.Universalid)
								If o.name <> o.source Then
									filename = "C:\temp\export_documents2\" & docProj.Universalid & "\" & o.source & +1
								Else
									filename = "C:\temp\export_documents2\" & docProj.Universalid & "\"& o.source
								End If
							
								Call o.ExtractFile(filename)
							End If
						End ForAll
					End If
				End If
			End If
		End If
nextdoc:
		Set docProj = nextdoc
		Do Until Not docProj Is nothing
			Set docProj = colProj.getnextdocument(docProj)
		Loop
	Wend

	GoTo einde

nobody:
	Set docProj = colProj.getnextdocument(docProj)
	Resume Next

fatal:
	Call AppLog.LogError(Err, "Fatal error: " & Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	GoTo nextdoc
	
einde:
	Call AppLog.LogAction("Agent has ended")
End Sub

Open in new window


And this is the code i used to extract the RTF-fields:
With the help of this library: http://www.nsftools.com/tips/NotesTips.htm#rtfexport (Rtftest.zip) All credit's of the library 'rtfExporter' goes to Julian Robichaux (as I believe is name is). I won't include it here, the one who want's to use it can download it from his site.
Also the code of the errorlog i won't include here, you can achieve the same with NotesLog but we use our own errorlog application, written by a former colleague.
First I created the a view as you mentioned with the following view selection, where I could change the noteid's to get a specific set of documents (in this case I needed a document with noteID NT000079D6, but if you change it to f.e. NT00005000 and NT00008BD2, you get all documents in between) And that's how I selected the documents every time: after a document went wrong I higher the first number to the document next to the wrong document:
SELECT (@NoteID > "NT000079D5" & @NoteID < "NT000079D7") & DocDeleted != "1" & DocDeleted != "2" & Status != "Closed" & !@IsAvailable($Conflict) & (Form="fmDocument" | Form="fmEmployee")

Open in new window

And the view had a column with the noteID:
@Text(@NoteID)

Open in new window

and DocumentUniqueID:
@Text(@DocumentUniqueID)

Open in new window


So this is the code to extract the RTF-fields:
Option Public
Option Declare

Use "llsErrorLog"
Use "rtfExporter"

Private Const MODULE 	= "Agent: Export-richttext-after-specific-noteid-aangepast-voor-notebody"

Sub Initialize
	On Error GoTo fatal
	Set AppLog 	= New ErrorLog(MODULE)
	Call AppLog.LogAction("Agent has started")
	
	Dim session As New NotesSession
	Dim dbProj As New NotesDatabase("SERVER/ORG","Apps\Application.nsf")
	Dim colProj	As NotesDocumentCollection
	Dim docProj	As NotesDocument
	Dim docNext As NotesDocument
	Dim strSearch	As String
	
	'strSearch	= {(Form="fmDocument" | Form="fmEmployee") & (DocDeleted <> "1" & DocDeleted <> "2") & (Status <> "Closed") & !@IsAvailable($Conflict)}
	'Set colProj	= dbProj.Search(strSearch,Nothing, 0)
	'Set docProj	= colProj.GetFirstDocument()
	Dim view As NotesView
	Set view = dbProj.GetView( "(0. Ruud\Debug Specific NoteID)" )
	Set docProj = view.Getfirstdocument()
	Dim fileNum As Integer
	Dim fileName As String
	
	Do Until docProj Is Nothing
		Call AppLog.LogAction(docProj.Noteid & " | " & docProj.UniversalID & " | " & docProj.Notesurl)
		Set docNext = view.Getnextdocument(docProj)
		fileNum = FreeFile()
		fileName = "C:\temp\export_documents\" & docProj.UniversalID & ".rtf"
		
		Open fileName For Output As fileNum
		
		Call WriteRTFHeader(fileNum, docProj.Title(0), LETTER_PORTRAIT)
		Call WriteRTFText(fileNum, docProj.Title(0), FONT_SANS, TEXT_BOLD & TEXT_ITALIC, 14)
		Call WriteRTFNewline(fileNum)
		
		If Not docProj.hasItem("LetterBody") Then
			GoTo Body
		Else
			Call WriteRTFField(fileNum, docProj, "LetterBody")
			GoTo retry
		End If

Body:		
		If Not docProj.hasItem("Body") Then
			GoTo nobody
		Else
			Call WriteRTFField(fileNum, docProj, "Body")
			GoTo retry
		End If
		
retry:
		On Error GoTo skip
		Call CloseRTFFile(fileNum)
skip:		
		Set docProj= docNext
		On Error GoTo 0 'reset error trapping
	Loop
	Call AppLog.LogAction("Agent has ended") 'Added later so only in the last log files after 7-2-2018 17:15 ;)
	Exit Sub

nobody:
	Call AppLog.LogAction("No body field in: " & docProj.Noteid & " | " & docProj.UniversalID & " | " & docProj.Notesurl)
	'Call WriteRTFField(fileNum, docProj, "Body")
	GoTo retry 'reset error trapping

catch:
	Call AppLog.LogError(Err, Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	GoTo retry
	
fatal:
	Call AppLog.LogError(Err, "Fatal: " & Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	Exit Sub
	
End Sub

Open in new window



And the code to export some basic text-fields (had 7 more for other document and fields but they all the same idea):
Option Public
Option Declare

Use "llsErrorLog"

Private Const MODULE 	= "Agent: Export-csv"

Sub Initialize()
	
	On Error GoTo ErrorHandler
	Set AppLog 	= New ErrorLog(MODULE)
	Call AppLog.LogAction("Agent has started")
	
	Dim ses		As New NotesSession
	Dim dbProj As New NotesDatabase("SERVER/ORG","Apps\Application.nsf")	
	Dim colProj	As NotesDocumentCollection
	Dim docProj	As NotesDocument
	Dim strSearch	As String
	'Dim strAdvID	As String
	Dim fileNum As Integer
	Dim fileName As String
		
	fileNum% = FreeFile()
	fileName$ = "c:\temp\employee_basisgegevens.csv"
	
	strSearch	= {Form="fmEmployee" & (DocDeleted <> "1" & DocDeleted <> "2") & !@IsAvailable($Conflict) & Status="Active" & FunctionTitle <> "Board" }
	Set colProj	= dbProj.Search(strSearch,Nothing, 0)
	Set docProj	= colProj.GetFirstDocument()

	Dim LineToWrite As String
	Dim row As Integer
	row = 0
	
	Open fileName$ For Output As fileNum%
	
	LineToWrite = "DocType;Gender;Initials;Surname;Firstname;Infix;Address;ZipCode;City;CountryOfResidence;TelephoneExt;TelCountry;TelArea;TelNumber;FaxCountry;FaxArea;FaxNum;MobileCountry;MobileArea;MobileNumber;Nationality;FunctionTitle;EmployeeType;Department;Title;PostTitle;DateOfBirth;BSNNumber;InternetAddress;EMailAddress;EmployeeID;SkypeName;MaritalStatus;Swiftcode;Iban;ShortName;PassportNumber;PassportFirstNames;PassportIssueDate;PassportExpirationDate;PassportIssueCity;HealthInsurance;HealthInsuranceNumber;GPName;GPAddress;GPZipCode;GPCity;GPCountry;GPTelephoneNumber;GPTelCountry;GPTelArea;GPTelNumber;ResponsibleForLeave;Manager;StaffType;TypeOfContract;ContractStartDateExt;ContractEndDateExt;ContractStartDate;ContractEndDate;PercentageEmployability;JobScale;EmployeeScale;CommentsContract"
	Print #fileNum%, LineToWrite
	LineToWrite = ""
	Do While Not docProj Is Nothing
		
		LineToWrite = CStr(docProj.getItemValue("DocType")(0)) & ";" _
		& CStr(docProj.getItemValue("Gender")(0)) & ";" _
		& CStr(docProj.getItemValue("Initials")(0)) & ";" _
		& CStr(docProj.getItemValue("Surname")(0)) & ";" _
		& CStr(docProj.getItemValue("Firstname")(0)) & ";" _
		& CStr(docProj.getItemValue("Infix")(0)) & ";" _
		& CStr(docProj.getItemValue("Address")(0)) & ";" _
		& CStr(docProj.getItemValue("ZipCode")(0)) & ";" _
		& CStr(docProj.getItemValue("City")(0)) & ";" _
		& CStr(docProj.getItemValue("CountryOfResidence")(0)) & ";" _
		& CStr(docProj.getItemValue("TelephoneExt")(0)) & ";" _
		& CStr(docProj.getItemValue("TelCountry")(0)) & ";" _
		& CStr(docProj.getItemValue("TelArea")(0)) & ";" _
		& CStr(docProj.getItemValue("TelNumber")(0)) & ";" _
		& CStr(docProj.getItemValue("FaxCountry")(0)) & ";" _
		& CStr(docProj.getItemValue("FaxArea")(0)) & ";" _
		& CStr(docProj.getItemValue("FaxNum")(0)) & ";" _
		& CStr(docProj.getItemValue("MobileCountry")(0)) & ";" _
		& CStr(docProj.getItemValue("MobileArea")(0)) & ";" _
		& CStr(docProj.getItemValue("MobileNumber")(0)) & ";" _
		& CStr(docProj.getItemValue("Nationality")(0)) & ";" _
		& CStr(docProj.getItemValue("FunctionTitle")(0)) & ";" _
		& CStr(docProj.getItemValue("EmployeeType")(0)) & ";" _
		& CStr(docProj.getItemValue("Department")(0)) & ";" _
		& CStr(docProj.getItemValue("Title")(0)) & ";" _
		& CStr(docProj.getItemValue("PostTitle")(0)) & ";" _		
		& CStr(docProj.getItemValue("DateOfBirth")(0)) & ";" _
		& CStr(docProj.getItemValue("BSNNumber")(0)) & ";" _
		& CStr(docProj.getItemValue("InternetAddress")(0)) & ";" _
		& CStr(docProj.getItemValue("EMailAddress")(0)) & ";" _
		& CStr(docProj.getItemValue("EmployeeID")(0)) & ";" _
		& CStr(docProj.getItemValue("SkypeName")(0)) & ";" _
		& CStr(docProj.getItemValue("MaritalStatus")(0)) & ";" _
		& CStr(docProj.getItemValue("Swiftcode")(0)) & ";" _
		& CStr(docProj.getItemValue("Iban")(0)) & ";" _
		& CStr(docProj.getItemValue("ShortName")(0)) & ";" _
		& CStr(docProj.getItemValue("PassportNumber")(0)) & ";" _
		& CStr(docProj.getItemValue("PassportFirstNames")(0)) & ";" _
		& CStr(docProj.getItemValue("PassportIssueDate")(0)) & ";" _
		& CStr(docProj.getItemValue("PassportExpirationDate")(0)) & ";" _
		& CStr(docProj.getItemValue("PassportIssueCity")(0)) & ";" _
		& CStr(docProj.getItemValue("HealthInsurance")(0)) & ";" _
		& CStr(docProj.getItemValue("HealthInsuranceNumber")(0)) & ";" _
		& CStr(docProj.getItemValue("GPName")(0)) & ";" _
		& CStr(docProj.getItemValue("GPAddress")(0)) & ";" _
		& CStr(docProj.getItemValue("GPZipCode")(0)) & ";" _
		& CStr(docProj.getItemValue("GPCity")(0)) & ";" _
		& CStr(docProj.getItemValue("GPCountry")(0)) & ";" _
		& CStr(docProj.getItemValue("GPTelephoneNumber")(0)) & ";" _
		& CStr(docProj.getItemValue("GPTelCountry")(0)) & ";" _
		& CStr(docProj.getItemValue("GPTelArea")(0)) & ";" _
		& CStr(docProj.getItemValue("GPTelNumber")(0)) & ";" _
		& CStr(docProj.getItemValue("ResponsibleForLeave")(0)) & ";" _
		& CStr(docProj.getItemValue("Manager")(0)) & ";" _
		& CStr(docProj.getItemValue("StaffType")(0)) & ";" _
		& CStr(docProj.getItemValue("TypeOfContract")(0)) & ";" _
		& CStr(docProj.getItemValue("ContractStartDateExt")(0)) & ";" _
		& CStr(docProj.getItemValue("ContractEndDateExt")(0)) & ";" _
		& CStr(docProj.getItemValue("ContractStartDate")(0)) & ";" _
		& CStr(docProj.getItemValue("ContractEndDate")(0)) & ";" _
		& CStr(docProj.getItemValue("PercentageEmployability")(0)) & ";" _
		& CStr(docProj.getItemValue("JobScale")(0)) & ";" _
		& CStr(docProj.getItemValue("EmployeeScale")(0)) & ";"
		
		Print #fileNum%, LineToWrite
		
		Set docProj	= colProj.GetNextDocument( docProj)
NextDoc:		
	Loop
	
	Close fileNum%
	
	Call AppLog.LogAction("File saved in: " & fileName)
	Call AppLog.LogAction("Agent has ended")
	
Terminator:
	'Close fileNum%
	Exit Sub
	
ErrorHandler:
	Call AppLog.LogError(Err, Error$ & " in " & MODULE & "." & GetThreadInfo(LSI_THREAD_PROC) & " line " & Erl)
	
	Resume NextDoc
End Sub

Private Function ReplaceCharacters(strV As Variant) As String
	ReplaceCharacters = ""
	
	If InStr(1, strV, {"})>0 Then
		strV = CStr(Join(Split(strV, {"}), {[QUOTE]}))
	End If
	
	If InStr(1, strV, {,})>0 Then
		strV = CStr(Join(Split(strV, {,}), {[COMMA]}))
	End If
	
	If InStr(1, strV, Chr$(10))>0 Then
		strV = CStr(Join(Split(strV, Chr$(10)), {[LF]}))
	End If
	
	If InStr(1, strV, Chr$(13))>0 Then
		strV = CStr(Join(Split(strV, Chr$(13)), {[CR]}))
	End If
	
	If InStr(1, strV, Chr$(59))>0 Then
		strV = CStr(Join(Split(strV, Chr$(13)), {[DOTCOMMA]}))
	End If
	
	ReplaceCharacters = CStr(strV) & "" & ";"
	
End Function

Open in new window


Again thank you for everything, and if someone has the magic trick to completely skip 32k fields, please let me know ;)
I hope this is everything to make others life a little easier ;)
0
 
Darude1234Author Commented:
Thank you very much! Which I could give you more points, but don't see how.
0
 
Sjef BosmanGroupware ConsultantCommented:
Thanks for the documentation, it's pretty thorough! And you don't have to give more points, I'm just glad that you got your problem solved. I can only hope that you have more Notes questions in the future...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.