Exporting Data to Firebird - Exportizer Documentation

Here you can find the detailed instruction on how to export data from different databases to Firebird database in Exportizer software.

Below, it is shown how to export data from GUI or command line.

Export Conditions

In most cases, to export data to Firebird, it is important to fulfill the following conditions:

If source and target databases are configured as ODBC DSNs, Exportizer Pro can be used, otherwise Exportizer Enterprise should be used.
Firebird client must be installed. Make sure the Exportizer and Firebird client have the same architecture, i.e. either all are 32-bit or all are 64-bit.
When using ODBC either on the source or the target side, make sure the Exportizer and the corresponding ODBC driver(s) have the same architecture too.

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.

Export to Firebird Preparations

The following actions are recommended, but not required. If you want to dive into the process and understand how it works, do them. 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.

Launch Exportizer.
Register your source database.
Register your target Firebird database. The recommended interface is FD, but other options are also available. Basically, you specify a server, a database (i.e. file path), and charset.
If the target database does not exist yet, you can create an empty .fdb file in Exportizer Enterprise. If you use Exportizer Pro, you need to create the Firebird file in some third-party tool which is able to do that.
Note: You can create and/or register the target database from the Export dialog during the exporting.

Export Steps (GUI)

Open the source database.
Choose a table or tables to export, or write and execute your SQL query or queries. Please note that exporting multiple datasets at a time is available in Exportizer Enterprise only.
Click Export button or choose a needed item from Export menu.
Switch to the Database tab and select your registered Firebird database as a target database. If you did not register it yet, do it now by clicking the '...' button to the right; you can also create a new Firebird database here.
Specify a target table. For multi-table exporting, you can leave this field blank, and all source-to-target table mappings can be filled at the next export step.
It is recommended to turn on all Memory saving mode options. If something goes wrong during the exporting, try to use different combinations of the options. If exported data or column names on any side can contain Unicode characters, set the corresponding stream Encoding, e.g. UTF-8.

Choose the correct export mode.
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: calculated either on the source side (by Exportizer) or on the destination side (COMPUTED Firebird columns). On the target side, full column specifications can be used (i.e. column types and sizes, default values, character sets, etc).

In table mappings (for multi-table exporting in Exportizer Enterprise), you create the correspondence between the source datasets (tables and/or queries) and target Firebird tables.
Click Export.

Resolving Export Performance Issues

In case of performance problems, try different combinations of Memory saving mode options and different values of Commit interval setting; you might want to read all the data exporting recommendations.

Resolving Unicode Issues

If there are problems with exporting Unicode characters in text columns, check the following settings:

If the stream Encoding option is available, make sure its value is UTF-8.
In the Field Mappings section, make sure the character set for the corresponding column(s) is UTF8. Please note that this setting will be applied for creating the target table (e.g. when using REPLACE+INSERT export mode). If the data are exported to existing table (e.g. in APPEND, UPDATE or APPEND+UPDATE export mode), make sure the existing destination column's character set is UTF8. If not, you can change the character set of the needed column in Exportizer by executing the following command in SQL window:
```
ALTER TABLE <table_name>
ALTER COLUMN <column_name> TYPE <column_type> CHARACTER SET UTF8;
```
(Less importance) Check the default database character set; it is recommended to set it to UTF8.

Exporting Excel to Firebird from Command Line

1. Exporting an Excel table to existing Firebird database:

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 9\exptizer.exe" /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /SrcDBInterface=ado /SrcDBKind=FILE /SrcDBDriver=Excel /SrcDB=C:\TEST\employee.xlsx "/SrcTableName=BONUSES$" /TrgDBInterface=fd /TrgDBKind=FILE /TrgDBDriver=Firebird /TrgDbUserName=sysdba /TrgDbPassword=masterkey /TrgDB=C:\Test\employee.fdb /TrgTableName=BONUSES

2. Exporting Excel to Firebird in silent mode, when no windows are shown. Also, let's add a log file to control the process:

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 9\exptizer.exe" /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /SrcDBInterface=ado /SrcDBKind=FILE /SrcDBDriver=Excel /SrcDB=C:\TEST\employee.xlsx "/SrcTableName=BONUSES$" /TrgDBInterface=fd /TrgDBKind=FILE /TrgDBDriver=Firebird /TrgDbUserName=sysdba /TrgDbPassword=masterkey /TrgDB=C:\Test\employee.fdb /TrgTableName=BONUSES /LogFile=C:\Test\export.log

3. Exporting all tables from Excel file to existing Firebird database in silent mode:

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 9\exptizer.exe" /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /SrcDBInterface=ado /SrcDBKind=FILE /SrcDBDriver=Excel /SrcDB=C:\TEST\employee.xlsx /SrcTableName=* /TrgDBInterface=fd /TrgDBKind=FILE /TrgDBDriver=Firebird /TrgDbUserName=sysdba /TrgDbPassword=masterkey /TrgDB=C:\Test\employee.fdb /TrgTableName=* /LogFile=C:\Test\export.log

4. Exporting a table from MS Access database to Firebird database:

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 9\exptizer.exe" /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /FieldMappingsFile=C:\MyData\SOFTWARE_FieldMappings.xml /TrgTableName=SOFTWARE /TrgDBInterface=fd /TrgDBKind=FILE /TrgDBDriver=Firebird /TrgDBUserName=SYSDBA /CommitInterval=1000 /MemorySaving /UseSQLParameters /UseBatchMode /TrgServer=localhost /TrgPort=3050 /TrgProtocol=TCPIP /TrgOSAuthentication=No /TrgCharset=UTF8 /TrimTrailingSpaces /SrcDBInterface=fd /SrcDBKind=FILE /SrcDBDriver=ACCESS /SrcDB=C:\temp\Products.mdb /SrcStringFormat=Choose /SrcTableName=PROGRAM /TrgDB=C:\TestData\example\dwh.fdb

Here, it is used a /FieldMappingsFile parameter, which points to a file with source-to-target field mappings. The destination part of the mappings contain full Firebird column specifications. Such a file can be generated automatically in Exportizer GUI.

Notes

These are just basic command lines, and you can improve them by adding other command line parameters in accordance with the documentation. For example, consider explicit using of the field or table mappings by adding the following parameters:
- /FieldMappingsFile Specify the file containing field mappings, i.e. the correspondence between the source and target fields/columns, when exporting one dataset.
- /TableMappingsFile Specify the file containing table mappings, i.e. the correspondence between the source and target tables, when exporting multiple tables. The table mappings may optionally contain nested field mappings for certain tables.
You can export data to a new Firebird database, creating it on the fly (Exportizer Enterprise only). Just add /CreateTargetContainer switch to your command line. With this switch, Exportizer Enterprise will create the target database, if it does not exist yet.

Alternative Scenario Using SQL

If you have some reason not to export data to Firebird directly, you can export the source 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.

See also