Export to csv from OpenOffice Base using SQL

I have a Mas 90 install that I needed to export data out of. The server didn't have Excel, but it did have an ODBC mapping for Mas 90, so I threw OpenOffice on it quick and decided to try to get the data that way. At first glance, the lack of the normal File->Export functionality made me wonder if it could do it, but a few Google searches later I had the answer. I found it here.

The easiest way is to drag your database table into an open Calc file. This will copy the contents to the Calc file, and you can then do an export to csv from there. It is also the only way I found that would allow you to preserve the column headers. To do the export from Calc, simply open up your Base file, and open a new Calc file. Drag the table or query you want to export into the open Spreadsheet window. This will copy the data into Calc. Once that finishes, in Calc, go to File->Save As. Enter the filename to save your csv as, and also change the "Save as type" to "Text CSV" (or whatever other format you'd like). Then follow the prompts to set the delimiter and change any other configurable options. If you'd rather avoid copying data to a Calc spreadsheet and then copying it back out, there is another way too.

You can also use a SQL command to get the data out of your table in Base. You can do that by opening your database, going to Tools->SQL... From there, type the following into the "Command to execute" box, then clicking on Execute. If you get a lexical element error, be sure to read further down.

SELECT * INTO TEXT "output_csv_file_name" FROM "your_table_name"

Substitute the name you want the exported csv to have for "output_csv_file_name", and use the name of the table you're exporting in place of "your_table_name". Also, surround both with double-quotes, just like in the above example. Without them the command won't work. Also, after you've executed this command, the csv will be put in the same directory that your Base database is stored in. I'm not sure if you can change that, but I do know that putting a path in for the file name doesn't affect the location.

I also ran into an issue using this directly from my initial Base database. I had set it to open my ODBC database rather than create an actual Base database, but this kept giving me an error saying there was an unexpected lexical element, and listing FROM. I found that by copying the table I wanted to export to an actual Base database I had created, I could then use the SQL command within that database instead. This requires two .odb files, one linked to your ODBC source, and another standalone, blank Base database you created new. It's not the greatest workaround, but copying and pasting is simple enough, and if that's the only way to get the data out, then so be it.

In case you're wondering, I used OpenOffice.org 3.0. This command was new to me, and I'm going to guess you could use it to export any type of database table that supports SQL commands to a csv, not just OpenOffice databases. I know you can use 

SELECT * INTO table1 FROM table2 

to create copies of SQL tables (this would create a copy of table2 named table1), but I wasn't aware that you could export direct to a csv by adding TEXT to it. Good luck!