Hello,
I was looking to export documents in a view to tabular text format on a scheduled basis(monthly) and send this document as an attachment along with an email. I am using Domino Designer 8.0.2.
Please help!
Subject: Easy
use the FileExport @Command
Subject: What I tried!
I tried this…
@Command([OpenView] ; “ACCESS EXPORT”);
@Command([EditSelectAll])
@Command([FileExport] ; “Tabular Text” ; “C:\tabulartext.txt”)
However, its not working as required. Need to make this work using a scheduled agent.
Subject: How is it “not working” ?
Does your PC blow up when running it? Does it not create a file? Does it create a file but it’s empty?
Subject: File not created
Hi Carl,
I set this agent as a scheduled agent. When the agent runs it does not create a file.
Subject: Are you checking your server for the file
or your machine? Did you give the agent the increased permissions it requires to write to the server hard drive?
Subject: Export to Tabular Text working somewhat fine!
Thanks Carl! That tip for checking the server gave me some direction for my work. I hadn’t checked the server as do not have access to the drives.
I just thought of sharing where I have reached so far, may be someone could help out.
Following is the piece of code I found to export view to tabular text file using LS.
Const textfile$ = “C:\output.txt”
Const fldDelim% = 9 ’ ASCII value of tab under Windows
Dim session As NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim lastCol As Integer ’ Upper bound of columns.
Dim colCnt As Integer ’ Iterator for columns.
Dim txt As String ’ Accumulator for row (document) output.
Dim fileNum As Integer ’ File handle.
Dim docCnt As Integer ’ Document counter.
’ Initialise objects and variables.
Set session = New NotesSession
Set db = session.CurrentDatabase
Set view = db.GetView(“Export”)
lastCol = Ubound(view.Columns)
colCnt = 0
txt = “”
fileNum% = Freefile()
’ Delete old output file.
If Dir$(textfile, 0) <> “” Then
Kill textfile
End If
’ Open the output file.
Open textfile For Output As fileNum%
’ Print the view’s column titles.
Forall c In view.Columns
txt = txt + c.Title
If (colCnt <> lastCol) Then
txt = txt + Chr$(fldDelim)
End If
colCnt = colCnt + 1
End Forall
Print #fileNum%, txt
’ Print the column values for each document.
Set doc = view.GetFirstDocument
docCnt = 0
While Not (doc Is Nothing)
txt = “”
For colCnt = 0 To lastCol Step 1
txt = txt + doc.ColumnValues(colCnt)
If (colCnt <> lastCol) Then
txt = txt + Chr$(fldDelim)
End If
Next
Print #fileNum%, txt
docCnt = docCnt + 1
Set doc = view.GetNextDocument(doc)
Wend
’ Close the output file.
Close fileNum%
I would like to enhace this code further.
When I run it from designer, it runs fine and creates a text file at the location specified with some bad formatting. It simply adds tabs after each value.In the output file, the first column is aligned correctly. Rest of the columns and the values below are just badly aligned. Can anyone help me in aligning the column values properly?
Secondly, I am required to run this agent as a scheduled agent. Given the fact that the databases reside on the server, will the output file be created on the server when this agent is run as scheduled? When the agent is run from designer after right click → Run, it creates file on the local machine and if the same agent is set as scheduled it does not create any file, kind of confuses me!
Subject: Like Carl said
You need to make sure the id that is running the domino server service has ad rights to created the file on the drive.
You need to as well make sure the id that signed the agent has rights to run unrestricted agents.
If you don’t have access to the server perhaps have the agent map a drive letter to a share that you have access to or email the attachment at the end.
Subject: Further Action
Thanks Carl and Barry!
I now have the rights to run unrestricted agents on the server. Also, I have stepped down the security of the agent to level=2, allow restricted operations.
I have cleared out 2 things after this,
-
I am able to create doc on the server.
-
attach this doc and send using email.
I am now stuck with the basic thing, formatting the tabular text file. Can anyone let me know how I can modify my code to align the columns neatly as done by File->Export->Tabular Text.
Thanks in advance.
Subject: Is it adding tabs?
If you look at the created file with a text editor, such as notepad++ and show all characters, do you see tabs after each columns entires.
If you do and they’re not aligned, it’s because the contents in your file are wider than the tab spacing. If you want it to appear nicely, you’ll need to figure out the widest column width and then work out how many tabs that is and then calculate how many you need to add to shorter text to make it look right.
BUT I imagine you don’t need to that and you’re not thinking this through, what are you doing with the exported file, if it’s tab delimited and you import it into excel, excel will use the tab as the delimiter and things will look to be in the right column, if you were to take the file and increase the number of tabs to make it look right in notepad, then it would look like shit in Excel, with things in the wrong columns.
You can also just try increasing the tab spacing in whatever app you’re viewing the text file in.
Think through the entire problem, what is the end goal for that file, what is the end consumer. Don’t just think I want a tab delimited file, think what you want to achieve.
Subject: Question
Why not just use a delimiter like | or tab and when you import into excel you specify the delimiter.
In order to define fixed columns you will need to check each field length and pad with spaces.
The other option is put chr(34) (double quotes) before and after each value with a comma in between. If you have a .csv extension Excel will have no issues with the conversion.
This is what code change I suggest
txt = chr(34)
For colCnt = 0 To lastCol Step 1
txt = txt + doc.ColumnValues(colCnt)
If (colCnt <> lastCol) Then
txt = txt + chr(34) & “,” & chr(34)
End If
Next
Print #fileNum%, txt ;chr(34)
Subject: love at first sight
I agree that there are better formats to export data into. Excel is readable and csv can be converted to excel(popular).
My client once asked me to export data into some a text file. I showed them export to csv and tabular text using inbuilt export in Notes thinking it would be just one off export.
For some reason they fell in love with Tabular text format and asked me to develop a
functionality which exports into tabular text and emails it to specific user. I tried to convince then saying that excel would be better and there is a wide scope with excel, but they won’t listen to me. It seems for them it was ‘love at first sight’ with the tabular text format.
Any way thanks for all the prompt and quick responses. Helped me cover a lot of ground in quick time.
Still working to fix the tab issue.
By the way, is there any place we can find the source code which Lotus Notes actually uses to export. Do they publish it by any chance?
Subject: ok then
Here is info on how to get column width from the view
Use that to know how much spaces to pad on each column so the line up pretty
Subject: Working Code
I just thought of sharing the code just in case anyone needs it
Sub Initialize
Const textfile$ = "C:\Lotus\MyFile.txt"
Const fldDelim% = 9 ' ASCII value of tab under Windows
Dim session As NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim lastCol As Integer ' Upper bound of columns.
Dim colCnt As Integer ' Iterator for columns.
Dim txt, row As String ' Accumulator for row (document) output.
Dim fileNum As Integer ' File handle.
Dim docCnt As Integer ' Document counter.
’ Initialise objects and variables.
Set session = New NotesSession
Set db = session.CurrentDatabase
Set view = db.GetView("MyView")
lastCol = Ubound(view.Columns)
colCnt = 0
txt = ""
row=""
fileNum% = Freefile()
’ Delete old output file.
If Dir$(textfile, 0) <> "" Then
Kill textfile
End If
’ Open the output file.
Open textfile For Output As fileNum%
’ Print the view’s column titles.
Forall c In view.Columns
'txt = txt + c.Title
txt = c.Title
If (colCnt <> lastCol) Then
'txt = txt + Chr$(fldDelim) + Chr$(fldDelim)
txt = Left(txt & Space(20), 20)
End If
row= row + txt
colCnt = colCnt + 1
End Forall
Print #fileNum%, row
’ Print the column values for each document.
Set doc = view.GetFirstDocument
docCnt = 0
While Not (doc Is Nothing)
txt = ""
row=""
For colCnt = 0 To lastCol Step 1
txt = doc.ColumnValues(colCnt)
If (colCnt <> lastCol) Then
'txt = txt + Chr$(fldDelim) + Chr$(fldDelim)
txt = Left(txt & Space(20), 20)
End If
row= row + txt
Next
Print #fileNum%, row
docCnt = docCnt + 1
Set doc = view.GetNextDocument(doc)
Wend
’ Close the output file.
Close fileNum%
Messagebox "Finished exporting " & docCnt & " documents to " & textfile
End Sub
This code works very well and is very close to what I was looking for. However, if I change the parameters in left() and space() functions in code: Left(txt & Space(20), 20)(which right now is 20), it spoils the alignment. Any idea how to get through, since some of my column data is 30 characters long?
Thanks in advance!
Subject: You need to find in your code the maximum width for a column
and then use that as the width to use vs hard coding a value.
Subject: Thanks Bary and Carl!
My code is in working condition.
Subject: Notes isn’t open source, so they don’t publish the code.