Data Export (includes high-volume)

The purpose of this page is to help you understand all the ways you can export data out of iDempiere

Export Options

Export Toolbar icon - almost every window has an Export toolbar icon. It is located on the right side of all toolbars (like save, new, etc..). The export functionality gives you the options of exporting to 2pack, Excel, or CSV. This option gives you the greatest freedom and flexibility to extract data out of the system.
Report Toolbar icon - almost every window has a Report toolbar icon. It is located near the middile of the toolbar icons (like save, new, etc..). This option first generates a report that can also be downloaded to pdf, xls and html.

Export Toolbar Icon

Zip or 2Pack - this option works just like the Pack Out functionality. The format is XML, and it target use is for the Pack In window in another iDempiere instance. It can pack out (export) a single record or all records currently listed in the window. It can pack out the current tab and it can pack out sub-tab records at the same time. Note: one limitation of this format is that it will fail when a window's subtab points to a table that does not use a primary key. The Role window is the perfect example. The Role window's Window Access tab is simply a link table between a AD_Role_ID and a AD_Window_ID. You should use the CSV option in these cases. CSV - this option is similar to Zip/2Pack; however, it stores the results in a csv file. What is amazing is that the system can use this format to store not only a window's tab, but it cal also store its sub-tab records as well. Here is a discussion and example. You can read much more about import options here. Excel - This option will only export the current tab's data. It will not export sub-tab data at the same time.

Report Toolbar Icon

Any time you create a report with iDempiere's native print format, you have the option to export it to csv, pdf, xls or html.

High Volume Export (20K+ Record)

Note that when iDempiere exports data, all data goes through GridTab to fetch Lookup values by executing lookup query for reference type fields. Said another way, it has to resolve all foreign keys to human readable text. Said yet another way, it is not exporting data directly from DB - it goes through a filter first. This concept is expensive in terms of time and computing power! The below bullets describe the fastest way to export massive amounts of data from iDempiere:

Configure the minimum data you wish to export
1. Click the 'Report' button (white page-looking toolbar icon with little blue box)
2. Check the Current Row Only checkbox
3. From the displayed Print Format, click on the Toolbar => Report Wizard icon
4. Remove all not-necessary columns from your report - said another way, only include the needed columns
Export the data
1. Go to the tab you wish to export
2. Click the 'Report' button (white page-looking toolbar icon with little blue box)
3. Go straight to export (no preview) by clicking the Export checkbox
4. Choose CSV - it is 2 times faster than XLS and 3 times faster than XLSX

If you are still not getting the speed you wish using the above steps, consider creating a dedicated view of your data that only includes raw data (no foreign keys to be resolved by iDempiere). By doing so, you remove the need to perform lookups during the export. This view would only include strings, numbers, dates, etc.. Performing this step can increase export speed by an additional 10X if not more.

References

High Volume PSQL Export Query Results to CSV File Training group discussion When to use 2pack discussion