Database Solutions

These database solutions are designed to give you ideas for how you can use one or more of the Pervasive DataTools products to solve data import and data export challenges -- including extract, tranform, and load -- for virtually all databases including SQL/DBMS and desktop databases.

In this section we have included information about migrating, converting, and transforming data between disparate databases. Use the DataTools to add/insert, update, upsert, and delete data records in your target database, all making use of the built-in ability to match records on key fields.

You will find data conversion solutions for source and target SQL databases such as MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2, Oracle, Informix, Sybase and many more. There are also data migration solutions for desktop databases such as FoxPro, dBASE, Microsoft Access, and Clipper. In addition, we have included solutions for mapping and converting data between databases and flat files, CSV text files, report files, print files, bulk load files, and many other data file formats.


Reading Source Data | Writing Target Data | Putting it All Together



Reading Source Data

On the "read" or "source" side of any project where you need to extract data from a database, one or more categories of the DataTools product line offers a method of source connection, as follows:

DataTools Source Connection Type
Extractors from Databases Read source data directly from a specific database such as Microsoft SQL Server or Oracle.
Parsers from Legacy Data Read and parse data directly from legacy data and flat file formats that contain no metadata.
Extractors from Data Files Read data that has been exported from the database to a CSV text file.
Parser for Print Files Read and extract data directly from print or report files that were created from the database.

Source Connection Ideas

Based on the information in the above table, consider each of these scenarios, and find the one that most closely matches the type of source connection that works best for your project.

  • Direct Connection - Use the Pervasive DataTools Data Extractor that provides a source-side adapter to connect directly to your database.
  • Intermediate Connection - If there is no DataTools Data Extractor that connects directly to your source database, use one of the following methods:

    • Parse Legacy Flat Files - Use the Data Parser to parse fixed text or legacy flat files into fields and records on the source side.
    • Connect to a CSV Text File - Export the data from your database to a Delimited ASCII text file. Use the Data Extractor from CSV Text to read the source data file.
    • Parse and Extract Data from a Print File - Create a report in your database and print the report to a file (rather than to a printer). Use the Data Parser for Unstructured Text to extract the useful data from the source report file.

Back to Top


Writing Target Data

On the "write" or "target" side of any project where you need to import or load data into a database, one or more categories of the DataTools product line offers a method of target connectivity, as follows:

DataTools Target Connection Type
Loaders to Databases Write the data directly to a specific target database.
Loaders to Databases Write the data to a SQL script file specific to the bulk import utility of the target database. DataTools supports bulk load, bulk copy, bcp, sql*loader, dbload, and other bulk load formats specific to the target database.
Builders to Create Import Files Write data to a specific intermediate file format that can be imported into the database.
Loader to CSV Text Write data to an intermediate file format that can be imported into the database.

Target Connection Ideas

Based on the information in the above table, consider each of these scenarios, and find the one that most closely matches the type of target connection that works for your project.

  • Direct Connection - Use the Pervasive DataTools Data Loader that provides a target-side adapter to connect directly to your database.
  • Bulk Load File - The Pervasive DataTools Data Loaders provide target-side bulk load, bulk copy, bulk insert, and bulk import options to create a load file for super-fast inserts into your database. Supported formats are bulk load, bulk copy, bcp, sql*loader, dbload, and others.
  • Intermediate Connection - If there is no DataTools Data Loader that connects directly to your database, write the data using one of the following methods:

    • Create an Import File - Use one of the Data Builders to write out a target data file in the exact format required by the database's import utility. Then import the data into the database.
    • Create a CSV Text File - Use the Data Loader to CSV Text to write the data to a target data file. Then import the data into the database.

Back to Top


Putting it All Together

While the large number of applications, databases, and file formats creates an endless combination of connection options, we will try to describe these options in broad terms. In that way, you can determine which method of connecting to your source and/or target database works best for you, and which of the DataTools products you need.

In this section, we will list and describe examples of how one or more DataTools products can be used to solve your data challenges.

Direct Connections to Source and Target

If you are converting data between any of the directly-supported databases listed above in the Reading Source Data or Writing Target Data sections, simply purchase a subscription for the Data Extractor from "your database" or the Data Loader to "your database" and connect to both databases using the supplied adapters, create the data mapping, and go!

When your target database is any of the supported SQL databases, the Data Loader to "your database" typically provides direct connection to several versions of the database, along with the ability to write the source data out to a bulk load, bulk copy, or bulk insert load file. These are SQL script files that match the specifications and meet the requirements of the bulk import utility of your target database. For example:

  • SQL Server - bcp (bulk copy utility)
  • Sybase - bcp (bulk copy utility)
  • IBM DB2 - dbload
  • Oracle - sql*loader, sqlldr

The data will load into the target database at lightning-fast speed!

Direct Connection to Source / Intermediate Connection to Target

If a Data Builder is available for your target database, purchase a subscription for that Data Builder. It will connect to your source database directly and will help you create data files that "fit" your target database perfectly and can be imported into the database.

If a Data Extractor is available for your source-side database, purchase a subscription for that Data Extractor. Then use a target adapter, such as the Delimited ASCII adapter, in the Data Extractor to write the data to a file format that can be imported into your target database.

Intermediate Connection to Source / Direct Connection to Target

The first step is to export the data out of the source database to an intermediate file format. Depending on the exact format of the exported data file, any one of several options are available for this combination, as follows:

Source File is a CSV Text File

If a Data Loader is available for your target database, purchase a subscription for that DataTool. Then use the Delimited ASCII source adapter in the Data Loader to read the data from the intermediate source file format and write the data directly into one or more tables in the target database.

Source File is a Fixed Text File

Purchase a subscription for the Data Parser for Fixed Text. Use the Data Parser to parse the fixed text data into records and fields, then export the data from the Data Parser to a CSV text file format.

If a Data Loader is available for your target database, purchase a subscription for that DataTool. Then use the Delimited ASCII source adapter in the Data Loader to read the data from the CSV text file that was exported by the Data Parser.

Source File is a Report File

Purchase a subscription for the Data Parser for Unstructured Text. Use this special Data Parser to extract and assemble useful data into records and fields, then export the data from the Data Parser to a CSV text file format.

If a Data Loader is available for your target database, purchase a subscription for that DataTool. Then use the Delimited ASCII source adapter in the Data Loader to read the data from the CSV text file that was exported by the Data Parser.

Intermediate Connections to Source and Target

The first step is to export the data out of the source database to an intermediate file format. Depending on the exact format of the exported data file, any one of several options are available for this combination, as follows:

Source File is a CSV Text File

If a Data Builder is available for your target database, purchase a subscription for that DataTool. Then use the Delimited ASCII source adapter in the Data Builder to read the data from the intermediate source file format and map the data to the exact format to import into your target database.

If a Data Builder is NOT available for your target database, purchase a subscription for the Data Loader to CSV Text to read the data from the intermediate source file format and write the data to a CSV Text file that can be imported into your target database.

Source File is a Fixed Text File

Purchase a subscription for the Data Parser for Fixed Text. Use the Data Parser to parse the fixed text data into records and fields, then export the data from the Data Parser to a CSV text file format.

If a Data Builder is available for your target database, purchase a subscription for that DataTool. Then use the Delimited ASCII source adapter in the Data Builder to read the data from the CSV text file that was exported by the Data Parser and map the data to the exact format to import into your target database.

If a Data Builder is NOT available for your target database, purchase a subscription for the Data Loader to CSV Text to read the data from the CSV text file that was exported by the Data Parser, and map and manipulate the data to a CSV Text file that can be imported into your target database.

Source File is a Report File

Purchase a subscription for the Data Parser for Unstructured Text. Use this special Data Parser to extract and assemble useful data into records and fields, then export the data from the Data Parser to a CSV text file format.

If a Data Builder is available for your target database, purchase a subscription for that DataTool. Then use the Delimited ASCII source adapter in the Data Builder to read the data from the CSV text file that was exported by the Data Parser and map the data to the exact format to import into your target database.

If a Data Builder is NOT available for your target database, purchase a subscription for the Data Loader to CSV Text to read the data from the CSV text file that was exported by the Data Extractor, and map and manipulate the data to a CSV Text file that can be imported into your target database.

Back to Top

List of Databases

Here is a partial list of databases for which the DataTools can be used to import and export data.

  • 4th Dimension
  • Adabas
  • Advantage
  • Alventis
  • ANTS
  • Axion
  • Berkeley
  • Birdstep
  • Clipper 5.2
  • Clipper 5.3
  • Cloudscape
  • Codebase
  • Daffodil DB
  • Dataflex
  • DataPerfect
  • db4objects
  • dBase II
  • dBase III
  • dBase IV
  • dBase V
  • dBase 2
  • dBase 3
  • dBase 4
  • dBase 4LW
  • dBase 5
  • dBase 5.0
  • dBase 5.5
  • dBase 5.7
  • dBase 7
  • dBase 7.5
  • DBISAM
  • DBMaker
  • dbStar
  • DBVista
  • FileMakerPro
  • Firebird
  • FirstSQL
  • FiveWin
  • FoxPro 2
  • FoxPro 2.0
  • FoxPro 2.5
  • FoxPro 2.6
  • FoxPro 2.6 A
  • FoxPro 2.6 DOS
  • FoxPro 2.6 Windows
  • FoxPro 3.0
  • FoxPro 5
  • FoxPro 5.0
  • FoxPro 6
  • FoxPro 6.0
  • FoxPro 7
  • FoxPro 7.0
  • FoxPro 8
  • FoxPro 8.0
  • FrontBase
  • GNU SQL
  • H2 Database
  • HSQLDB
  • Infinity
  • Ingres
  • InterBase
  • ITTIA
  • JavaDBF
  • jBase
  • JDataStore
  • MaxDB
  • Mckoi
  • Mimer SQL
  • mSQL
  • MySQL
  • Paradox
  • Pick
  • Pointbase
  • PowerBuilder
  • Progress
  • QED
  • Revelation
  • SAP DB
  • Sav Zigzag
  • Servoy
  • Smyle
  • Spider
  • SQLite
  • tinySQL
  • Unify
  • VistaDB
  • Visual dBase
  • Visual dBase 5.5
  • Visual dBase 5.7
  • Visual dBase 7
  • Visual dBase 7.5
  • xBaseJ

For a more complete list of databases - but certainly not all! - click here...

And of course, if you need assistance, please enter a brief description of your project - along with your question(s) - in one of our user forums. The Pervasive DataTools Team is always happy to help you determine which of our data tools will work for your data project.

Back to Top