Re: [libreoffice-users] Re: And while I'm here, data extraction
On Thu, Feb 5, 2015 at 1:40 AM, CA Säger saege...@t-online.de wrote: 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. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: And while I'm here, data extraction
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. -- View this message in context: http://nabble.documentfoundation.org/And-while-I-m-here-data-extraction-tp4138570p4139044.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: And while I'm here, data extraction
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. -- View this message in context: http://nabble.documentfoundation.org/And-while-I-m-here-data-extraction-tp4138570p4138809.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: And while I'm here, data extraction
On Wed, Feb 4, 2015 at 6:20 PM, Andreas Säger saege...@t-online.de wrote:
Am 04.02.2015 um 01:26 schrieb Alan B:
select * into text newfile' from Sheet1;
Well, it's obvious. Isn't it?
:-) 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'.
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: And while I'm here, data extraction
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=75t=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. -- View this message in context: http://nabble.documentfoundation.org/And-while-I-m-here-data-extraction-tp4138570p4138689.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: And while I'm here, data extraction
Le 03/02/2015 21:49, tonybsa a écrit : You do not select the cells from an open query. 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 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: And while I'm here, data extraction
Le 02/02/2015 12:34, Mark Stanton a écrit : There seems to be no way to output *data* from Base, is that right? 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 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
