[Solved] How to export from OO Base as a .CSV?

[Swarup]

I work in Linux Ubuntu 7.04 (Feisty) and want to export a database from OO Base into Kexi. I am guessing that the best way to do it is to first export the DB from OO Base as a .CSV, and then import that .CSV into Kexi. I opened the main window of my DB in OO Base and selected File -> export. But the "export" option is grayed out. So how do I do the export?

[Swarup]

As I haven't gotten a reply, I am guessing that the mention of Linux and kexi may perhaps be making the matter unclear for people. So please ignore those points. Here is my question:

I want to export a database file from OO Base as a .CSV. But in File -> export, the "export" option is grayed out. So how can "export" be made an active option? Or is there any other way to do an export besides using this pathway?

[Villeroy]

Any type of database: Drag into Calc and save as text(*.csv)
Base document with embedded hsqldb: http://hsqldb.org/doc/guide/ch06.html

[Swarup]

In my computer, if I "drag" the table from Base to Calc, it doesn't come. But I tried copy and paste, and that worked. Thanks.

[Hagar Delest]

In my computer, if I "drag" the table from Base to Calc, it doesn't come.

Perhaps because you use the Ubuntu version of OOo. If you want to give OOo 2.4 a try (official Sun's version), see here: [Ubuntu] Installing OOo on Debian and Co.

[Swarup]

In my computer, if I "drag" the table from Base to Calc, it doesn't come.

Perhaps because you use the Ubuntu version of OOo. If you want to give OOo 2.4 a try (official Sun's version), see here: [Ubuntu] Installing OOo on Debian and Co.

Thanks--that was an interesting read. I'll surely try it.

By the way, may this update also fix certain other things that have really been a problem for me in OO Base. Such as, that, in any database when scrolling from left to right (or right to left) across columns, Base does not scroll smoothly. Rather, it haltingly moves along, stopping at every column (in larger DBs) and every few columns (in smaller DBs) to re-present the entire column. In Kexi as well as in MSA, this is not at all the case.

[Sliderule]

For anyone else reading this, there is another way ( technique ) to accomplish the task of 'exporting' from one, or more, HSQL table(s) to a .CSV file.

First, understand, within OpenOffice Base, you may execute an HSQL SQL statement directly. And, according to HSQL documentation that can be found here, http://www.hsqldb.org/doc/guide/ch09.ht ... ct-section ,

SELECT [{LIMIT <offset> <limit> | TOP <limit>}[1]][ALL | DISTINCT]
{ selectExpression | table.* | * } [, ...]
[INTO [CACHED | TEMP | TEXT][1] newTable]
FROM tableList
[WHERE Expression]
[GROUP BY Expression [, ...]]
[HAVING Expression]
[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } |
INTERSECT [DISTINCT] } selectStatement]
[ORDER BY orderExpression [, ...]]
[LIMIT <limit> [OFFSET <offset>]];

Therefore, by using the INTO TEXT clause, you may accomplish this.

To start this ( and, this can also be accomplished by a macro if you so desire ) . . . the steps are:

1. Open your database
2. From the menu: Tools -> SQL...
3. under Command to execute, enter your SQL
4. an example might be ( change output_csv_file_name to your desired OUTPUT name, and, your_table_name to the HSQL table you are using )

   Code: Select all

   SELECT * INTO TEXT "output_csv_file_name" FROM "your_table_name"

5. Press the Execute button
6. Press the Close button

The bottom line . . . this will create a TEXT CSV file for you . . . located in the same directory where your database ODB file is located. The file will have a file type of CSV .

Additionally, your database will have a NEW TEXT 'table' as defined by the name you gave it in the SELECT above. This 'database' file may be deleted.

Also, you can include a WHERE clause above if you wish to only output a part of the table to your CSV file. And, an ORDER BY clause will further define the CSV output.

I hope this helps.

Sliderule

[Villeroy]

Thank you, Sliderule for working this out. I referred to the wrong chapter in the hsql documentation.

[horst.graben]

I too have been having problems "exporting" Open Office Base (2.4) to CSV files.

When I follow the Base instructions to "copy" the table and "paste" it into a new Calc file, my data is indeed copied into Calc, but each data field (i.e., column) occupies 3 or 2 or 1 calc columns (it varies). And, the spreadsheet text is formatted strangely - all characters have strikeout! When I save this monstronsity to CSV format, I see that each field has several tab delimiters. I can't merge these delimiters because my data set has some blank fields.

Sliderule's SQL solution works like a charm. The only disadvantage is that the field names are not written in the first row.

I wonder if the SQL command could be added to a form as a "button" to automatically export a table? (save me the trouble of remembering the SQL code each time!)

Thank you all!


By the way: The data table was created using Sun OO (not the Ubuntu package) under Ubunty Hardy. The bizarre "copy and paste" exports are happening under both Linux and Windows OO's.

[Villeroy]

Drag into Calc or Writer: You can link by dragging a query or table from the datasource window's (key F4) left pane into the document. The database needs to be "registered" for a refreshable link (Tools>Options...Base>Databases)
You can copy any grid view of a query or table by dragging the empty grey box on top of the grey record selectors and left of the field headers.

[horst.graben]

A belated thank you for this very handy advice! And, yes, I do desire to implement a macro in order to easily export tables to CSV files.

But, I have no experience with macros, office Basic programming, or SQL Can you please give me a tip on where to start?

This feeble attempt at a BASIC macro failed with the "Basic syntax error: expected Case"

REM ***** BASIC *****
Sub Main
SELECT * INTO TEXT "test.csv" FROM "Table1"
End Sub

To start this ( and, this can also be accomplished by a macro if you so desire ) . . . the steps are:

1. Open your database
2. From the menu: Tools -> SQL...
3. under Command to execute, enter your SQL
4. an example might be ( change output_csv_file_name to your desired OUTPUT name, and, your_table_name to the HSQL table you are using )

Code: Select all Expand viewCollapse view
SELECT * INTO TEXT "output_csv_file_name" FROM "your_table_name"

5. Press the Execute button
6. Press the Close button

[megabandwidth]

I am trying to export CSV file from OOBase 3 and my table has over 3000000 records so it will not copy to OOcalc to many records. and i tried the SQL above and was given an error NAME ':' '?' expected. Is their a current work around that works with OOffice 3.1.1 I have been looking for hours and have found nothing that works.

[RPG]

Hello

Did you use the SQL tool?

select first the tables and then:
menu --> tools --> SQL

Romke

[pedromiravaz]

Hi,

Any way to use

Code: Select all

SELECT * INTO TEXT "output_csv_file_name" FROM "your_table_name"

and export column names on the first row?

Thanks a lot.

[Villeroy]

and export column names on the first row?

You may ask this on the HSQLDB mailing list. It is really unrelated to the office suite and http://hsqldb.org/doc/guide/ch06.html does not reveal such option.