Skip to content

32_CSVBatchInserts.md

Latest commit

 

History

History
31 lines (24 loc) · 3.45 KB

32_CSVBatchInserts.md

File metadata and controls

31 lines (24 loc) · 3.45 KB

CSV Batch Inserts

The driver can read batch insert bind values from a CSV (comma separated values) file. This feature can be used with SQL batch inserts and with FastLoad.

To specify batch insert bind values in a CSV file, the application prepends the escape function {fn teradata_read_csv(CSVFileName)} to the INSERT statement.

The application can specify batch insert bind values in a CSV file, or specify bind parameter values, but not both together. The driver returns an error if both are specified together.

Considerations when using a CSV file:

  • Each record is on a separate line of the CSV file. Records are delimited by line breaks (CRLF). The last record in the file may or may not have an ending line break.
  • The first line of the CSV file is a header line. The header line lists the column names separated by the field separator (e.g. col1,col2,col3).
  • The field separator defaults to the comma character (,). You can specify a different field separator character with the field_sep connection parameter or with the teradata_field_sep escape function. The specified field separator character must match the actual separator character used in the CSV file.
  • Each field can optionally be enclosed by the field quote character, which defaults to the double-quote character (e.g. "abc",123,efg). You can specify a different field quote character with the field_quote connection parameter or with the teradata_field_quote escape function. The field quote character must match the actual field quote character used in the CSV file.
  • The field separator and field quote characters cannot be set to the same value. The field separator and field quote characters must be legal UTF-8 characters and cannot be line feed (\n) or carriage return (\r).
  • Field quote characters are only permitted in fields enclosed by field quote characters. Field quote characters must not appear inside unquoted fields (e.g. not allowed ab"cd"ef,1,abc).
  • To include a field quote character in a quoted field, the field quote character must be repeated (e.g. "abc""efg""dh",123,xyz).
  • Line breaks, field quote characters, and field separators may be included in a quoted field (e.g. "abc,efg\ndh",123,xyz).
  • Specify a NULL value in the CSV file with an empty value between commas (e.g. 1,,456).
  • A zero-length quoted string specifies a zero-length non-NULL string, not a NULL value (e.g. 1,"",456).
  • Not all data types are supported. For example, BLOB, BYTE, and VARBYTE are not supported.
  • A field length greater than 64KB is transmitted to the database as a DEFERRED CLOB for a SQL batch insert. A field length greater than 64KB is not supported with FastLoad.

Limitations when using CSV batch inserts:

  • Bound parameter values cannot be specified in the execute method when using the escape function {fn teradata_read_csv(CSVFileName)}.
  • The CSV file must contain at least one valid record in addition to the header line containing the column names.
  • For FastLoad, the insert operation will fail if the CSV file is improperly formatted and a parser error occurs.
  • For SQL batch insert, some records may be inserted before a parsing error occurs. A list of the parser errors will be returned. Each parser error will include the line number (starting at line 1) and the column number (starting at zero).
  • Using a CSV file with FastLoad has the same limitations and is used the same way as described in the FastLoad section.