export a large dataset
lazarus / 16-Databases

----------------------------------------------------------------------
Hansvb · 2016-02-19 21:05

What is a good way to export a large dataset? Until now i exported
data from Oracle with fpspreadsbeet to excel or csv. Both formats are
difficult to handle when the dataset is to big. The export is mostly
used for GIS.

----------------------------------------------------------------------
wp · 2016-02-19 21:13

Try fpspreadsheet in VirtualMode - there's an explanation in the wiki
and there are examples in the installation folder and here in the
forum (use "Search"). VirtualMode does not create an in-memory table
but writes the database records directly to the spreadsheet file.

But the main question is: why do you do this? Having a large database
in a huge spreadsheet file has many disadvantages...

----------------------------------------------------------------------
Hansvb · 2016-02-19 21:21

It is not the export that is difficult. (I use the virtualmode). To
handle the file gives the problem. It is to large. Excel gets slow or
crashes. FME doesnt like it.
i have to make these exports to get the data from a client. Work with
it and import it later into a different database.

----------------------------------------------------------------------
rvk · 2016-02-19 21:36

Quote from: Hansvb on February 19, 2016, 10:21:44 pmExcel gets slow or
crashes.Yeah, Excel tends to do that with very big files. But what do
you want to do about it?? Do you need to handle the files in Excel? If
not then it shouldn't be a problem. You (or they) can just import the
Excel file (or csv) to another database. Just don't try to handle big
files in Excel itself.

----------------------------------------------------------------------
Hansvb · 2016-02-19 21:55

Is there a way to export a table as a insert script? Then i can load
the data into a database.
the csv file format is not always a good format. When there is a memo
field the text can hold the delimiter char.

----------------------------------------------------------------------
mas steindorff · 2016-02-19 22:35

Is the reason for your export backup or transport?
I don't know if any FPC tools support it but HDF5 is a designed for
large data files (with mixed and complex data sets).
https://www.hdfgroup.org/
last I check was during the rev 0.9x days. maybe someone has converted
one of the examples into pascal

----------------------------------------------------------------------
Hansvb · 2016-02-20 06:19

My reason is transport.

----------------------------------------------------------------------
sky_khan · 2016-02-20 12:27

Exporting to Sqlite3 database is almost as fast as writing to a file
with these settings.

PRAGMA journal_mode=OFF;
PRAGMA synchronous = OFF;
PRAGMA locking_mode = EXCLUSIVE;

and you may still access your data with database connections or
tools.  IMO, its worth considering

----------------------------------------------------------------------
Hansvb · 2016-02-20 16:44

I first will try the SQLexporter. I just tested it on a small dataset
en it works. Now  i need to see how it works with a large dataset.

----------------------------------------------------------------------
Hansvb · 2016-02-20 20:30

I now use the TSQLExporter. It works. First i create a text file in
which i write the drop table statement and the create statement for
the table and then ik execute the TSQLExporter. Everything seems to
work fine. (I tested it with about 1000000 records and it writes
fast).

It would be nice if the TSQLExporter would write a commit; in the file
after every 1000 records but otherwise it is verry good.

i open a new thread for a question about the TSQLExporter.