Here you can find the detailed instruction on how to export/import Microsoft Excel table(s) to Firebird database. The data migration from Excel to other database types like SQLite, PostgreSQL, 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.
In most cases, to export from Excel to Firebird, the following conditions are required:
- If the target Firebird database configured as ODBC DSN, 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.
Configuring Excel to Firebird Exporting
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 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.
- 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 Excel database or just drug and drop the source Excel file onto the Exportizer table list.
- 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). 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.
- Click Export.
In case of performance problems: try different combinations of Memory saving mode options and Commit interval option; you might want to read all the data exporting recommendations.
Exporting Excel to Firebird from Command Line
Here are some examples of exporting data from an .xlsx file to Firebird database.
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
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 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
Exporting All Tables from Excel File
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
- 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 Third-Party SQL Tool
If you have a third party Firebird SQL tool, you can export the Excel data in Exportizer to a SQL script first, and then load data from the script using that 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.
Anyway, we recommend to try both export scenarios and select the fastest and/or the most convenient one.
Exporting Range of Cells Using Clipboard
- Copy a range of cells in Excel.
- Launch Exportizer.
- Register your Firebird database.
- Open your registered Firebird database and a target table in it.
- Rearrange the table columns so that their order match the order of Excel columns from the copied range.
- 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.
- Right-click the cell, which will be the first cell to paste data to, and choose Paste Cells from Clipboard menu.
- Exporting Excel to Firebird
- Exporting Large Tables
- Exporting Calculated Fields
- Exporting BLOBs to Individual Files
- Exporting to HTML Using Template
- Importing Database Files from Explorer