Here you can find the detailed instruction on how to export/import Microsoft Excel table(s) to PostgreSQL database in Exportizer software.

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

Export Conditions

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

Notes

Excel to PostgreSQL Exporting Preparation

The following preparations are recommended, but not required. If you want to dive into the process and understand how it works, do these steps. Anyway, you can skip them and proceed to the Export Steps below; in this case, all the preparations will be done on the fly, explicitly or implicitly.

  1. Launch Exportizer.
  2. Register the source Excel database. It can be an .xls, .xlsx, .xlsb, or .xlsm file. See the links at the end of the topic to learn the possible options and troubleshooting.
  3. Register the target PostgreSQL database. The recommended interface is FD, but other options are also available. Basically, you specify a server and a port. Other important options are database, vendor library, and charset. Note: You can register the target database from the Export dialog during the exporting.

Export Steps (GUI)

Preparing Source Data

  1. Open the registered Excel database or drug and drop the source Excel file onto the Exportizer table list.

    Note: If you do not see expected tables inside it, make sure the corresponding cell ranges have names, because the most of Excel ODBC drivers or OLE DB providers see named cell ranges as tables. See more details in opening XLSX files instruction.

  2. Choose a dataset or datasets to export:
    1. select a table from the table list;
      or
    2. open an SQL window, then write and execute an SQL query;
      or
    3. select multiple tables in the table list; before doing that, click Select Tables button ;
      or
    4. prepare a mix of multiple tables and/or SQL queries: open multiple tables one by one and multiple SQL windows; in SQL windows, write your database queries and execute them to get the result sets.

    Please note that exporting multiple datasets at a time is available in Exportizer Enterprise only.

Choosing Target Database and Export Options

  1. If your case is a, b, or c, proceed by clicking Export button . Otherwise, choose Export | Export Open Datasets... menu.
  2. Switch to the Database tab and select your registered PostgreSQL database as a target database. If you did not register it yet, do it now by clicking the '...' button to the right. Exporting Data to PostgreSQL Database
  3. Specify export parameters:
    • Turn on the Memory saving mode option and its related options. In case of exporting problems, if you suspect that they can be caused by these options, try different combinations of them.
    • Specify the Table name, i.e. name of the target table. For multi-table exporting, you can leave it empty, otherwise, all source datasets will be exported to one destination table; anyways, the source-to-target table mappings are available at the next step.
    • Optionally, specify the target table description (comment). For multi-table exporting, leave it empty; you will be able to specify the target descriptions later on the Table Mappings step.
    • Choose the Export mode. You can read detailed description for selected export mode to the right (hover mouse over it to see full text). For multi-table exporting, this mode will be applied to the most of the tables, and you can override it for specific tables at the next step. For example, it can be Replace+Insert for the most of tables, and Update or Append+Update for others etc.
    • Specify Commit interval. The bigger its value, the faster your exporting process. But too big value may cause memory and other issues. So, try to play with it to find the optimal value for your database before porting the solution to your production environment.

Preparing Source-to-Target Mappings

Click Next to proceed to source-to-target table and/or column mappings or click Export to skip the next steps. When skipping the mappings step, all mappings are built automatically, which sometimes is not desired. When proceeding to the mappings step, initial mappings are built automatically too, but they can be edited when needed. It is recommended to use explicitly built mappings when:

So, when switching to the mappings step, there are two different scenarios depending on how many datasets are involved. If exporting more that one dataset, you work with table mappings, otherwise your work with field/column mappings.

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. It is possible to define full specifications for target columns here, i.e. rename target columns, choose their types, specify NOT NULL constraints, default values, primary key flags, computation formulas, descriptions (comments) etc. These specifications will be applied when the target table needs to be created or overwritten, i.e. when the corresponding export mode is used.

For PostgreSQL identity or computed columns, the source part of the field mappings should be empty.

Editing column mappings for PostgreSQL database

Table Mappings

In table mappings (for multi-dataset exporting), you create the correspondence between the source datasets (tables and/or queries) and target tables. Here, you can specify target table names, their descriptions (comments), and nested field mappings for each table pair.

Editing table mappings

Logs and Error Handling

Click Next to proceed to export logs and error handling options or click Export to start the export process immediately.

Resolving Export Performance Issues

In the case of the slow performance, there are some actions you can try:

Exporting Excel to PostgreSQL from Command Line

Here are some examples of exporting data from an .xlsx file to PostgreSQL database.

Exporting an Excel Table to Existing PostgreSQL Database

Exporting in Exportizer Enterprise (uses FD on target side):

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 10\exptizer.exe" /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /SrcDBInterface=ADO /SrcDBKind=FILE /SrcDBDriver=Excel /SrcDB=C:\TEST\employee.xlsx "/SrcTableName=BONUSES$" /TrgDBInterface=FD /TrgDBKind=DSN /TrgDBDriver=POSTGRESQL "/TrgServer=222.333.2.14" /TrgPort=5432 /TrgDB=dwh /TrgDBUserName=dwh_master /TrgDBPassword=dwh_master_password /TrgTableName=bonuses

Exporting in Exportizer Pro (uses ADO and ODBC on target side):

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Pro 10\exptizer.exe" /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /SrcDBInterface=ADO /SrcDBKind=FILE /SrcDBDriver=Excel /SrcDB=C:\TEST\employee.xlsx "/SrcTableName=BONUSES$" /TrgDBInterface=ADO /TrgDBKind=DSN /TrgDBDriver=POSTGRESQL /TrgDB=PG_ODBC_DSN /TrgDBUserName=dwh_master /TrgDBPassword=dwh_master_password /TrgTableName=bonuses

Exporting in Silent Mode

Let's improve the above example by running it in silent mode, when no windows are shown. Also, let's add a log file to control exporting process:

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 10\exptizer.exe" /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /SrcDBInterface=ADO /SrcDBKind=FILE /SrcDBDriver=Excel /SrcDB=C:\TEST\employee.xlsx "/SrcTableName=BONUSES$" /TrgDBInterface=FD /TrgDBKind=DSN /TrgDBDriver=POSTGRESQL "/TrgServer=222.333.2.14" /TrgPort=5432 /TrgDB=dwh /TrgDBUserName=dwh_master /TrgDBPassword=dwh_master_password /TrgTableName=bonuses /LogFile=C:\Test\export.log

Exporting All Tables from Excel File

Exporting all tables from Excel file to existing PostgreSQL database in silent mode:

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 10\exptizer.exe" /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /SrcDBInterface=ADO /SrcDBKind=FILE /SrcDBDriver=Excel /SrcDB=C:\TEST\employee.xlsx "/SrcTableName=*" /TrgDBInterface=FD/TrgDBKind=DSN /TrgDBDriver=POSTGRESQL "/TrgServer=222.333.2.14" /TrgPort=5432 /TrgDB=dwh /TrgDBUserName=dwh_master /TrgDBPassword=dwh_master_password /TrgTableName=* /LogFile=C:\Test\export.log

Notes

These are just basic command lines, you can improve them by adding other command line parameters in accordance with the documentation.

For example, the examples use implicit field and table mappings, which is quite acceptable when exporting data as is. But when the target structure must differ from the source one, consider explicit using of the mappings by adding the following parameters:

Mappings files can be created either manually in any text editor or automatically from the Export dialog.

Alternative Scenario Using SQL

If you have some reason not to export data to PostgreSQL directly, you can export the Excel data in Exportizer to a SQL script first, and then load data from the script in Exportizer or using an external SQL tool. And, of course, you can export data to SQL script either by GUI or from the command line.

But please note, that when choosing exporting to SQL, your export process will have two phases: first, exporting the data to SQL script; second, loading data from the script to your target database. So, this way can be less effective, especially for large datasets or when automating the data exporting. Another disadvantage of this way is that BLOB data will not be transferred.

Anyway, you can to try both export scenarios and select the fastest and/or the most convenient one.

Exporting Range of Cells Using Clipboard

  1. Copy a range of cells in Excel.
  2. Launch Exportizer.
  3. Register your PostgreSQL database.
  4. Open your registered PostgreSQL database and a target table in it.
  5. Rearrange the table columns so that their order match the order of Excel columns from the copied range.
  6. If you want to insert the copied data as new records, click Insert Record button. Skip this step, if you wish to update existing data.
  7. Right-click the cell, which will be the first cell to paste data to, and choose Paste Cells from Clipboard menu.

See also