Load Table Data from a File (Teradata, Aster) | Teradata Studio/Studio Express - Loading Table Data from a File (Teradata, Aster) - Teradata Studio

Teradata® Studio™ User Guide - 17.20

Product
Teradata Studio
Release Number
17.20
Published
September 2022
Language
English (United States)
Last Update
2022-09-23
dita:mapPath
gvw1648839236371.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2041
Product Category
Teradata Tools and Utilities
Use the Load Data Wizard to load data from an input file into a table using either JDBC FastLoad or batch operations. The input file can be a delimited text file or an Excel file. If the table is not empty, the Load Data Wizard appends or replaces the existing data.
JDBC FastLoad operations are not applicable to Aster data transfers.
  1. Open the Data Transfer perspective.
  2. Select a Teradata or Aster table in the Data Source Explorer as the destination table.
  3. Right-click the table and select Data > Load Data.
  4. In the Data Transfer Wizard, select External File as the Source Type.
  5. Click Launch.
  6. At Input File, type the directory path and name of the file that contains the data you want to import.
    To find a file in your file system, click Browse, select the file, and click Open.
  7. In File Type, select one of the following options:
    Option Description
    Delimited Text Text file that uses delimiters to separate data; such as, commas or semicolons in a TXT or CSV file.
    Excel (.xlsx) Microsoft Excel file with.xlsx file extension. This is not available for Hadoop.
    Excel 97-2003 (.xls) Microsoft Excel file with.xls file extension. This is not available for Hadoop.
  8. [Optional] Select Column Labels in First Row to use the first row of data as the column labels for the new table.
  9. Select Stop Loading If Max Error(s) Occur and specify the maximum number of errors allowed before the Load operation stops.
  10. [Optional] Select Lock Table to lock the source and destination tables during the Load operation.
  11. [Optional] Select Replace Existing Data to specify that data in the source file replaces any existing data in the destination file.
  12. [Optional] Select Include Null Rows to include null rows in the table.
  13. Select Use System Locale to use the formatting for date, timestamp, digit grouping symbol, and decimal symbol based on the country and language setting for your operating system. A preview of the format is displayed.
  14. Complete the File Options.
    For example, if you selected Delimited Text, complete the following fields:
    1. In Column Delimiter, select the delimiter that you want used to separate data.
      • Comma – Uses commas (,) to separate data in destination file
      • Semicolon – Uses semicolons (;) to separate data in destination file
      • Space – Uses blank spaces to separate data in destination file
      • Tab – Uses Tabs to separate data in destination file
      • Fixed Width – Uses fixed width columns to separate data. Specify in Fixed Width Columns a numeric value for the fixed column width.
      • I – Inserts a vertical bar (I) to separate data
    2. Select the Character String Delimiter to use to distinguish string text.
      The options are " (double-quotes), ' (single quote), or None.
    3. In Line Separator, select one of the following options based on the operating system from which the data is imported. This option enables you to import files with only Carriage Returns (CR), only LineFeeds (LF), or both Carriage Returns and LineFeeds (CR and LF).
      • Windows OS (CR and LF)
      • Prior to Apple macOS 10.0 (CR)
      • Apple macOS 10.0 and after (LF)
    4. In File Encoding, select the character set to use to encode the data.
      The options are: UTF-8, utf-16, ISO-8859-1, UTF-16BE, UTF-16LE, US-ASCB, Cp1252.
    5. In Start Loading At Row Number, type the table row number at which you want the wizard to start loading data.
      This option does not apply to Hadoop.
      A sample of the input file based on the options you select displays under the File Options. If you change the values selected in File Options fields, the sample row display also changes.
  15. Click Finish.

    The load job starts, and data is loaded to the table.

    [Teradata and Aster]
    • If there is a column mismatch in the data load process and you did NOT set the Data Utilities preference to be prompted in case of column(s) mismatch, then the data load process is completed without any user interaction.
    • If there is a column mismatch in the data load process and you set the Data Utilities preference to be prompted in case of column(s) mismatch, then do one of the following to complete the data load process:
      Option Action
      Click OK.
      • If the file rows contain fewer columns than table columns, then nulls will be added to remaining columns while processing those specific rows.
      • If the file rows contain more columns than table columns, then those extra columns will be removed starting from the end of row while processing those specific rows.
      • If the file has rows where the datatype of a column does not match with table columns datatype, then those rows will be ignored during processing.
      Click Cancel.
      • The rows up to the first error row will be stored in the table.
      • An exception is logged in teradata.log stating you selected Cancel
      • The transfer process is stopped.
    The wizard returns you to the Transfer History View where you can view the report on the Load operation success or failure. Click the Messages tab to view detailed messages.