How to Import Data into Teradata via Python?

You will have to manipulate the data resides in multiple platforms when you do the data analysis, machine learning, reporting, etc. We are going to discuss how to leverage Python to upload the data in MS Excel and MS Access into Teradata.

Before we jump into the main topic, you need to have a file udaExec.ini stored in the same folder of py code and this file include your connection credential. Please ensure your name of ODBC setup is “Teradata”.

# Application Configuration
 [CONFIG]
 appName=CSVtry
 version=1.0
 logConsole=False
 dataSourceName=Teradata
  
 # Default Data Source Configuration
 [DEFAULT]
 method=odbc
 charset=UTF8
  
 # Data Source Definition
 [Teradata]
 dsn=Teradata
 username= informula
 password= passcode

MS Excel into Teradata

• Required package: teradata/ pandas
• Key Parts:

1. Define path of Excel
2. Read the data of the defined path
3. Define connection of Teradata
4. Delete the content of target table in Teradata
5. Insert data into target table in Teradata

• Full Py code

import teradata
import pandas as pd
path = "‪C:/Excel.xlsx"
file = pd.ExcelFile(path)
for name in file.sheet_names:
 if 'Sheet1' in name:
  sh_name = name
df = pd.read_excel(file,  sheet_name = sh_name)
df = df[['Column1']].fillna('0')
udaExec = teradata.UdaExec(appName='${appName}', version='${version}', logConsole='${logConsole}')
with udaExec.connect('${dataSourceName}') as session:
 session.execute("""delete from Datalab.Table1""")
 for row in df.to_records(index = False):
  ExcelDf = []
  ExcelDf.append(str(row[0]))
  session.execute(""" Insert Into Datalab.Table1 ("Column1")
                      VALUES (?) """, ExcelDf)
print("uploaded")

MS Access into Teradata

• Required package: teradata/ pandas/ pyodbc
• Key Parts:

1. Establish the connection of MS Access with defined path
2. Select the table from the imported table in Access
3. Define connection of Teradata
4. Delete the content of target table in Teradata
5. Insert data into target table in Teradata

• Full Py code

import pyodbc
import teradata
import pandas as pd
Access_connection = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=‪C:/Access.accdb;')
cursor = Access_connection.cursor()
cursor.commit()
df = pd.read_sql('select * from Table_Access', Access_connection)
udaExec = teradata.UdaExec(appName='${appName}', version='${version}', logConsole='${logConsole}')
with udaExec.connect('${dataSourceName}') as session:
 session.execute("""delete from Datalab.Table2""")
 for row in df.to_records(index=False):
  session.execute(""" Insert Into Datalab.Table2 ("Column1")
                      VALUES (?) """, (str(row[0]) ))
print("uploaded")

Here are some other our articles for data import & flow across the platforms. Take a look if you are interested in these topics.

Python

• How to Import Data into Snowflake via Python?
• How to Import data into SQLite via Python Part 1 (Json file/ MS Excel/ MS Access) ?

JavaScript

• How to Import Data into MS SQL Server via JavaScript Node JS (Part 1: csv & Excel)?
• How to Import Data into Snowflake via JavaScript Node JS (Part 1: csv & Excel)?
• How to import Notion data into MS SQL Server via JavaScript NodeJS?

R

• How to Import Data into MS SQL Server via R (Part 1: csv, Excel, Snowflake)?

Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)

𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲