High Volume PSQL Export Query Results to CSV File

The purpose of this page is to help you export high volumes of data out of iDempiere into a csv file with the most speed and the least effort.

There are times when you need to get data out of a database directly into a CSV file. iDempiere makes this incredibly easy using the Window => Toolbar => Export button; however, this feature is also incredibly slow when dealing with large amounts of data. Exporting 1M+ rows using this technique can take hours or even days. iDempiere is slow because of the overhead needed to convert foreign keys to the human readable text equivalents.

References before you get started:

• Exporting data in iDempiere webui

First Step - Export Good Data

Be aware that if your table/view is full of foreign keys, the csv will also be full of foreign keys. Generally, foreign keys are of little value to humans. If you are exporting to csv, you might consider creating a view that de-references the keys to show human-readable column values.

References

• Copy command basics
• General bulk operations

Simple Data Export

Let's start with simply creating a csv file from your data. You can execute the below command from any sql editor (psql, phppgadmin, pgadmin4, etc...)

copy (select * from chuboe_some_view) to '/tmp/chuboe_some_view.csv' delimiter ',' csv header;

This will save the contents of your view a csv in your /tmp/ directory of your database server. Here are some things to consider about this approach:

• The file is saved on your database server.
  • If you have a simple setup, this situation is probably just fine.
  • If you have your app and db server separated, you will probably need to copy the file from the db server to your app server to make the file usable.
• This is a manual process, and you will most likely need a more automated approach to creating your csv file.

Automated Data Export

The next step is to create a cli or bash script to export the data either on a timer or on demand from iDempiere => SystemAdmin => Task window. The below linux command can be run from any server that has the Postgresql psql command installed. The installation script installs this tool on all servers. This means you can run it from your application server.

psqli -h your_db_server -d idempiere -U adempiere -c "copy (select * from chuboe_some_view) to stdout  delimiter ',' csv header" > /tmp/chuboe_some_view.csv

If you were to run this command from a process, you could use the process to return the file back to the user when the process completes.

If the psql call appends a timing line to the end (example: "Time: 141.516 ms"), then use the following command to strip off the last line (source).

head -n -1 input.txt > tmp.txt && mv tmp.txt input.txt