When the integration platform cannot connect directly to source or target data, it uses intermediate import and export formats for data transformation.

Although all programs, applications, and software packages store data in native internal formats, most offer some primitive level of import and export support (for example, .sdf, .dbf, .wk1) for reading and writing external data. The integration platform can read and write most dialects of intermediate file (fixed ASCII, delimited ASCII, sequential, Lotus 123, Excel, and xBASE), modifying and mapping data between unequal sources and targets to perform transformations.

Much of this work involves modifying the structure of fields (for example, adding, deleting, rearranging, and changing field sizes) and modifying the data itself (for example, parsing names, formatting dates, and expanding codes). The integration platform provides a visual interface for performing this work quickly.

The following topic provides details about how the integration platform works with intermediate import and export file formats.

When the integration platform cannot write directly to the native format of a target application, you can use its ability to import through intermediate data if the application has a minimum facility for importing external data files. Using such import capabilities enables you to insert data into complex internal native file systems and to perform indexing, transaction control, audit trail updates, and other advanced tasks. However, to import the data, target applications often require that it first be organized in particular ways.

The integration platform provides a utility that cleans and formats data for batch loading into SQL databases such as Oracle, SQL Server, and Sybase. Although the integration platform can transform data directly into SQL targets (with the Native API or ODBC), SQL inserts of individual records into a database table is slow and therefore not practical for large amounts of data.

Most SQL databases have an off-line batch loading facility (for example, BCP for Sybase and SQL Server, SQL Loader for Oracle, DBLoad for Informix). These loader utilities have been optimized for the mass insertion of large amounts of data directly into database tables but demand that incoming data be formatted in specific ways. You can use the integration platform to map, manipulate, and transform the foreign source data into a format suitable for feeding into batch loader tools.

When the integration platform cannot read data directly from the native format of a source application, you can use its ability to export data if the target application has a minimal facility for exporting external data files. However, exported data often needs to be transformed into formats that downstream applications can use. You can use the integration platform to transform the data as needed for downstream use.