Exporting a big table from Base

[aloe]

I have a database with a big table (660K records, it’s actually connected accdb file using ADO). It’s possible to browse it in Base. I need to export this table.

I tried to select all records, Edit->Copy. Open Calc, Edit->Paste. Calc freezes and after a few minutes Windows tells me that LibreOffice stopped working. Repeat, the same result.

I also tried to follow this solution, use Tools->SQL and type:

SCRIPT ‘c:/temp/my.sql’

But Base tells me “Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.”

Any ideas, how to export this big table? It would be the best to export just this one table (not the whole database) to an SQL format (CREATE TABLE, INSERT RECORD etc), but exporting to CSV is also acceptable.

[Edit]

The final code I used as a solution in Tools->SQL was:

SELECT * INTO [Text;DATABASE=C:\temp\].mytable.csv FROM Table1;

Because of a delimiter problem described in this thread, I also created file schema.ini in folder c:\temp containing the following:

[mytable.csv]
ColNameHeader=False
Format=Delimited(|)

The export took 10 seconds and looks good.

[doug]

That would be a great command if it worked for exporting an ACCDB. To make it work in HSQLDB internal on a Windows machine simply reverse the slashes into backslashes. I would not predict whether you will get a SQL load file that actually will work for the data, but it would provide the table structure which will be a time-saver.

However, both that command and the other one I would suggest (the INTO TEXT "tblname" extension to the SELECT syntax, see here) probably will fail if executed on a non-HSQLDB back-end, and so they presumably will fail for the case you intend. Both commands fail for my split Base-MySQL setup. The critical point here is that Base generally relies on the backend engine for all database functions. So if you are running HSQLDB back end, that syntax will work, if you are running something else, some other syntax will work (f there is a syntax). Hopefully the syntax matches up for the ordinary use cases, but for exotic ones, it won’t.

What you need is the ACCDB command to dump the database structure and put tables into CSVs. A quick search for a SQL command comes up empty, which sort of makes sense because the point of ACCDB was not to enable a high degree of portability. I see several outside utilities and methods referenced in this forum, including mdbtools which might be new enough for you.

In short, Base probably won’t help you much with this problem. The underlying reason is that you cannot mix connections into Base and have a little bit of ACCDB and a little bit of HSQLDB and then move data between them. That would be a great, useful, tool, but Base in its current form cannot do that. One database connection to one back end, and in that setup Base only can ask the back end to do things that the back end knows how to do. ACCDB apparently does not know how to export to CSV using a SQL command, so that probably is your answer.

[aloe]

I managed to export the data using info from your answer and from this thread. I’ll update my original post with exact code I used.

[PYS]

Hi

If the database is registered (Tools Options Base Databases) you can try:

• Open a new LibreOffice Calc document
• Open the datasource dialogue (View Data sources)
• Choose the database, choose the table or query
• Click at the intersection of the headers of rows and columns to select all records
• Click Data to textin the Datasource toolbar

Maybe it will not freeze as copy and paste?

Regards

[doug]

good point. also when calc ‘freezes’ in these situations, often it is just working and if you allow it enough time it may recover.

[aloe]

I followed this procedure and waited for around 30 minutes, then killed the Calc process. It was using 60% of my processor capacity and the progress bar wasn’t visible (as soon as it started the import, the Calc window content went all blank and it blinked as if refreshing every 20 seconds). Maybe I would wait if the progress bar were visible.

[PYS]

Ok, too bad

Thank you for your feedback and your solution