[Solved] Error Base not connecting To Calc

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] Error Base not connecting To Calc

Postby jscars54 » Mon Apr 13, 2015 6:15 am

Thanks To All Solved

I'm a beginner and I finally was able to successfully run a query. Now when I try to convert the table to a CSV file I get the error message could not be established or I drag and drop and calc runs for about 30 seconds and freezes up. I reinstalled OO 4.1 thinking there might be a corrupt file, no good, no help, I've also registered the new db in Base. Any help out there would be most excellent. Everyone here has been great, I have one more task to learn this being it.

Thanks in advance
Last edited by jscars54 on Tue Apr 14, 2015 6:35 pm, edited 1 time in total.
Open Office 4.1.0
windows 7 HP
jscars54
 
Posts: 26
Joined: Fri Apr 03, 2015 12:04 am

Re: error base not connecting To calc.

Postby Villeroy » Mon Apr 13, 2015 11:46 am

If the spreadsheet solution does not work (always used to work for me), let the database engine do the job, particularly when the record set is large. If the status bar of your database window reads "HSQL embedded", you can do it like this:

1. Copy the SQL string of your query.
2. menu:Insert>View (Simple)
3. Paste the query and save the resulting view.
A view is the similar to a query but the database engine does not see any Base queries because the database engine is HSQL and not Base.
4. Open the view just to be sure it does the same thing.
5. menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
SELECT * FROM "MyView" INTO TEXT "export"

where "MyView" is the name of your view and "export" is the name of the resulting csv file.
6. There should be an export.csv in the same directory as the Base document.

Alternatively, without view, you can copy the SQL command from your query and execute:
Code: Select all   Expand viewCollapse view
SELECT * FROM ( your_pasted_query_string ) INTO TEXT "export"

with braces around your SELECT statement.

---------------- Back to the spreadsheet copy ---------------------------------------------
Due to a bug dragging and dropping a query icon from a database window into a spreadsheet does not work anymore. You can still copy the query icon and then paste to a spreadsheet cell in order to copy query results into a sheet. With large amounts of data this may take some time.
Drag&drop still works from the data source window (hit F4 in Calc). Drag the icon of your query from the left pane onto the sheet. This will create a linked copy of the query results which means that you can store the spreadsheet as an ordinary .ods document and re-run the same query by clicking any single cell in the import area and then menu:Data>Refresh.
If the amount of data is large, you might want to use the following option:
Click any single cell of the import area and call menu:Data>Define... [More Options]
Check the "Don't save imported data" and click buttons [Modify] and then [OK].
Select all the import data and delete contents (backspace key).
Now you have a small spreadsheet with no import data. When opening the spreadsheet you will be prompted to refresh the import with current database data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24659
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 2 guests