Create Tables with Smart Load (Teradata, Aster) | Teradata Studio/Studio Express - Creating Tables with Smart Load (Teradata, Aster) - Teradata Studio

Teradata® Studio™ User Guide

Product
Teradata Studio
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-20
dita:mapPath
rvy1605890559220.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2041
lifecycle
previous
Product Category
Teradata Tools and Utilities
Use this procedure to create a new Teradata or Aster table by importing data from an external file.
  1. In Data Source Explorer right-click Tables on a Teradata or Aster system.
  2. Choose the correct option for your system:
    Option Action
    Teradata Teradata > Data Transfer
    Aster Aster > Data Transfer
  3. In Source Type, select External File (Smart Load).
  4. Click Launch.
  5. In Input File, type the directory path and name of the file that contains data you want to use to build a table. You can also browse to the file.
  6. Select the File Type for the input file:
    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.
    Excel 97-2003 (.xls) Microsoft Excel file with .xls file extension.
  7. [Optional] Select Column Labels in First Row to specify to use the first row of data as the column labels for the new table.
  8. [Optional] Select Stop Loading Data If Max Error(s) Occur to stop data loading if the number of errors you enter occurs.
  9. [Optional] In Drop Table if Max Error(s) Occur, type the maximum number of errors that a table can have before it is dropped from the import operation.
  10. [Optional] Select Include Null Rows to include null rows in the table.
  11. 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.
  12. [Optional] If you selected Delimited Text, select Quick Scan to scan the file partially when trying to derive a table from the imported data.
  13. If you selected Delimited Text, use the list in Column Delimiter to select the delimiter that is used to separate data in the input file.
    Option Description
    Comma Input file data is separated by commas (,)
    Semicolon Input file data is separated by semicolons (;)
    Space Input file data is separated by blank spaces
    Tab Input file data is separated by Tabs
    Fixed Width Input file data has fixed width columns. Specify in the Fixed Width Columns field a numeric value for the fixed column width.
    I Vertical bar (I) is used to separate data
  14. If you selected Delimited Text, select the Character String Delimiter to use to distinguish string text.
    The options are " (double-quotes), ' (single quote), or None. This option does not apply to Hadoop.
  15. If you selected Delimited Text, 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)
    • Unix, Linux, Apple macOS 10.0 and above (LF)
  16. If you selected Delimited Text, 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.
  17. Click Next.
    [Teradata and Aster]
    • If there is a column mismatch in the Smart Load process and you did NOT set the Data Utilities preference to be prompted in case of column(s) mismatch, then Smart Load will be continued without any user interaction.
    • If there is a column mismatch in the Smart 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 Smart Load:
      Option Action
      Click OK. The Smart Load will be continued.
      Click Cancel. The Smart Load fails and the following message appears: Error loading file: Rows in file have a different number of columns.
  18. In Table Name, type the name of the table to be derived from the loaded data.
  19. [Teradata Only] In Primary Index, select whether this table will have a primary index, and whether it is unique.
    The Teradata primary index is used to evenly distribute data among AMPs.A non-unique primary index permits the insertion of duplicate field values.
  20. Select the Table Type.
  21. To edit the data types of a specific column, click "" in the column header.
  22. Select one of the following:
    Option Description
    Click Next Previews the SQL/DDL code that creates the new table. You can edit the SQL statements in the Generated SQL text area by clicking Show in Editor. If you choose to open the SQL Editor, the Smart Load Wizard closes. You can run the SQL query statements from the SQL Editor to create the table.
    Click Finish Creates the table and imports the data from the file.