Exporting PostgreSQL to Excel - Exportizer Documentation

Here you can find the complete guide for converting data from PostgreSQL database to Microsoft Excel in Exportizer software. Exporting data from other database types like Firebird, SQL Server, Oracle, etc is very similar; the difference is in registering the database in Exportizer.

Below, it is shown how to export data directly (from GUI or command line), or using clipboard.

Note: Exportizer also provides the ability to export data to intermediate file formats. For example, you can export PostgreSQL (Firebird, MySQL etc) data to CSV or SYLK format, and then open the destination .csv or .slk file in Excel: it is faster, but has some inconveniences comparing with direct exporting to Excel (e.g. when exporting multiple tables to different sheets of one Excel workbook). Exporting to intermediate file formats is not covered in this topic.

Export Conditions

In most cases, to export from PostgreSQL to Microsoft Excel, the following conditions are required:

PostgreSQL client must be installed. Make sure the Exportizer and PostgreSQL client are both 32-bit or 64-bit.
You can open PostgreSQL database either by Exportizer Pro or by Exportizer Enterprise. If you have created an ODBC DSN for your PostgreSQL database, Exportizer Pro can be used, otherwise Exportizer Enterprise should be used.
It is important to choose the proper Excel output format:
- When exporting to Excel (OLE) target format, Microsoft Office must be installed; Exportizer and Microsoft Office should be both 32-bit or both 64-bit.
- When exporting to other Excel formats like XLS or XLSX, Microsoft Office is not required, and you can use either Exportizer 32-bit or Exportizer 64-bit.
When using ODBC on the source side, make sure the Exportizer and the corresponding PostgreSQL ODBC driver have the same architecture (32-bit or 64-bit).

Note: If your operating system is 64-bit, you can install both 32-bit and 64-bit versions of the Exportizer software and use them independently.

Configuring PostgreSQL to Excel Exporting

If you are exporting data via GUI:

Launch Exportizer Pro or Exportizer Enterprise.
Register your source PostgreSQL database:
- In Exportizer Pro, use ADO interface. The database must be an ODBC DSN, pointed to your PostgreSQL database.
- In Exportizer Enterprise, use FD interface. In most cases, specifying a server and port is enough. Sometimes, specifying the Vendor library parameter may be needed: this must be a main DLL from the folder where PostgreSQL installed, i.e. libpq.dll.

Export Steps (GUI)

Open the registered PostgreSQL database.
Prepare a dataset or datasets to export. Please choose between different options:
1. Open a table.
2. Write and execute a SQL query to export the query result.
3. Click Select Tables button and select multiple tables in the table list.
4. To export multiple query results, open needed number of SQL windows. In each of them, write and execute SQL queries.
Please note that exporting multiple datasets at a time is available in Exportizer Enterprise only.
If your case is a, b, or c, proceed by clicking Export button . Otherwise, choose Export | Export Open Datasets... menu.
Choose the target Excel format. You have several formats available: Excel (XLSX), Excel (XML-based), Excel (XLS), Excel (OLE). Please note that exporting to Excel (XLS) is the fastest, and exporting to Excel (OLE) is the slowest and requires Microsoft Excel installed; Excel (XLSX) format is the most configurable, and allows multiple tables to be written to one file, one table per Excel sheet.
Specify a target file (for multi-table exporting, you can specify a folder, where the target files will be created) and needed export options like target sheet name, table title, range name and so on.

Note: If you later need to open the target Excel file in Exportizer, make sure to specify the range name for the target table; named ranges help Excel ODBC driver to correctly identify tables inside Excel file when opening it as a database.
Click Next.
Specify the source-to-target table and/or field mappings.

In field mappings, you create the correspondence between the source and target columns. In addition to source table columns, you can also use calculated fields specified by formulas.

In table mappings (for multi-dataset exporting), you create the correspondence between the source datasets (tables and/or queries) and target Excel files. If you export to Excel (XLSX) format, you can specify a different sheet name and table title for each exported dataset. You can also specify nested field mappings for each table pair.

You can skip this step by clicking Export instead of clicking Next. In this case, the table and/or field mappings will be built automatically, which may be not desirable.
Click Next to proceed to export logs and error handling options or click Export to start the export process immediately.

Exporting PostgreSQL to Excel Using Command Line

In documentation, you can find full Exportizer command line specification. But basic command lines can be built in Exportizer GUI automatically by clicking Build Command Line button. In Export dialog, you can also create an action file using Tools button. Below, you can find a set of command line examples of exporting data from PostgreSQL to Excel.

When you export data from the command line, you do not need to register the source database (but you still can).

PostgreSQL to XLSX Command Line Examples

Exporting a table from PostgreSQL database:

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Enterprise 9\exptizer.exe" /export /ExportType=EXCELXLSX /ExportMode=REPLACE+INSERT /RowsPerSheet=800000 /SheetName=Employees "/TableTitle=Company Employees" /RangeName=employee /IncludeColNames /IncludeImages /IncludeMemo /SrcDBInterface=fd /SrcDBKind=DSN /SrcDBDriver=POSTGRESQL "/SrcServer=222.333.2.14" /SrcPort=5432 /SrcDB=dwh /SrcDBUserName=dwh_master /SrcDBPassword=dwh_master_password /SrcTableName=employee /TrgDB=C:\TEST\employee.xlsx

Exporting several tables from PostgreSQL database to one target XLSX file (one table per Excel sheet):

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Enterprise 9\exptizer.exe" /export /ExportType=EXCELXLSX /ExportMode=REPLACE+INSERT /TableMappingsFile=C:\TEST\PostgreSQL2ExcelTableMappings.xml /RowsPerSheet=800000 /IncludeColNames /IncludeImages /IncludeMemo /SrcDBInterface=fd /SrcDBKind=DSN /SrcDBDriver=POSTGRESQL "/SrcServer=222.333.2.14" /SrcPort=5432 /SrcDB=dwh /SrcDBUserName=dwh_master /SrcDBPassword=dwh_master_password /TrgDB=C:\TEST\

In the last case, please pay attention to how the source-to-target table mappings are defined. We use /TableMappingsFile parameter which points to an XML file with the mappings definition. For exporting three tables, the file content might look like this:

<?xml version="1.0" encoding="UTF-8"?>
<TableMappings>
  <Items>
    <TableMapping>
      <Source>
        <TableName>employee</TableName>
      </Source>
      <Target>
        <FileName>org.xlsx</FileName>
        <TableTitle>Company Employees</TableTitle>
        <SheetName>Employees</SheetName>
        <RangeName>employee</RangeName>
      </Target>
    </TableMapping>
    <TableMapping>
      <Source>
        <TableName>goods</TableName>
      </Source>
      <Target>
        <FileName>org.xlsx</FileName>
        <TableTitle>Available Goods</TableTitle>
        <SheetName>Goods</SheetName>
        <RangeName>goods</RangeName>
      </Target>
    </TableMapping>
    <TableMapping>
      <Source>
        <TableName>salary</TableName>
      </Source>
      <Target>
        <FileName>org.xlsx</FileName>
        <TableTitle>Position Salaries By Department</TableTitle>
        <SheetName>Salaries</SheetName>
        <RangeName>salary</RangeName>
      </Target>
    </TableMapping>
  </Items>
</TableMappings>

PostgreSQL to XLSX Action File Example

The last command line above can be transformed to an action file, which is much more comfortable to work with. You can run action files either form the command line or from Exportizer GUI. The action file contains all the command line parameters (except /silent switch), one parameter per line, and allows comments:

/export 
/ExportType=EXCELXLSX 
/ExportMode=REPLACE+INSERT  ;Target files will be overwritten if they exist
/TableMappingsFile=C:\TEST\PostgreSQL2ExcelTableMappings.xml 
/RowsPerSheet=800000 
/IncludeColNames 
/IncludeImages 
/IncludeMemo 
/SrcDBInterface=fd 
/SrcDBKind=DSN 
/SrcDBDriver=POSTGRESQL 
/SrcServer=222.333.2.14 ;IP address or server URL
/SrcPort=5432 
/SrcDB=dwh 
/SrcDBUserName=dwh_master 
/SrcDBPassword=dwh_master_password 
/TrgDB=C:\TEST\

Exporting Data Using Clipboard

Launch Exportizer Pro or Exportizer Enterprise.
Register your PostgreSQL database.
Open your registered PostgreSQL database.
Open the source table or write and execute your SQL query.
Copy the needed data. You can do this in two ways:
- Quick, but limited. Select a range of cells in a visible part of the data grid, then click Ctrl+C.
- Flexible. Click Copy Data button . Choose Text/CSV format, Standard schema, Tab field separator, and specify other options if needed. Then click Copy.
Launch Microsoft Excel and paste the copied data to its sheet.