Often we need to quickly load a text file without writing elaborate loading scripts. If we want it to be quick and easy, it is neither useful to design TPT scripts nor to use any ETL tools (Informatica, AB Initio, Datastage, etc.). Teradata with Python is often the simplest choice.

In this article, I will show you how to load any CSV file into a Teradata database with just a few code lines. Nevertheless, this example is intended for smaller files since no fastload or multiload protocol is used.

Preparing the CSV test file

In our example, we create a test table with 3 columns of data type INTEGER and load the CSV file shown below:


The Python Script

We first load the CSV file into a Teradata volatile table and then read it again immediately. Each row is returned as a list. Each individual column of a row can then be easily accessed via the list element:

I think it can’t get any simpler than shown in our example:

import csv
import teradatasql
with open ('test.csv', newline='') as f:
    with teradatasql.connect ('{"host":"","user":"dbc","password":"dbc"}') as con:
        with con.cursor () as cur:
            cur.execute ("create volatile table TestTable (num1 integer, num2 integer, num3 integer) on commit preserve rows")
            cur.execute ("insert into TestTable (?, ?, ?)", [ row for row in csv.reader (f) ])
            cur.execute ("select num1, num2, num3 from TestTable order by 1,2,3")
            [ print (row) for row in cur.fetchall () ]
Loading a CSV File into Teradata using Python 1
Above figure shows the same script being executed in Jupyter Notebook
Buy now at Amazon
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like