![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
There seems to be no way to output *data* from Base, is that right?
For my purposes at the moment, what would be perfectly acceptable
would be a csv file but I can't find a way to do that, is there
something?
Yes, I know I can produce a report, but you don't want to see what
that looks like when you force the output into a text file, and a
merged writer document will not produce raw data like this (will it?)
Again, have I missed the answer?
Mark
Open a blank Calc document.
Create a query. Run it.
In the results window, select all or some of the records with the mouse
button :
- ALL : click on the upper left hand grey corner cell of the query
result window ;
- some : using Ctrl-click to click on the grey cells leftmost of the
query result window
Drag and drop the selected record rows to your Calc sheet.
Save sheet as CSV
If you are using embedded hsqldb, it should be possible to use a
statement run from Tools > SQL to export directly the query result to
CSV - see the hsqldb documentation for that. Indeed, the preview window
in the Tools > SQL dialog will show a preview (CSV from what I recall)
of the results.
Alex
There seems to be no way to output *data* from Base, is that right?
I think there is.
For my purposes at the moment, what would be perfectly acceptable would be a csv file but I can't find a way to do that, is there something?
I'm no database expert (please read that again!) but how about:
o Click Tables in the left panel.
o Right-click a table in the Tables panel and select Copy from the context menu.
o Start a spreadsheet document.
o Paste into the spreadsheet.
o Save as CSV from the spreadsheet.
I trust this helps.
Brian Barker
Hello Mark,
Maybe I am missing something here and if I am suggesting something
that is "old hat" to you - I am sorry!
Besides producing reports the usual way from a database (which is
o.k. in SOME cases) I also keep extracting data into Calc-spreadsheets,
a very simple process indeed! Just mark all "records" in a table (eg. by
marking the left top corner in a table/view-display), copy them and
insert them into a "new" spreadsheet.
Regards
Heinrich
Mark Stanton schrieb
Thanks all.
I've been thinking about this.
Firstly, the cut'n'paste solution isn't working here for me on the
results (23 rows, perhaps ten columns) of a query in SQL view with
Fedora Linux (20) and LO 4.2.8.2 (please don't suggest I upgrade to
the latest as I'm using the OS repos, and there hasn't been any work
on Base in that time anyway)(has there?). It doesn't work in design
view either I now find.
To be clear selecting all the data in the result and pressing ^C (or
selecting "Copy" from the edit menu) then selecting a blank Calc
spreadsheet and pressing ^V (or selecting "Paste" from the edit menu)
doesn't produce any data whatsoever in the spreadsheet. I little
experimentation has shown me that selecting either a complete column
or a single cell in the result set, pressing ^C and then pressing ^V
in the spreadsheet *both* give me a single cell output to the
spreadsheet.
Something does seem to be copying into the spreadsheet. Trying to past
multiple cells like this clears the previous contents of the cell but
there is nothing to see. Saving the sheet, which looks empty, produces
a file of 10.2kb where saving an a truly empty sheet is 10.1kb.
Is there some setting I've miss-set?
<Stope Press> Pasting a small table (35 rows five cols) works,
although, strangely, it brings in an extra row for column names. Not
much use bringing in a table. That's what we need databases for in the
first place, to manipulate tables to produce results.
Having said all of that, is it ideal that the only way to get a data
output from Base is C'n'P?
Mark
No it isn't, not at all. Having used dBase, FoxPro, Paradox and Access and
others I can attest that all had multiple result export methods.
I would appreciate export in Base also. Unfortunately don't have the skill
to code it or the cash to fund development so am left with what is.
After a little bit of digging I did find that HSSQL does offer means to
export data. So it "may" be possible to use the SQL command line in Base to
perform your exports. I write "may" because not all commands I've run at
the SQL command line seem to be executed as I would expect.
And this method certainly isn't as trivial as an "Export to" option in the
UI would be. In any case perhaps this link can help you accomplish your
goal...
http://hsqldb.org/doc/2.0/util-guide/sqltool-chapt.html#sqltool_csv-sect
You must not copy any opened table or query. You copy and paste the _icon_ of
a query or table.
You can also link record sets to spreadsheet ranges and pivot tables which
gives quite a powerful "report engine".
[Tutorial] Using registered datasources in Calc
<https://forum.openoffice.org/en/forum/viewtopic.php?f=75&t=18511>
You do not tell us which database you are using (no, Base is _not_ a
database). If it happens to be an embedded HSQLDB (indicated on the status
bar) then you can use this command:
SELECT * INTO TEXT "export" FROM "Table or View"
This uses the HSQL database engine to export all records of some named table
or view into a text file named "export.csv" in my Linux home directory.
The full documentation of your database program can be found here:
http://www.hsqldb.org/doc/1.8/guide/ch09.html
Finally you can drag a record set from the data source window into a Writer
document and choose "Text" export. In that dialog you can concatenate the
wanted fields in one line with some delimiter between the fields. Then save
as plain text.
You do not select the cells from an open query.
Just select the query in the Query panel i.e the actual name of the query and copy that.
Then paste that into your spreadsheet and all your data should be imported.
So don't open the query just copy the query name.
Hope this makes sense.
Tony Bray
If you want individual records from your query result window, select the
grey cell at the lefthand end of the grid. Use Ctrl-click to select
non-contiguous results.
With the rows highlighted, maintain the left mouse button clicked and
drag to the Calc sheet. The drag icon will change to a dropped plus
symbol indicating that multiple records are selected. The field names
are also copied over.
Alex
Hmm
You must not copy any opened table or query. You copy and paste the _icon_ of
a query or table.
That's not what the three answers above said, nor indeed what is intuitive (to me),
and indeed cutting and pasting small amounts of data from the result set does
work, which I would expect/hope for (without the "small"), but I'll give it a go...
Wow! *That* works really well. I'd say that it requires a very odd intuitive
leap, and I don't remember seeing it written in the docs anywhere, did I just miss it?
You can also link record sets to spreadsheet ranges and pivot tables which
gives quite a powerful "report engine".
Am I right this is only "almost great"? I don't see how to define a datasource,
it just seems to be picking up data from the last icon I dragged to the sheet.
[Tutorial] Using registered datasources in Calc
<https://forum.openoffice.org/en/forum/viewtopic.php?f=75&t=18511>
That is very good, thank you. I must remember to look in the OpenOffice areas
more often.
You do not tell us which database you are using (no, Base is _not_ a
database).
With over thirty years of database coding and consulting I'm very well aware
that Base isn't a database. In the previous thread I said, MariaDb (under
Fedora).
If it happens to be an embedded HSQLDB
Yes, I saw this, but decided to ignore it. Given the trouble it causes, surely
it's not a good idea to recommend embedded HSQLDB for any purpose, is it?
Finally you can drag a record set from the data source window into a Writer
document and choose "Text" export. In that dialog you can concatenate the
wanted fields in one line with some delimiter between the fields. Then save
as plain text.
Wild! I don't see a text export though, other than saving the Writer file as
a text file.
Mark Stanton
One small step for mankind...
Mark Stanton wrote
Hmm
Wild! I don't see a text export though, other than saving the Writer file
as
a text file.
Well, yes. Saving in any other file format than ODF is an export indeed.
So we can use Calc, Writer and the underlying database engine to export
database data as plain text. Base is nothing more than a bridge between
office documents and some database engine. Base itself does not provide any
features other than storing connection data and providing some tabular
preview with basic sorting and filtering features. All the rest (forms and
reports) is provided by office documents.
when you put it that way.
Gave me hope for a moment it was my error. Unfortunately I must have made a
typo in my email. I went back with a corrected statement (below is cut and
paste from command window) and got the same error. 
select * into text "newfile" from "Sheet1";
1: syntax error, unexpected NAME, expecting ':' or '?' or '['
also tried...
select * into text 'newfile' from 'Sheet1';
select * into text 'newfile' from "Sheet1";
select * into text 'newfile' from Sheet1;
select * into text "newfile" from Sheet1;
select * into text "newfile" from "Sheet1";
select * into text "newfile" from 'Sheet1';
select * into text newfile from Sheet1;
select * into text newfile from 'Sheet1';
select * into text newfile from "Sheet1";
All above are cut and paste from command window. All produced the same
error.
If {into text newfile} is removed and the command
select * from Sheet1;
is run the command succeeds with Sheet1 and "Sheet1" but fails with
'Sheet1'.
Download this artbitrary database document with an embedded HSQLDB:
https://forum.openoffice.org/en/forum/download/file.php?id=22304
Run this:
SELECT * INTO TEXT "exportTable1" FROM "Table1"
which creates exportTable1.csv in the same directory where the document
lives.
...and it does work. So I tried again on my original database and a few
others.
It worked in my other embeded HSQL databases except for two exceptions...
#1 The database and table I had been trying with from the very beginning.
There it continues to fail. The only distinctive feature of that table is
it was created by importing an Excel spreadsheet.
#2 A table with a blob field failed if I used "select *". It did not fail
with that table if I listed fields and did not include the blob field in
the list.
So now I need to create another table by import from Excel. If that table
cannot be exported "into text" then I suspect the import from Excel is
creating some sort of problem that prevents subsequent "select * into text"
operations on the table. If I am able to "select * into text" then time to
go back to the original table and try it field by field to see if one or
several fields are preventing the export.
Anyway, this has been an interesting exercise.