Export from Base query to text/csv file?

Creating tables and queries

Export from Base query to text/csv file?

Postby eepofthegra » Mon Oct 12, 2009 4:45 am

Can't find an option for exporting from a Query directly to a text/csv file.

Do I first need to copy the query results to a Calc file?

Hoping for a direct path...thanks.
OpenOffice 3.1.1 on WindowsXP
eepofthegra
 
Posts: 2
Joined: Mon Oct 12, 2009 4:41 am

Re: Export from Base query to text/csv file?

Postby Villeroy » Mon Oct 12, 2009 10:35 am

Register your database, drag your query from the beamer's left pane into the sheet (F4, drag&drop).
Edit the resulting import range (Data>Define...[More...]) and set all the additional properties.
Apply the number formats you want to see in the csv.
Save as ods.
Save as csv with default settings (comma separated, displayed as shown) and close.

Next time after your database changed:
- Open the spreadsheet, confirm to refresh the import range (we stored only the link, not the data).
- Save as csv with default settings.

[Tutorial] Using registered datasources in Calc

##########
HSQLDB knows editable text tables which can be populated from a view rather than query.
http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=23260
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17319
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Export from Base query to text/csv file?

Postby eepofthegra » Mon Oct 12, 2009 5:33 pm

It just had to be that easy didn't it. ha ha ha.

Thank you. Danke!
OpenOffice 3.1.1 on WindowsXP
eepofthegra
 
Posts: 2
Joined: Mon Oct 12, 2009 4:41 am

Re: Export from Base query to text/csv file?

Postby Sliderule » Mon Oct 12, 2009 5:38 pm

eepofthegra:

Assuming your database is from HSQL, the 'embedded' database, another option is a macro.

The link below, please see the LAST post by Sliderule ( yours truly ) includes a macro where you can include via passed parameters, OR, prompts:

  1. Table name, View name, OR Query name ( including queries with prompts )
  2. Output file name
  3. Header-Yes OR Header-No ( column names to be included in output file )
  4. Registered file name
If you use the macro, please be sure to change, at the beginning, YOUR DEFAULTS.

This macro can be run:

  1. From Calc ( with output to a file )
  2. From another macro
  3. From a form
  4. From the toolbar - Macro -> Run

http://www.oooforum.org/forum/viewtopic.phtml?t=86880

I hope this helps, please be sure to let me / us know. :super:

Sliderule

Thanks to add [Solved] in your first post Subject ( edit button top right ) if your issue has been fixed / resolved.
User avatar
Sliderule
 
Posts: 660
Joined: Thu Nov 29, 2007 9:46 am

Re: Export from Base query to text/csv file?

Postby Villeroy » Mon Oct 12, 2009 7:09 pm

eepofthegra wrote:It just had to be that easy didn't it. ha ha ha.

Thank you. Danke!

Indeed, the spreadsheet formatting is an easy way to specify all the details of your prefered ouput format once.
And the best thing: It works with any input table, even with other spreadsheets when you want to configure a certain output format your csv.

All you have to do then is opening a your prepared spreadsheet, confirm the refresh and save as csv with default options.

HSQL's built-in text table support is just another way to configure the output options once. Then you can drag your view onto the linked text table in order to write the file with the configured text option.
How to use the HSQLDB: [Solved] SQL: from OOo Base to tab-delimited text
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17319
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 6 guests