[Solved] Simple dumb? Can't use SaveAll...now! Export to CSV

Creating tables and queries
Post Reply
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

[Solved] Simple dumb? Can't use SaveAll...now! Export to CSV

Post by MSPhobe »

(Solutions (below) are to "how do I export the results of a query to a comma separated text file?" I have not (yet!) solved the "Save all" mystery.)

=== (Original post began...)
I created a little scratch table. Nothing fancy. Had a primary key, yes.

Put some data in it.

I used wizard to create a query, to report all fields, all records, defaults across the board.

Fifteen minutes ago, I could have sworn that I used, in the query result's window, "File/ Save All" to export the records shown to a .csv file.

SHOULD THIS BE POSSIBLE? Maybe I mis-remembered? So... do I need to find out why something that SHOULD work DOESN'T, or is it a case that I probably didn't do what I THINK I did??

Now when I try it... "Save All" is grayed out. Any ideas what would cause this???

===
(Or, if there's a better way to get the contents of a table into a .csv, do please tell!)

Sorry... something dumb I expect... but I can't find it!
Last edited by floris v on Tue Nov 19, 2019 7:12 pm, edited 4 times in total.
Reason: Edited Solved tag, floris v, moderator
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Simple dumb? Can't use SaveAll...now!< revised

Post by UnklDonald418 »

In my experience Calc seems to do a better job of exporting tables.
From the result screen of the Base query select all the rows and drag them onto a blank Calc sheet.
Then from the Calc sheet use SaveAs to export the data.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Simple dumb? Can't use SaveAll...now!< revised

Post by Villeroy »

Until today I did not even notice that there is a "Save all" item in the menue. Export to what? Text? Create a text table and a view. Drag the view onto the text table.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: Simple dumb? Can't use SaveAll...now!< revised

Post by MSPhobe »

Yes!... Export to text. (I need to process the contents of some records with an external program.)

Not sure what you mean by "create text table". And haven't used "views", though recall hearing of them, at least!

Thanks...
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Simple dumb? Can't use SaveAll...now!< revised

Post by UnklDonald418 »

A View is a hybrid creature. It acts like a table, but it's data source is a query.
Copy the SQL for your query into the clipboard, then on the left side of your Base window click on Tables.
Select Create View and you will get what looks like a query dialog.
Switch to the Design View and paste the contents of the clipboard into the SQL pane.
Run the query to verify that it displays what you intend.
Save the View.
Drag the View onto a Writer page, (or Base form) and you will see a dialog that allows the choice of display formats.
If you select Table, the data will be displayed on a text table.
Or you could select Text and add the fields separated by commas to get a csv text list.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Simple dumb? Can't use SaveAll...now!< revised

Post by MrProgrammer »

MSPhobe wrote:I used wizard to create a query, to report all fields, all records, defaults across the board. Fifteen minutes ago, I could have sworn that I used, in the query result's window, "File/ Save All" to export the records shown to a .csv file.
Open the query in Base. Edit → Copy. Open a new spreadsheet. Edit → Paste. File → Save As → File type → (scroll way down to find) Text CSV.

Or open a new spreadsheet. View → Data Sources. Select your query. Select all the records (square to left of field names). (from the toolbar) Data to Text. File → Save As → File type → (scroll way down to find) Text CSV.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: Simple dumb? Can't use SaveAll...now!< revised

Post by MSPhobe »

Thank you everyone. You have saved my project! (I am porting an old Paradox database over to OpenOffice. A database I actually NEED... and I've been nervous for some time about how much longer I'm going to be able to get Paradox to work, as Mr Gates' monster continually "improves" Windows.)

While I still long for the NICE, SIMPLE "export to .csv" that Paradox (and MySQL! (Yes. I know that Base can be used for a MySQL front end!) offer, the work-arounds suggested by the posters above WORK! (And I managed to get past all the "gotchas" that I manage to find, when trying to follow instructions.)

All that follows is just my "blow by blow" exposition of what they suggested, filled out with guidance for the next person on where I went wrong trying to implement the suggestions.

(I'd still like to know what "Save All" SHOULD do, or if my memory about what I think it did for me (once!) must be faulty.)

All of the following done on a Windows 7 machine, using the native database engine of OO, ver 4.1.1, 13 Mar 17

====
UnklDonald418's answer, massaged...

Create query
In ooBase main project manager window, select (left pane) Queries.
Right click on query's name, lower right pane, select "edit in SQL View".
Select all the text, ctrl-c to copy the selected text.
Close the editing session.

In ooBase main project manager window, select (left pane) Tables.
In upper right pane, click on Create View. You will get what looks like a query dialog.

The way you are viewing your view(!) could be in either of two states. If you are seeing rows and columns, (Row labels at left: "Field", "Alias", etc; columns: One for each field in table), then you are viewing your View in "Design View". Switch that OFF ("View/ Switch Design View On-off", or via button on toolbar). That should leave you with a simple pane into which you can type anything.

Do ctrl-v (pr paste by any other means), to put what you copied earlier here. It will look a bit like...

SELECT "date" AS "date", "tick" AS "tick", "quant" AS "quant", "cost" AS "cost", "frm" AS "frm", "to" AS "to", "pool" AS "pool" FROM "xactions

...(If, of course, your table is like mine was! Name: xactions. Fields: date, tick, quant, etc.)

Save your VIEW. You won't be able to tell Views from Queries within Base after this, if there is a significant difference in the first place, unless you put something in the name you chose for the view. (Don't be alarmed that the prompts are speaking of a "Query".)

You can, at this stage, run the query to verify that it displays what you intend.

Close the "Query" design window. In which you have designed your View!

Open a Writer document, and arrange your windows so that you can see that, and see you ooBase main project manager window, which should be showing your queries and views.

From the lower right pane of the ooBase main project manager window, drag your view onto the Writer page.

A dialog opens.

First, make your wish known: You want to "Insert data as" TEXT. (Radio buttons across top of dialog.)

For each field from the table that you want in your .csv, double click on the name (in "Database columns" column), and it will be added to the panel to the left of the list of the database column names.

You can now "do things" to the text in the panel you've just populated. In my case, at this point, it looked like...

<cost> <date> <frm> <pool> <quant> <tick> <to>

... which would have produced text, by not comma separated, for one thing. Nor would text fields be delimited with quotation marks. And the fields would have been in the wrong order for my wants. All of that easily fixable. What I needed to get what I wanted was....

<date>,"<tick>","<quant>",<cost>,"<frm>","<to>","<pool>"

... which I created "by hand" out of the "rough draft" that the Base dialog had helped me start. Created in the panel on the dialog.

(I then copied the result to a text file, and saved it for re-use later. You don't HAVE to fill the panel to the right of the one with field ("database column") names by the dialog's mechanism.)

WHEW!

Dialog filled in. Click OK.

Your Writer doc should fill with lines of data, one record from table to each line... with whatever commas, quotation marks, etc, you specified.

Then, final stage, you can either....

---------
OPTION A:

(Note to self... this won't work for ST2XTAB's wants.)

Still in Writer: SaveAs. Change type to Text.
(Navigate to right folder, assign name)
Click "Save" button.
You will be asked "This doc may contain... " with two choices... "Keep current format" or "Save in ODF" format. You want "Keep Current Format".

You MAY (unlikely) get "Error saving... Access denied." (I will probably get it, hence this note!) This can happen if you have an earlier document, in the folder you are trying to save to, using the same name as you are trying to use, is currently open in another application. Close that, and try again! (In my use, if ST2XTAB is still open, havng accessed TmpGL.txt, that will cause the error.)

---------
OPTION B... longwinded, but "safe"...

In some obscure instances, The way Writer ends a "saved as .txt" file's lines and files differs from the way others do it. In my case, the difference mattered (for ST2XTab2). (Note to self: ALSO: For ST2XTAB, the last record needs to have at least 7 commas added to it. Too many does not hurt.)

An alternative way to save the text AS TEXT...

In the Writer doc, ctrl-A to select all of the text. Close Writer doc, if you want to save the text from the text editor (next step) under the same name.

Open the text editor you like. Open a new doc. Ctrl-V. Save.

TA DA!!! Data now safely in .csv file!!! (Using extension ".txt", if you used Option A.)

============
ALTERNATIVES....

----------
Post by UnklDonald418 » Sun Mar 12, 2017

In my experience Calc seems to do a better job of exporting tables.

From the result screen of the Base query select all the rows and drag them onto a blank Calc sheet.

Then from the Calc sheet use SaveAs to export the data.

** N B **

To select all rows of the query, click the empty box to left of column headings, the "cell" above the column of empty boxes which start each row of the query result. The MOST upper left "cell" of the whole query table, immediately above and left of first record's first field.

And also note: Click there, to select all rows, all columns, then IMMEDIATELY either do ctrl-c, or right-click/copy. (If you click somewhere else first, although all records remain selected, "copy" does not copy everything.

(I haven't checked, in detail, the rest of this solution... I believe it works, though. Be sure during the SaveAs (of the Calc sheet) you tick the "Edit filter settings" box, if you want to be able to put quotes around text fields, etc. (Note to self: ST2XTAB needs the quotes))

-----------
Post by MrProgrammer » Mon Mar 13, 2017

Option 1:
Open the query in Base. Edit > Copy. Open a new spreadsheet. Edit > Paste. File > Save As > File type > (scroll way down to find) Text CSV.

(MSPhobe comment: Yes! Works! (Pretty sure... haven't done exhaustive test, apart from work you see elsewhere here.)

--
Option 2:
Open a new spreadsheet. View > Data Sources. Select your query. Select all the records (square to left of field names). (from the toolbar) Data to Text....

(MSPhobe comment: Yes! Works! ... and Very Neat!!! I like this! First time I've seen a USE for "data sources") (Works IF you have "registered" the database with Base, which is the default result when you create a new database.)

The suggestion from MrProgrammer went on to say...

Then: File > Save As > File type > (scroll way down to find) Text CSV.

(MSPhobe comment: Works! Pretty sure... haven't done exhaustive test, apart from work you see elsewhere here.)
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED] Simple dumb? Can't use SaveAll...now! Export to

Post by Villeroy »

The easiest and most flexible way to get data in and out is a text table plus one or more views. Like all the good stuff, it is more work to set it up properly. But once you got the gear wheels fitting to each other, you just have to turn the crank (or trigger a most simple macro).
Text tables and views are not office features. They are implemented in the underlying database engine. Base can show text tables and views among the other tables. Text tables are database tables linked to a text file. They are well described in this forums tutorial section. If your imported/exported text has the same structure like one of your regular tables you can write to the text file by dragging the table icon over the text table icon and vice versa.
However, In most real world cases you need some SELECT statement to adjust different table structures. Views are SELECT statements, therefore they are almost the same as queries. Unlike queries, wich are stored in the office document and visible by the office suite only, views are stored in the database and accessible by the database eingine. This way you can define queries, save them as views and then do something like INSERT INTO "Some Table" (SELECT * FROM "My View"). When you try this with a query name, the database will raise an error that it does not know any object with that name.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED] Simple dumb? Can't use SaveAll...now! Export to

Post by Villeroy »

Found an example with embedded HSQL: download/file.php?id=21951
The 2 views return Date, Person Name, Category Name and Value merged from 3 tables.
The text table named "Export" has the same field types in same order.
Instead of drag&drop you better copy a view icon, select "Export" and paste. This way you get the right default options in the the following dialog. After confirming that dialog you get a perfectly valid comma separated file with ISO dates, and point decimals. For exotic formats (such as comma decimals and US dates) you can create another view.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
doubleDog55
Posts: 2
Joined: Mon Nov 18, 2019 5:32 pm

Re: [SOLVED] Simple dumb? Can't use SaveAll...now! Export to

Post by doubleDog55 »

Getting one row from Query to Spreadsheet is not helpful. This issue makes BASE somewhat useless. NOT SOLVED for Open Office 4.7
doubleDog55 OpenOffice 4.7
doubleDog55
Posts: 2
Joined: Mon Nov 18, 2019 5:32 pm

Re: [SOLVED] Simple dumb? Can't use SaveAll...now! Export to

Post by doubleDog55 »

OK, it is solved. When the instruction said 'open' the query' it really meant just open the list of queries and then right-click to the context menu and use that 'Copy' command. Much simpler.
doubleDog55 OpenOffice 4.7
Post Reply