Export Teradata table to CSV easily in Java

By | 12th July 2019

In this post, we will see how we can connect to Teradata using the JDBC connection in Java using the credentials and copy the contents of the table to a CSV file.

JDBC stands for Java Database Connector which is nothing but a set of libraries that will have supported functions to communicate with Teradata API.

The JDBC library will be different for each RDBMS that we want to connect from Java.

The JARS(libraries) used in this code to connect Teradata are tdgssconfig.jar and terajdbc4.jar. The below-given java program will help us to export Teradata table to a CSV file.

Export Teradata table to CSV

package com.tera.executor;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class executor {

	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
		
		
		try {
			String connurl="jdbc:teradata://your_teradata_server_name";

			Class.forName("com.teradata.jdbc.TeraDriver");
			Connection conn=DriverManager.getConnection(connurl, "username", "password");
			String query="select top 5 * from databaseName.tblName";
			
			PreparedStatement stmt=conn.prepareStatement(query);
			ResultSet rs=stmt.executeQuery();
						
			String outputFile = "C:\\TeraExports\tblName.csv";
			resultToCsv(rs, outputFile);
			System.out.println("Output file: " + outputFile + " is created successfully");
			} 
			catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		

	}
	
	public static void resultToCsv(ResultSet rs, String outputFile) throws SQLException, FileNotFoundException {
 
        PrintWriter csvWriter = new PrintWriter(new File(outputFile)) ;
        ResultSetMetaData meta = rs.getMetaData() ; 
        int numberOfColumns = meta.getColumnCount() ; 
        String dataHeaders = "\"" + meta.getColumnName(1) + "\"" ; 
        for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { 
                dataHeaders += ",\"" + meta.getColumnName(i).replaceAll("\"","\\\"") + "\"" ;
        }
        csvWriter.println(dataHeaders) ;
        while (rs.next()) {
            String row = "\"" + rs.getString(1).trim().replaceAll("\"","\\\"") + "\""  ; 
            for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) {
                row += ",\"" + rs.getString(i).trim().replaceAll("\"","\\\"") + "\"" ;
            }
        csvWriter.println(row) ;
        }
        csvWriter.close();
    }

}

In the above piece of code, the main place where the connection with Teradata is established at,

Connection conn = DriverManager.getConnection(url, username, password);

Pay attention to the classes PreparedStatement , ResultSetMetaData and ResultSet.

PreparedStatement – This class is used to pass the query to the connection object

ResultSetMetaData – This class is used to get the Meta Information about the ResultSet. The main usage of this class is to fetch the column names from the ResultSet

ResultSet – This is the object type that will be returned when we run the query

The function “resultToCsv” is where the logic to export Teradata table to CSV file is written. It will receive the result set and the output file name as arguments.

Also check, “Exporting Oracle table to CSV“, “Parsing SQL Select query using Java