Please Advise : Reports from a Database

Get help using and writing Nisus Writer Pro macros.
Post Reply
Simon Knight
Posts: 13
Joined: 2007-10-31 02:15:13

Please Advise : Reports from a Database

Post by Simon Knight »

Hi, I have a small database application written in Realbasic. One problem is with the application (and RealBasic) is that producing reports is difficult. However, it is simple to produce a text file and I have been experimenting with CSV files. I would like to be able to import these CSV files into NWP and have the data displayed in columns (or in a table) inside a document based on a template that has headers and footers preset. Is this the sort of thing that I should be able to do using NWP and a script? If yes what flavour of script (AppleScript, Pearl, Python)? Bearing in mind that I have limited experience of AppleScript and no experience of Pearl or Python.

Any ideas or thoughts on where I should start.

many thanks

Simon Knight
User avatar
martin
Official Nisus Person
Posts: 5227
Joined: 2002-07-11 17:14:10
Location: San Diego, CA
Contact:

Re: Please Advise : Reports from a Database

Post by martin »

You could definitely put together a macro that would accomplish that kind of task. These would be the general steps:

1. Load up the CSV as plain text.
2. Convert the CSV text to tab delimited text.
3. Convert the tab delimited text to a proper NWP table (this part is easy, just use the menu Table > Convert to Table).
4. Open your template file and paste in a copy of the table you created.

I think step 2 would be the most work. It's definitely possible to convert CSV to tab delimited text using NWP's macro language, but it may be a complex task depending on the structure of your CSV. On the other hand, if you control the CSV export format, it could be quite easy. For example, if every field in the CSV file is guaranteed to be enclosed in double quote marks, then it would be as easy as a single find & replace command. As an alternative, there is likely a Perl module that parses CSV files into arrays/hashes for you.

But the rest of the steps are pretty straight forward and could be accomplished using NWP's macro language. The one thing I would note is that macros cannot currently control very many aspects of table cells, eg: background color, borders, etc. You could prepare a table in your template file, and then manually insert the tab delimited text, but that would be more complicated.
Simon Knight
Posts: 13
Joined: 2007-10-31 02:15:13

Re: Please Advise : Reports from a Database

Post by Simon Knight »

Martin,
Thanks for your reply. Since I first posted I have discovered that NWP "prefers" Tab separated files and that these convert into tables rather well. I have rewritten my export code to export the data with Tab characters, now all I have to do is learn how to write NWP macros.

thanks

Simon
User avatar
martin
Official Nisus Person
Posts: 5227
Joined: 2002-07-11 17:14:10
Location: San Diego, CA
Contact:

Re: Please Advise : Reports from a Database

Post by martin »

Great, that makes things much easier.

I'll presume you've found the macro language reference. Let us know if you have any questions.
Simon Knight
Posts: 13
Joined: 2007-10-31 02:15:13

Re: Please Advise : Reports from a Database

Post by Simon Knight »

Martin et al,
I am trying to automate my report generator and would like to create a document in NWP using Applescript. I have managed to create a new document, then add some text and then insert a table. I am now stuck as I have not found a method of writing text to the cells of the table. My script for what it is worth is here:

tell application "Nisus Writer Pro"
activate
set newDoc to make at front of documents new document
set text of newDoc to "Hello World."

--Is it possible to display a Nisus prompt ? (Yes)
set thetext to "Prompt ('Do you see me?','A test of the promp from Appe script', 'continue')"
--Do Menu Macro with macro thetext
-- Change the text
Do Menu Macro with macro "Select All"
Do Menu Macro with macro "Arial"
Do Menu Macro with macro "Bold"
set color of text of newDoc to "red"
--Move the selection to the end of the document otherwise the next insert overwrites the text
Do Menu Macro with macro "Select End"

--set some size variables up
set rows to 10
set cols to 3
--Build the insert table command based on the variables rows and columns
set command to "Insert Table " & rows & "," & cols
Do Menu Macro with macro command

-- I am unable to write to the table cells
--loop through the table
(*
repeat while row < rows + 1
repeat while col < cols + 1
set col to col + 1
*)

-- select the table cell and add text?
--Do Menu Macro with macro "TableSelection.new 1, 2, 2"
--end repeat
--set row to row + 1
--end repeat

end tell

I have tried to use a table object of newdoc but the syntax I use is wrong.
Is it possible to write to a table cell?

best wishes

Simon
User avatar
martin
Official Nisus Person
Posts: 5227
Joined: 2002-07-11 17:14:10
Location: San Diego, CA
Contact:

Re: Please Advise : Reports from a Database

Post by martin »

Hi Simon. I have very little AppleScript experience myself, but to write table rows you'd use something like this:

Code: Select all

	set row to 1
	repeat while row < rows + 1
		set col to 1
		repeat while col < cols + 1
			Do Menu Macro with macro "Select Table Cell " & row & ", " & col
			Do Menu Macro with macro "Type Text 'row " & row & "'"
			set col to col + 1
		end repeat
		set row to row + 1
	end repeat
But really, using AppleScript like this to just send single shot macro commands isn't efficient and seems counterproductive. The entire macro you have above could be rewritten natively as:

Code: Select all

New
Type Text "Hello World."

# Is it possible to display a Nisus prompt (yes)
# $thetext =  "Prompt 'Do you see me?','A test of the promp from Appe script', 'continue')"

# Change the text
Select All
Arial
Bold
Text Color:Red

# Move the selection to the end of the document otherwise the next insert overwrites the text
Select End
	
# set some size variables up
$rows = 10
$cols = 3
# Build the insert table command based on the variables rows and columns 
Insert Table $rows, $cols
	
# loop through the table 
$row = 1
While $row <= $rows
	$col = 1
	While $col <= $cols
		Select Table Cell $row, $col
		Type Text "row $row"
		$col = $col + 1
	End
	$row = $row + 1
End
You'll notice that it finishes running almost immediately, whereas the AppleScript goes so slow you can see each step being taken.
Simon Knight
Posts: 13
Joined: 2007-10-31 02:15:13

Re: Please Advise : Reports from a Database

Post by Simon Knight »

Martin,

Thank you for your reply. The reason I am looking at using Applescript is that it is possible to include applescripts inside a RealBasic application which means (I hope) that I should be able to action a script from an application button and use NWP as a report generator for my database. At present all my reports are created using x,y plot commands which means that making changes such as a new header or even multiple pages is a complete pain. Realbasic users have been asking for a better method of generating printed output for years but we are still stuck in the dark ages.

If the routine proves to be to slow I might try writing a block of Tab separated text to NWP then use a NWP macro to convert the text to a table in one hit. Or I could still use an intermediate file but have the database send an Applescript command to NWP instructing it to run an import macro. All I need is the time to play!

best wishes

Simon
User avatar
martin
Official Nisus Person
Posts: 5227
Joined: 2002-07-11 17:14:10
Location: San Diego, CA
Contact:

Re: Please Advise : Reports from a Database

Post by martin »

Ah, I understand Simon. You could also construct a string in AppleScript that includes many Nisus Writer macro commands and send them all at once. No doubt you'll figure something out that meets your needs- have fun playing!
Bob Stern
Posts: 170
Joined: 2006-03-12 12:32:47

Post by Bob Stern »

You can put as many commands as possible in a single Nisus Macro, then use a single AppleScript command to execute the Macro.
Post Reply