Exporting SQLite Data to Firebird - Exportizer Documentation

Here you can find the detailed instruction on how to export/import data from SQLite to Firebird database in Exportizer software.

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

Export Conditions

In most cases, to export from SQLite to Firebird, the following conditions are required:

SQLite3.dll must be accessible.
Exportizer Pro or Exportizer Enterprise must be used.
When using Exportizer Pro, the source and target databases must be connected via ODBC (using ADO interface), so make sure both SQLite ODBC driver and Firebird ODBC driver were installed and the corresponding ODBC data sources were created. The drivers must be both either 32-bit or 64-bit.
When using Exportizer Enterprise, both databases can be connected using FD interface, and there are no ODBC drivers required. Of course, you can use other connection methods available for these types of databases, but note that using FD on the target side can significantly speed up the data exporting.
All involved components, i.e. Exportizer, ODBC drivers (if you use ODBC on source or target side), OLE DB providers, should have the same architecture: 32-bit or 64-bit.

Notes

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.
You can invoke the ODBC Data Source Administrator directly from Exportizer (in Open Data Source dialog) when it was launched in administrator mode.

SQLite to Firebird 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.

Launch Exportizer.
Register your source SQLite database:
- In Exportizer Pro, use ADO interface. The database must be an ODBC DSN, pointed to your SQLite database file.
- In Exportizer Enterprise, use FD interface. In most cases, specifying a pointer to the SQLite file is enough. Sometimes, specifying the Vendor library parameter may be needed: this must be a main DLL from the folder where SQLite installed, e.g. sqlite3.dll.
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 registered SQLite database or just drug and drop the source SQLite file onto the Exportizer table list.
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.
If your case is a, b, or c, proceed by clicking Export button . Otherwise, choose Export | Export Open Datasets... 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). Turn on the Memory saving mode option. Choose the correct export mode.
Click Next. For multi-table exporting, specify the source-to-target table mappings, otherwise check the source-to-target field mappings. Here, you set the correspondence between the source and target objects.
When you specify the field mappings, you can use either physical database fields or calculated fields at the source side. At the target side, it is possible to provide full specifications for target columns, including types, default values, character sets, NOT NULL constraints etc.

When you specify the table mappings (for multi-dataset exporting in Exportizer Enterprise), you can also create the nested field mappings for each dataset-to-table pair.
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 SQLite to Firebird from Command Line

Here are some examples of exporting data from SQLite database to Firebird database.

Exporting an SQLite Table to Existing Firebird Database

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 9\exptizer.exe" /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /SrcDBInterface=FD /SrcDBKind=FILE /SrcDBDriver=SQLITE "/SrcDB=C:\data sources\MyData\project.db" /SrcTableName=BONUSES /TrgDBInterface=FD /TrgDBKind=FILE /TrgDBDriver=Firebird /TrgCharset=UTF8 /TrgDbUserName=sysdba /TrgDbPassword=masterkey /TrgDB=C:\Test\employee.fdb /TrgTableName=BONUSES

Exporting in Silent Mode

You can improve the above example by running it in silent mode, when no windows are shown. Also, a log file can be added to control the exporting process:

"C:\Program Files (x86)\Vitaliy Levchenko\Exportizer Enterprise 9\exptizer.exe" /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /SrcDBInterface=FD /SrcDBKind=FILE /SrcDBDriver=SQLITE "/SrcDB=C:\data sources\MyData\project.db" /SrcTableName=BONUSES /TrgDBInterface=FD /TrgDBKind=FILE /TrgDBDriver=Firebird /TrgCharset=UTF8 /TrgDbUserName=sysdba /TrgDbPassword=masterkey /TrgDB=C:\Test\employee.fdb /TrgTableName=BONUSES /LogFile=C:\Test\export.log

Exporting All Tables from SQLite File

Exporting all tables from SQLite 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 /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /SrcDBInterface=FD /SrcDBKind=FILE /SrcDBDriver=SQLITE "/SrcDB=C:\data sources\MyData\project.db" /SrcTableName=* /TrgDBInterface=FD /TrgDBKind=FILE /TrgDBDriver=Firebird /TrgCharset=UTF8 /TrgDbUserName=sysdba /TrgDbPassword=masterkey /TrgDB=C:\Test\employee.fdb /TrgTableName=* /LogFile=C:\Test\export.log

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 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.

See also