AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for Open Office database tutorials.   > > > > >   MACROS section, Open Office tutorials.

Open Office Base (database) Tutorials
Exporting CSV with a macro

You may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.

Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.

There's more about ooBase in the main index to this material.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software ®, 2/06-5/09.



Introduction

OpenOffice is a wonderful software suite. There are many, many things that it does extremely well. But there comes a time when you "really need" something that Open Office has not provided a button for.

Fear not! All is not lost! Macros may be the way to meet your need.

Although this page talks specifically about exporting to CSV from an ooBase database, it is mostly about general issues, issues which will arise in any work accessing a database by a macro. If you are an Open Office macro beginner, you may be better served by my "Put a Button on a Document" tutorial. It covers many of the points covered here, but doesn't get drawn into the database issues. If you want to know more about what CSV is and why you would ever care, there's also an introduction to CSV essay for you.

If as you proceed with the page you are reading now, it all becomes a little overwhelming, consider switching to the page in which I give another example of creating and activating a macro. It has a link back to where you are now, which may be more accessible after you've worked through the other example.

To do this tutorial, you need a small database named 'FDB6Navy' with a table called "Personel" (yes... that is a spelling mistake, but changing everything... database, macros, tutorial to "personnel" was more than I could face!), and it needs fields called "Name" and "Rank". The document you are going to create with ooWriter should be saved in the same folder as the database, and the datafile holding the exported CSV data will arise in that folder too.

You can either set that up for yourself now, or download the one I made. In either case, put it in it's own folder, and put the rest of what we're doing here in that folder too.

If you download the one I made, then you will need to register it. (I haven't zipped the file or anything, it is "ready to use".) After you have downloaded it and moved it to a suitable folder, open it, and use Tools | Options | OpenOffice.orgBase | Databases to get to the "Registered Databases" page, where you will register the database file as database "FDB6Navy".

(When I say the database must be "registered", I am talking about the sort of registration that ooBase offers when you first create the database. It is only "registered" to your local Open Office environment, not with anyone outside of that. Humm.. during an edit session I saw this and wondered... do you really need to register the database? It is simple to do, so do it just in case!) )

This tutorial was written using ooBase version 3.0 on Windows XP, but many things should work the same way under other post version 1 OpenOffice installations, and under other operating systems. There are some strong reasons to move to Open Office 3.1 if you want to use macros with ooBase. Where the difference is important, you will be told.



The macro....

Here's the code of the macro we need. Don't worry: It's not as bad as it looks, and I will be taking you though it....

REM  *****  BASIC  *****
REM Adapted from...
REM http://www.oooforum.org/forum/viewtopic.phtml?t=72522

REM WEIRD: "test09b2.csv" keeps appearing and disappearing. ONCE was creating a file of that name.

REM Also weird: Table VIEWS (see tables list) appearing.

REM ** Something needs doing to release the .csv file after it is created. At present
REM only closing the document achieves this. (Until doc is closed, .csv file cannot be deleted.

Sub ExportCSV

   Dim sPassword, sPDSName, sUser, sErrorMsg, sOutput_File_Name, sView_Name, sDrop_CSV_SQL As String
   Dim oStatement, oDBSource, oConnection As Object

   sPDSName = "FDB6Navy"   '{{ registered datasource...
   'if you use this macro for your own needs, change to
   'the name of YOUR database

   sOutput_File_Name = "TmpCSVexport"  '{{ Name for the
   'file the CSV data will be sent to. Any file of this
   'name will be overwritten by the macro. Do not use
   'hyphens in the file's name

   sTable_Name = "Personel" '{{ name of table to export
   'data from. Remember the name is case sensitive

REM... next few lines are scraps of original. Instructive,
'but neither tested nor necessary to the tutorial
'from http://sheepdogguides.com/fdb/fdb6exportcsv.htm
   ' SQL to include a FIXED FORMAT output
   'sQuery = "SELECT " &_
   '            "CONCAT(""My_Field_01"", SPACE( 30 - LENGTH( ""My_Field_01"" ) ) ) AS ""My_Field_01"", " &_
   '            "CONCAT(""My_Field_02"", SPACE( 30 - LENGTH( ""My_Field_02"" ) ) ) AS ""My_Field_02"", " &_
   '            "CONCAT(""My_Field_03"", SPACE( 30 - LENGTH( ""My_Field_03"" ) ) ) AS ""My_Field_03"" " &_
   '         "INTO TEXT " + sOutput_File_Name + " " &_
   '         "FROM " + sTable_Name

REM TKB CRUDE VERSION....
REM *** N.B. *** without the extra "'s around the table name, table not seen. Dbl "'s inside "'s to generate " in string.
REM (semi-colon doesn't seem to make any difference)
sQuery = "SELECT * INTO TEXT "+sOutput_File_Name+" FROM """+sTable_Name+""";"

   REM... not tested... yet!
   ' msgbox (sQuery, 16, "ErrorHandler")  '{{Display 'created SQL if desired

   'Now, if a FILE with the given name already exists
   'DROP ( delete ) it
   sDrop_CSV_SQL = "DROP TABLE " + sOutput_File_Name + " IF EXISTS;"

   ' msgbox (sDrop_CSV_SQL, 16, "ErrorHandler")    '<<  Display created SQL if desired

   oDatabaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )

   oDBSource = oDatabaseContext.GetByName(sPDSName)

   oConnection = oDBSource.GetConnection("", "")
   oStatement = oConnection.createStatement()

   oStatement.executeUpdate( sDrop_CSV_SQL )   '<< Drop FILE CSV if exists before recreating

   oStatement.executeQuery( sQuery )   '<< Create the Fixed Format CSV file

   MsgBox "Data has been exported."    '<<   Let user know
   'the process is complete

End Sub

Where to put it....

What I am going to do here has pros and cons, but I'll spare you those details for now. Just don't worry if something conflicts with other things you know.

We're going to store the macro outside of our main Open Office installation. Before Open Office 3.1, that could not be in the database file itself, the .odb file. Happily, from Open Office 3.1 you can store macros in the .odb file. If you want to do macros with ooBase, you should seriously consider upgrading your Open Office installation to version 3.1. For those of you who don't want to do that, I present an alternative that works for version 3.0, at least, and maybe for earlier versions.

For pre version 3.1 macros with ooBase: Create an ordinary ooWrite "page", i.e. New Text Document. Put "My database macro" on the top of it, and save the document in the same folder as your database. Call it "FDB6ExpMacros". ("FDB" for "Free Data Base", "6" for section 6 of my tutorials... the section about macros. "Exp" for "export", and "Macros" because this ooWriter page is going to hold the macro we're writing to access the data in the database.) Any time you want the macros to work, the ooWriter document must be open.

For macros with ooBase after Open Office version 3.0: The macros can reside in the .odb file where the rest of your database is.

Go to the menu bar of the window of whatever is going to hold your macros. Click Tools | Macros | Organize Macros | OpenOffice.org Basic

On the left is a window called "Macro from" which lists several places macros can be.

If you are working with a pre version 3.1 Open Office: Your "macro container" will be the ooWriter document we talked about. It will be listed as "FDB6ExpMacros" if you saved it with the name I told you to use.

If you are working with a post version 3.0 Open Office: Your "macro container" will be the .odb file containing the rest of your database. It will be listed as "FDB6Navy.odb" if you saved it with the name I told you to use.

Click on the "+" in front of the macro container. That should reveal a folder called "Standard".

Now click the "New" button, to create a new macro container, and name it "FDB6Macros". (Although we are only going to create one macro, what we are creating here is a container which could hold several macros.)

You should get the following skeleton....

REM  *****  BASIC  *****

Sub Main

End Sub

Use copy/paste to replace everything... from and including "REM..." down to and including "...End Sub", using the material presented earlier. (You will be removing the skeletal "Sub Main", but that's okay... you don't need a "Sub Main")

In the macro's window, use File | Save to save what you have, before anything goes wrong. You won't be asked for a file name, because you will only be re-saving the macro containing document, even though you are doing it from the macro editor's window.

Don't have too high expectations yet. Things may not (yet) work as you may be assuming they will! Just hang in there, follow this through carefully.

Get your windows arranged so that you can see the macro, and the form you created earlier. Of that, you only need to be able to see your button.

Just below the menu in the macro's window, there's a green triangle icon, tooltip "Run BASIC". Click it. You should get the "Data has been exported." message.

Check that it actually was exported by opening the file with a text editor. (Textpad, from , is very good, by the way.) Depending on how your computer is set up, the CSV export may open in ooCalc if you open the file by double clicking on it's name in a Windows Explorer window. If it does, don't worry.

There's a bug in my code, by the way: You won't be able to delete the file of exported CSV until you close whatever the macro is stored in. ("You'll get an "in use by other program" message.) If anyone knows how to fix this, I'd be delighted to hear from you. However, I think that the code was, at least at one point, taking care of removing any pre-existing copy of the CSV export for you, at least. (Be careful... you many not WANT an automatic, without warning, over-write in every case.)

At the moment, if the old CSV export file still exists, pressing the button again seems to result in the CSV file merely growing, a new copy of the data in the table is APPENDED to the previous export. Perhaps the "Drop" statement isn't working. Sorry. (All of this in an Open Office 3.0 environment. Things may be working better if you've upgraded (as you probably should) to version 3.1.)

Whew! Not done yet, even with our "imperfect" "solution", but at least we're a long way down the road.

Re-save the macro, just to be on the safe side.


But it should happen automatically!!!....

... and it will!

Use the menu of your ooWriter document to check View | Toolbars. Make sure that the form design toolbar is switched on.

FormDesign bar

The "Design Mode On/ Off" button on this toolbar is just a duplicate of the one you were using earlier; it isn't different. Make sure your form is in design mode, so that the icons are colored.

There's a "FORM navigator" icon. It is a window with a tiny compass. Fifth icon on the OO 3.1 toolbar. (Don't confuse it with the more general "Navigator" (just a compass) icon you may be familiar with.)

Click the Form Navigator icon, and a Form Navigator window should come up, with the elements of your form shown.

The default button name and label will be fine. ("PushButton" and "Button").

Get yourself to where you are looking at your button's Events... "Before commencing...", etc.

Click on the button to the right of the "Mouse button released" edit box. It is square with "..." in it.

In the "Assign Action" window that ensues, click on the "Macro" button below the "Assign:" label, over at the upper right.

Recognize the things in the "Library" panel? They are what you saw earlier when you created your macro. Click the "+" by your macro container, and then the "+" by the "Standard" which then appears, and your should see "FDB6Macros" in the "Library" panel. Click on it, and "ExportCSV" should appear in the "Macro name" panel. Both "FDB6Macros" and "ExportCSV" should be displayed as selected. Click "OK" to say that you want to assign that macro to the Button Released event. Click "Okay" to close the Assign Action window

Save everything. (I have to admit: Mine went a bit "nuts" around now... but eventually, without changing anything I've told you, things settled down. It just took a few cycles of closing the document, re-opening it, re-doing what I thought I'd done....)

Leave the "Design" mode, and.... I hope!!!.... your button works; i.e. each time you click the button, the table's data is exported in CSV form.


You need to notice....

In what we've done, there are a number of things you need to be aware of. Some other important things were covered in more detail in the essays leading up to this one.

*** There are places where I've chosen a name for something, and places where I allowed OpenOffice's choice to stand.

*** The language is case sensitive. Change "Rank" to "rank" and the macro stops working if the table is still called "Rank" in the database.

*** Be grateful for the "syntax highlighting" that the macro editor provides. The colors help you see what the computer will make of something.

*** Objects and Methods

You are working in an object oriented environment. There is much more about this in the page I wrote introducing OOP and events. The Wikipedia article is good, too.

Would you like me to go through the macro's code, explain it more or less line by line? If so, read on. If not you can skip over the analysis.

The first few lines created up some variables. Some of them are not used for the "export to CSV" work. They are scraps left over from a bigger example which was adapted for the tutorial you are reading.

Dim sPassword, sPDSName, sUser, sErrorMsg,
    sOutput_File_Name, sView_Name,
    sDrop_CSV_SQL As String

   Dim oStatement, oDBSource, oConnection As Object

Then we filled some variables with things we wanted to be able to refer to....

sPDSName = "FDB6Navy"   '{{ registered datasource...
   'if you use this macro for your own needs, change to
   'the name of YOUR database

sOutput_File_Name = "TmpCSVexport"  '{{ Name for the
   'file the CSV data will be sent to. Any file of this
   'name will be overwritten by the macro. Do not use
   'hyphens in the file's name

sTable_Name = "Personel" '{{ name of table to export
   'data from. Remember the name is case sensitive

We refer to two of these only once. Why not just "plug" the names into our code directly? Using variables the way we did it here is a safer way of incorporating things in a program. It becomes especially helpful if at some point we derive other programs from the original. I almost said "We refer to all of these things only once." But then I realized that we refer to TmpCSVexport in two places... where we (tried to) delete any pre-existing copy, and in the place where we specified the destination for the CSV data we were exporting. Using a variable is safest because....

A detail: notice that we don't need to specify the extension for the file we are sending the CSV data to. The computer will add ".csv" to the file name we've given it.

Next came the generation of the line of code at the heart of the job. It was put in a variable for tidiness.

sQuery = "SELECT * INTO TEXT "+sOutput_File_Name+" FROM """+sTable_Name+""";"

(That boils down to...)

SELECT * INTO TEXT TmpCSVexport FROM "Personel";

.... when the variables hold what they do in our code. Note the quote marks around the table name. To build the string in sQuery with quotes in it requires lots of quote marks, as you can see from the code. Some delineate bits of text which are part of the final sQuery, and some (in pairs) say "put a quote mark here in the result".

The line....

sDrop_CSV_SQL = "DROP TABLE " + sOutput_File_Name + " IF EXISTS;"

....is part of what is supposed to take care of removing any pre-existing TmpCSVexport file. As mentioned, it wasn't working quite right at the time I was testing the material in this tutorial, on an OO 3.0 machine.

Everything so far was by way of preparation. Next, after just a little more "preparation" we moved into the heart of the code.

oDatabaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )

oDBSource = oDatabaseContext.GetByName(sPDSName)

oConnection = oDBSource.GetConnection("", "")

oStatement = oConnection.createStatement()

This code "connected" us to the database, and created an object (stored in oStatement) which we then used to do things with or to the database.

In the following, we were saying "Use the executeUpdate method of the instance of the object stored in the variable oStatement to do the SQL command stored in the variable sDrop_CSV_SQL

oStatement.executeUpdate( sDrop_CSV_SQL )

And then we used a very similar statement to execute the line of SQL which does the export operation....

oStatement.executeQuery( sQuery )

WHEW! Who would have thought that getting the computer "just" to do
SELECT * INTO TEXT TmpCSVexport FROM "Personel";
could be so complicated!!

Ah well. Complicated or not, we've Done It!!

Well done you, not skipping over the code analysis. Now we re-join the wimps who skipped ahead. Other things you should try to notice....


*** Properties

The properties of objects are half of the story of achieving our goals.

*** Events

In another essay, I said that methods are half of OOP and properties are the other half. That's true. But to work with macros, you have to master two things: OOP and events.

Happily, events are easier to master than OOP, and what I needed to say about them was put into the other essays.

One last thing to think about: the hierarchy of objects. Elsewhere, I used a dog as an example of an object. I used the fact that dog's are made up of constituent parts to illustrate the idea of hierarchies of objects, for example a dog has a leg, it has a paw, it has claws. Just as properties (e.g. color) can apply at different levels, and be inherited downward, so to can event handlers. An "event" that the dog "OS" might be set up to handle is "poke", i.e. the dog might well have a response to being poked. Now, you might have a high level "poke" handler, and dog.poke would get a certain response. And that response could be the same, whether you poked the dog or poked his leg. However, the dog (and your Open Office document) can have different handlers at different level. While dog.poke and leg.poke might elicit the same response, eye.poke might have its own, special handler, the dog might respond differently if poked there.

I hope you've already inferred what I'm about to say, but just to be explicit: We "told" the operating system to use our ExportCSV macro via the events tab of the button's "properties". (That window really ought to be called "Properties and Event Handlers"!) (Also: Don't worry about all the properties and events you see... you only need to understand a handful of them to do lots and lots of "good stuff".)

Well.... I'm exhausted, and I suspect you are too. This started so innocently. And then, as computing things so often do, it took on a life of its own. Thank you for reading. I hope your effort will reap commensurate rewards. If you can just get past the "getting started" stuff, there is so much fun to be had!


Further Reading....

Guide to ooBasic.... the language of your macros...

An OpenOffice.org page

Guide to OO API.... the "things" your macros act on, e.g. getByName...

An OpenOffice.org page

====================================

Supplying macros as add-on...

An oooForum.org discussion thread

Enumerating....

An oooForum.org discussion thread

Documentation...

An OpenOffice.org page

A good set of links to help on macros....

Pitonyak's page

Sample macros...

oooMacros.org

A discussion of exporting an ooCalc doc to CSV...

oooForum.org discussion

Editorial Philosophy

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.




Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .