The loading of flat files is performed using the database specific loader. For Oracle, this is SQL*LOADER. For SQL Server it is the Bulk Insert Transact SQL statement. The contents of the Transformation tab in the column's Properties are the functions and conversions supported by the database loader.
Example
SQL*LOADER performs transformations such as:

  • DATE 'DD-MMM-YYYY'converts from a value such as 23-Mar-1999 to an Oracle date.
  • "TO_NUMBER(:COL)"converts the data to a number.
  • "NVL(TRIM(:COL),0)"trims leading and trailing white characters and inserts zero if null.

In Teradata, the loading of flat files is performed using Fastload, Multi-load or TPT. The contents of the Transformation tab in a column's Properties are the functions and conversions supported by the database loader.

Example

Multiload performs transformations such as:

  • FORMAT 'DD-MMM-YYYY' converts from a value such as 23-Mar-1999 to a Teradata date.
  • CAST(:COL AS NUMERIC(18,4)) converts the data to a numeric(18,4)
  • COALESCE(LTRIM(RTRIM(:COL)),0) trims leading and trailing white characters and inserts zero if null. 
     
  • No labels