Creating Tables with Smart Load (Teradata, Aster) - Teradata Studio

Teradata Studio User Guide

Product
Teradata Studio
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
sbq1486739977986.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
    The Data Transfer Wizard displays.
  3. In Source Type, select External File (Smart Load).
  4. Click Launch. The Smart Load Wizard opens.
  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 87-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] 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.
  9. [Optional] Select Include Null Rows to include null rows in the table.
  10. 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.
  11. [Optional] If you selected Delimited Text, select Quick Scan to scan the file partially when trying to derive a table from the imported data.
  12. 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
  13. 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.
  14. 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 Mac OS X v10.0 (CR)
    • Unix, Linux, Mac OS X v10.0 and above (LF)
  15. 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.
  16. Click Next. The Table Column Data Types screen displays.
  17. In Table Name, type the name of the table to be derived from the loaded data.
  18. [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.
  19. Select the Table Type.
  20. To edit the data types of a specific column, click (ellipses) in the column header.
  21. 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.