Importing External File Data to Aster Tables - Teradata Studio

Teradata Studio User Guide

Product
Teradata Studio
Release Number
15.11
Published
May 2016
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
bpb1467322243715.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2041
lifecycle
previous
Product Category
Teradata Tools and Utilities

The following instructions assume that you have the Data Transfer perspective open.

  1. Open the Data Transfer perspective by clicking the Data Transfer button in the toolbar.
  2. Open the Data Source Explorer and navigate to a Tables node in a Teradata Aster Database.
  3. Right-click the Tables folder and select Aster > Data Transfer.
  4. In the Data Transfer Wizard, select External File as the Source Type.
  5. Click Launch. The Load Data Wizard launches.
  6. In Import Data, type the directory path and name of the input 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.
    Excel 87-2003 (.xls) Microsoft Excel file with.xls file extension.
  8. [Optional] Select the Column Labels in First Row check box to specify to use the first row of data as the column labels for the new table.
  9. [Default] Select the Drop Table If Max Error(s) Occur check box and specify the maximum number of errors allowed before the Load operation stops.
  10. [Optional] Select the Quick Scan check box to scan the file partially when trying to derive a table from the imported data. This option is only available for delimited text files, such as TXT and CSV files.
  11. In step 10, if you selected Delimited Text, complete the following File Options 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 the Fixed Width Columns field 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 Mac OS X v10.0 (CR)
      • Unix, Linux, Mac OS X v10.0 and above (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. A sample of the input file based on the options you select displays below the File Options. If you change the values selected in File Options fields, the sample row display also changes.
  12. In step 10, if you selected Excel or Excel 97-2003, complete the following File Options fields.
    1. [Optional] In Import Worksheet, select an individual worksheet in the input Excel file to import.
    2. In Start Loading At Row Number, type the table row number at which you want the wizard to start loading data.
  13. Click Next. The Table Column Data Types screen displays.

  14. In Table Column Data Types specify the name of the table to be derived from the loaded data.
  15. In Table Type select one of the following options:
    Option Description
    DIMENSION Describes the information in fact tables and contains attributes used for grouping, constraining, and reporting fact table data.
    FACT Contains numeric measurements such as aggregates, averages, and summations. Fact tables are surrounded by dimension tables which analyze or dimension the data in a fact table.
  16. You must decide which column to distribute by hash. To designate distribution by hash and to edit the data types of a specific column, click (ellipses) in the column header. For information about editing columns for an Aster database, see Editing Aster Column Data Types.
  17. Choose one of the following options:
    Option Description
    Click Next Enables you to preview the SQL 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 will close. 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.