LOAD FROM File

The LOAD FROM file contains the data to be loaded into the specified table or view. The default pathname for the load file is the current directory.

You can use the file that the UNLOAD statement creates as the LOAD FROM file. (See UNLOAD TO File for a description of how values of various data types are represented within the UNLOAD TO file.)

If you do not include a list of columns in the INSERT INTO clause, the fields in the file must match the columns that are specified for the table in number, order, and data type.

Each line of the file must have the same number of fields. You must define field lengths that are less than or equal to the length that is specified for the corresponding column. Specify only values that can convert to the data type of the corresponding column. The following table indicates how the database server expects you to represent the data types in the LOAD FROM file (when you use the default locale, U.S. English).
Type of Data Input Format
blank One or more blank characters between delimiters You can include leading blanks in fields that do not correspond to character columns.
BOOLEAN A t or T indicates a TRUE value, and an f or F indicates a FALSE value.
COLLECTIONS Collection must have its values surrounded by braces ( { } ) and a field delimiter separating each element. For more information, see Loading Complex Data Types.
DATE Character string in the following format: mm/dd/year You must state the month as a two-digit number. You can use a two-digit number for the year if the year is in the 20th century. (You can specify another century algorithm with the DBCENTURY environment variable.) The value must be an actual date; for example, February 30 is illegal. You can use a different date format if you indicate this format with the GL_DATE or DBDATE environment variable. For more information about environment variables, see the Informix® Guide to SQL: Reference and the Informix® GLS User's Guide.
DECIMAL, MONEY, FLOAT Value that can include a leading and/or trailing currency symbol and thousands and decimal separators Your locale files or the DBMONEY environment variable can specify a currency format.
NULL Nothing between the delimiters
ROW types (named or unnamed) ROW type must have its values surrounded by parentheses and a field delimiter that separates each element. For more information, see Loading Complex Data Types.
Simple large objects (TEXT, BYTE) TEXT and BYTE columns are loaded directly from the LOAD TO file. For more information, see Loading Simple Large Objects.
Smart large objects (CLOB, BLOB) CLOB and BLOB columns are loaded from a separate operating-system file. The field for the CLOB or BLOB column in the LOAD FROM file contains the name of this separate file. For more information, see Loading Smart Large Objects.
Time Character string in year-month-day hour:minute:second.fraction format You cannot use data type keywords or qualifiers for DATETIME or INTERVAL values. The year must be a 4-digit number, and the month must be a 2-digit number. The DBTIME or GL_DATETIME environment variable can specify other end-user formats.
User-defined data formats (opaque types) Associated opaque type must have an import support function defined if special processing is required to copy the data in the LOAD FROM file to the internal format of the opaque type. An import binary support function might also be required for data in binary format. The LOAD FROM file data must be in the format that the import or import binary support function expects. The associated opaque type must have an assign support function if special processing is required before writing the data in the database. See Loading Opaque-Type Columns.

For more information on DB* environment variables, refer to the Informix® Guide to SQL: Reference. For more information on GL* environment variables, refer to the Informix® GLS User's Guide.

If you are using a nondefault locale, the formats of DATE, DATETIME, MONEY, and numeric column values in the LOAD FROM file must be compatible with the formats that the locale supports for these data types. For more information, see the Informix® GLS User's Guide.

The following example shows the contents of an input file named new_custs: 
0|Jeffery|Padgett|Wheel Thrills|3450 El Camino|Suite 10|Palo Alto|CA|94306||
0|Linda|Lane|Palo Alto Bicycles|2344 University||Palo Alto|CA|94301|
    (415)323-6440
This data file conveys the following information:
  • Indicates a serial field by specifying a zero (0)
  • Uses the pipe ( | ), the default delimiter
  • Assigns NULL values to the phone field for the first row and the address2 field for the second row

    The NULL values are shown by two delimiters with nothing between them.

The following statement loads the values from the new_custs file into the customer table that jason owns: 
LOAD FROM 'new_custs' INSERT INTO jason.customer;
If you include any of the following special characters as part of the value of a field, you must precede the character with a backslash ( \ ) escape symbol:
  • Backslash
  • Delimiter
  • Newline character anywhere in the value of a VARCHAR or NVARCHAR column
  • Newline character at end of a value for a TEXT value

Do not use the backslash character ( \ ) as a field separator. It serves as an escape character to inform the LOAD statement that the next character is to be interpreted as part of the data, rather than as having special significance.

Fields that correspond to character columns can contain more characters than the defined maximum allows for the field. The extra characters are ignored.

If you are loading files that contain VARCHAR data types, note the following information:
  • If you give the LOAD statement data in which the character fields (including VARCHAR) are longer than the column size, the excess characters are disregarded.
  • Use the backslash ( \ ) to escape embedded delimiter and backslash characters in all character fields, including VARCHAR.
  • Do not use the following characters as delimiting characters in the LOAD FROM file: digits ( 0 to 9), the letters a to f, and A to F, the backslash ( \ ) character, or the NEWLINE (CTRL-J) character.