Examples: Shredding the CSV Data - Analytics Database - Teradata Vantage

Example: Shredding the CSV Data to a Database Table

Convert the CSV data to a DATASET value with the CSV storage format by using CreateDATASET. Specify a schema if the CSV data does not follow the standard CSV format (for example, it has different column or record delimiters). The DATASET_TABLE then shreds the CSV data to a database table. Use DATASET_TABLE to specify each column data type in the CSV value by using the 'column_expression_literal USING specification.

CREATE TABLE empTableFromCSV(
 empID INTEGER, 
 empName VARCHAR(20),
 empSalary INTEGER);

Example: Aligning the CSV Columns and Table Columns

*+csv11.txt+*
empID,empName,empSalary
1,John,100000
2,Sam,50000
3,Mary,75000

.import vartext file csv11.txt
USING (c1 VARCHAR(1000))
INSERT INTO empTableFromCSV 
SELECT * FROM DATASET_TABLE (
 ON (SELECT CreateDATASET(NULL, :c1, CSV))
 USING rowexpr('') 
   colexpr(
  '[ {"dotnotation" : "$..empID",
      "type" : "INTEGER"},
     {"dotnotation" : "$..empName",
              "type" : "VARCHAR(20)"} ,
     {"dotnotation" : "$..empSalary",
              "type" : "INTEGER"} ]')
) AS Emp(empID, empName, empSalary);

SELECT * FROM empTableFromCSV ORDER BY 1;

The resulting table has three rows:

Example: CSV Column and Table Names are the Same, but Do Not Align

*+csv12.txt+*
empID,empSalary,empName
4,100000,Kate
5,50000,Rob
6,75000,Peter

.import vartext file csv12.txt
USING (c1 VARCHAR(1000))
INSERT INTO empTableFromCSV(empID,empSalary,empName) 
SELECT * FROM DATASET_TABLE (
 ON (SELECT CreateDATASET(NULL, :c1, CSV))
 USING rowexpr('') 
   colexpr(
  '[ {"dotnotation" : "$..empID",
      "type" : "INTEGER"},
     {"dotnotation" : "$..empSalary",
              "type" : "INTEGER"} ,
     {"dotnotation" : "$..empName",
              "type" : "VARCHAR(20)"} ]')
) AS Emp(empID, empSalary, empName);

Example: Passing the Schema to CreateDATASET

*+csv13.txt+*
7,Matt,100000
8,Mark,50000
9,Luke,75000

.import vartext file csv13.txt
USING (c1 VARCHAR(1000))
INSERT INTO empTableFromCSV 
SELECT * FROM DATASET_TABLE (
 ON (SELECT CreateDATASET('{"field_names":NULL}':c1, CSV))
 USING rowexpr('')
   colexpr(
  '[ {"dotnotation" : "$..empID",
     "type" : "INTEGER"},
     {"dotnotation" : "$..empName",
              "type" : "VARCHAR(20)"} ,
     {"dotnotation" : "$..empSalary",
              "type" : "INTEGER"} ]')
) AS Emp(empID, empName, empSalary);

Example: Returning All Employees in the Table

SELECT * FROM empTableFromCSV ORDER BY 1;