Here you can find the detailed instruction on how to export data from a dBase table to SQL file.
Please note, that when choosing exporting to SQL, you do the things twice: first, export the data to SQL script; second, load data from the script to your destination database. Therefore, this option can be less effective, especially for large datasets or when automating the data exporting.
To export from dBase to SQL script, please consider the following:
- To export one table, Exportizer standard edition is enough to perform the task. To export more a group of tables and/or SQL query results at a time, Exportizer Enterprise is required. Below, exporting one table is described.
- You can work with dBase data either via ADO or BDE interface, but note:
- When using ADO and ODBC, the Exportizer architecture (i.e. 32-bit or 64-bit) must match the architecture of the corresponding ODBC driver.
- When using ADO and Microsoft Jet, Exportizer 32-bit must be used.
- When using ADO and Microsoft ACE, the Exportizer architecture must match the architecture of the installed Microsoft Office.
- When using BDE, Exportizer 32-bit must be used.
Note: If your operating system is 64-bit, you can install both Exportizer 32-bit and Exportizer 64-bit and use them independently.
Configuring DBF to SQL Script Exporting
- Launch Exportizer.
- Register your source dBase database. It must be a folder with .dbf files.
Interface can be ADO or BDE. In the latter case, you'll probably need some preparations on Windows Vista+ to get it to work (there are instructions in the Internet).
In case of ADO, just select the folder and specify the driver (dBase). In Exportizer Enterprise, you can use your own connection string (see examples) to connect the database instead of specifying just the folder name.
To let you easily identify the database, give it a friendly name (alias).
- Open the registered dBase database.
- Choose a table to export.
- Click Export button .
- Switch to the SQL Script tab and specify a destination SQL file.
- Specify export parameters: target table name, statement type, target database type, file encoding and so on.
Statement type specifies the type of SQL commands to produce. The following commands are supported: INSERT, BATCH INSERT (if supported by target database type), UPDATE, MERGE (the combination of INSERT and UPDATE), DELETE, and PostgreSQL Copy.
Target database type specifies the type of database where the generated SQL command will be executed. For example, SQL Server, Firebird, SQLite etc.
There is a block of options to specify initial actions. These are SQL commands which must be added before the data processing statements. For example, if you want to drop existing table and then create it again before inserting records, use Drop table and Add DDL options; if you are not sure if the target table exists, specify Add IF (NOT) EXISTS option (if supported by the target database type). Please note that some initial actions are mutually exclusive, for example, you cannot use Empty table and Drop table options at the same time.
- Click Next and check the source-to-target field mappings. Here, you specify the following important options:
- The correspondence between source fields (physical or calculated) and target fields.
- Key fields for MERGE, UPDATE and DELETE commands. Key fields are needed to identify target records to be affected.
- Full specification of the target fields. It will be used if Add DDL option have been chosen on the previous step.
- Click Export.
- Exporting dBase (DBF) to SQL Script
- Exporting SQLite to SQL File
- Exporting Large Tables
- Exporting BLOBs to Individual Files
- Exporting to HTML Using Template
- Importing Database Files from Explorer