DWHPro Insights

Effortlessly Loading a CSV File into Teradata using Python

By Roland Wenzlofsky ·

Effortlessly Loading a CSV File into Teradata using Python

Loading a text file into a Teradata database can be arduous without the use of intricate scripts. However, generating TPT scripts or depending on ETL tools like Informatica, AB Initio, and Datastage can be a protracted process. A more straightforward alternative is to utilize Teradata in conjunction with Python. This article will show how to load a CSV file into a Teradata database with minimal code. It is important to mention that this approach is most suitable for smaller files as fastload or multiload protocols will not be utilized.

Preparing the CSV test file

We generate a testing table consisting of three columns with INTEGER data types, then proceed to import the subsequent CSV file.

1,2,3
4,5,6
7,8,9
10,11,12

The Python Script

We begin by importing the CSV file into a temporary Teradata table, which is immediately reread. The data is retrieved as a list, where each row contains several easily accessible columns within the list element.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

This small-file example reads the complete CSV into memory. It maps blank numeric fields to NULL, validates numeric text, optionally skips a declared header and avoids submitting an empty batch. Supply credentials privately through the process environment. The database fixture validated parsing and inserts, including quoted numbers; it did not validate Jupyter or Windows installation.

import csv
import teradatasql
import csv
import os
import teradatasql

has_header = False  # Set True only when this input has a header.
with open('test.csv', newline='', encoding='utf-8') as f:
    reader = csv.reader(f)
    if has_header:
        next(reader, None)
    rows = []
    for fields in reader:
        if len(fields) != 3:
            raise ValueError('Expected three CSV fields')
        rows.append([None if value.strip() == '' else int(value) for value in fields])

with teradatasql.connect(host=os.environ['TD_HOST'],
                         user=os.environ['TD_USER'],
                         password=os.environ['TD_PASSWORD']) as con:
    with con.cursor() as cur:
        cur.execute('CREATE VOLATILE TABLE TestTable (num1 INTEGER, num2 INTEGER, num3 INTEGER) ON COMMIT PRESERVE ROWS')
        if rows:
            cur.execute('INSERT INTO TestTable (?, ?, ?)', rows)
        cur.execute('SELECT num1, num2, num3 FROM TestTable ORDER BY 1,2,3')
        for row in cur.fetchall():
            print(row)
teradata with python
The above figure shows the same script being executed in Jupyter Notebook

Building or improving a data pipeline?

Discuss ingestion, transformations, orchestration, quality checks and operational handover.

Explore data engineering services

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “Effortlessly Loading a CSV File into Teradata using Python”

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam