exporting sql query result to a csv or excel

Question

I want to write the result of an sql query to a csv or excel file and save it in a particular folder.I would like to know if this can be achieved using a java program which can be reused for any sql query result.I would also like to know if this can be used for different types of databases(oracle,mysql,MS sql server etc).I plan to attach the saved file to an email(is this possible directly exporting a sql query result to an email).Please help.

Answer 1

For this to work you need to work write a small code that can take up any query and any driver . The first input should be the driver name as the input to the software that you are writing. Then the software you are writing should be in a position to execute any SQL given to it and give out only rows and columns.

The next task comes to parse the ResultSet that comes from the JDBC of java application. Either you want to write the results into CSV file or EXCEL is based on how good you have the java api to do that.

Writing the output into the CVS is easy and not trival. I have not worked on exporting the data into Excel. I am sure you find jars for that.

Answer 2

Yes!

You can connect to the different database types using jdbc and then create an Excel with the results (Seen here).

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestAccessExcel {
  public static Connection getConnection() throws Exception {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String url = "jdbc:odbc:excelDB";
    String username = "username";
    String password = "pass";
    Class.forName(driver);
    return DriverManager.getConnection(url, username, password);
  }

  public static void main(String args[]) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      String excelQuery = "select * from [Sheet1$]";
      rs = stmt.executeQuery(excelQuery);

      while (rs.next()) {
        System.out.println(rs.getString("BadgeNumber") + " " + rs.getString("FirstName") + " "
            + rs.getString("LastName"));
      }
    } catch (Exception e) {
      System.err.println(e.getMessage());
    } finally {
      try {
        rs.close();
        stmt.close();
        conn.close();

      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Answer 3

This is my solution. Code to insert in the main class:

import java.io.*;
import java.sql.*;
import com.company.*;
/**
 * Created by MAXNIGELNEGRO
*/
  String[] filePath =       new String[] {"C:\\Users\\Documents\\MyFile.csv"};
  String[] driverDB =       new String[] {"oracle.jdbc.driver.OracleDriver"};
  String[] stringConnDB =   new String[] {"jdbc:oracle:thin:@//127.0.0.1:1881/mydb"};
  String[] userDB =         new String[] {"pippo"};
  String[] passDB =         new String[] {"pluto"};
  String[] charSep =        new String[] {";"};
  Boolean colomn=   new Boolean (true);
  String[] queryDB =        new String[] {"select * FROM MYQUERY"};


try{
    System.out.println("---------------File exist?------------" + filePath[0]);
    File fileTemp = new File(filePath[0].toString());
    if (fileTemp.exists()){ 
        fileTemp.delete();
        System.out.println("---------------DELETE FILE------------" + filePath[0] );
                } 
   System.out.println("QUERY: ---->"+ queryDB[0].toString());
   exportQueryToCsv exp = new exportQueryToCsv();
   exp.exportQueryToCsv(filePath,driverDB,stringConnDB,userDB,passDB,queryDB, colomn,charSep);
   if (fileTemp.exists()){ 
     System.out.println("---File created---" + filePath[0]);
  }

}
catch(Exception e){
         e.printStackTrace();
      }

The core class:

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by MAXNIGELNEGRO
 */
public class exportQueryToCsv {
    public exportQueryToCsv(){}
    public static void  exportQueryToCsv (String[] filename, String[] driverDB, String[] connDB
                                            , String[] userDB, String[] passDB, String[] queryDB, Boolean intestaFile
                                            , String[] charSep) throws SQLException, IOException {
        Statement stmt=null;
        ResultSet rset=null;
        Connection conn=null;
        try { DBConn connessione = new DBConn();
        conn=connessione.connect(driverDB[0],connDB[0],userDB[0],passDB[0]);
        conn.setAutoCommit(false);

        stmt = conn.createStatement();

        rset = stmt.executeQuery(queryDB[0]);

        ExportData2CSV csv = new ExportData2CSV();
        csv.ExportData2CSV(rset,filename[0],intestaFile,charSep[0]);

            csv.createFileCsv();
        } catch (SQLException e) {
                e.printStackTrace();
        } catch (IOException e) {
                e.printStackTrace();
        }
        finally {
            if (stmt != null) {stmt.close();}
            if (conn != null) {conn.close();}
            if (rset != null) {rset.close();}



        }


    }
}

This is the class DBConn for Connection to database

import java.sql.*;

/**
 * Created by MAXNIGELNEGRO
 */
public class DBConn {
    public DBConn() {
    }
    public Connection connect(String driverDB, String db_connect_str, String db_userid, String db_password) {
        Connection conn;

        try {
            Class.forName(driverDB).newInstance();
            conn = DriverManager.getConnection(db_connect_str, db_userid, db_password);


        } catch (Exception e) {
            e.printStackTrace();
            conn = null;

        }
        return conn;
    }


}

This is the class for retrieves data from table to resultset and writes to csv file

package com.company;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * Created by MAXNIGELNEGRO 
 */
public class ExportData2CSV {
    public ResultSet rset;
    public String filename;
    public Boolean colomnName;
    public String charSep;

    public void ExportData2CSV(ResultSet rset, String filename, Boolean colomnName, String charSep) {
        this.rset = rset;
        this.filename = filename;
        this.colomnName = colomnName;
        this.charSep = charSep;
    }

    public void createFileCsv() throws SQLException, IOException {
        FileWriter cname = null;
        try {

                // WRITE COLOMN NAME
            ResultSetMetaData rsmd = rset.getMetaData();
            cname = new FileWriter(filename);
            if (colomnName) {
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    cname.append(rsmd.getColumnName(i));
                    cname.append(charSep);
                    cname.flush();
                }
                cname.append(System.getProperty("line.separator"));
            }

            // WRITE DATA
            while (rset.next()) {
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    if (rset.getObject(i) != null) {
                        String data = rset.getObject(i).toString().replaceAll(charSep, "");
                        cname.append(data);
                        cname.append(charSep);
                    } else {
                        String data = "null";
                        cname.append(data);
                        cname.append(charSep);
                    }

                }
                //new line entered after each row
                cname.append(System.getProperty("line.separator"));

            }


        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (cname != null) {
                cname.flush();
                cname.close();
            }
            if (rset != null) {
                rset.close();
            }

        }

    }
}

Answer 4

This is the excel data sheet

input excel file containing records Hi this is the solution you need 3 files 1.input thread 2.output thread 3.data structure 4.main 1.input thread to read the excel and output thread to write the sql out put 2.data structure is to hold and transfer the data

(InputThread.java)

    import java.io.*; 
public class InputThread extends Thread{


    String fp; 
    InputString is; 
    String tableName="emp"; 
    String outFile;
    InputThread(String FilePath,String nameOfTheTable,String outFileName){
        fp=FilePath;
        outFile=outFileName;
        tableName=nameOfTheTable;
    }
    public void run(){
        File file = new File(fp);
        String line;
        try{
            BufferedReader br = new BufferedReader(new FileReader(file)); 
            if( (line=br.readLine()) != null)
                is = new InputString(line);

            //transform(is);    

            InputString tmp = new InputString(createTable(line));
            //tmp.next = is;
            is = tmp;
            //tmp = tmp.next;

            for(; (line = br.readLine()) != null; ) {
                tmp.next = new InputString(line);
                tmp = tmp.next;
                transform(tmp); 
                }               

        }catch(Exception e){ System.out.println("Error is :"+e); }

        //traverse();
        new OutputThread(is,outFile).start();
    }
    void transform(InputString x){

        String[] arr = x.getLine().split(",");
        String sql = "insert into "+tableName+" values(";
        for(int i=0;i<arr.length;i++){
            sql+="'"+arr[i]+"'";
            if( (i+1) < arr.length) sql+=",";
        }
        sql+=");";
        x.setLine(sql);

    }
    String createTable(String x){
        String[] arr = x.split(",");
        String sql = "create database vamsidb "+ "use vamsidb "+"create table "+tableName+"(";
        for(int i=0;i<arr.length;i++){
            sql+=arr[i]+" varchar(50)";
            if( (i+1) < arr.length) sql+=",";
        }
        sql+=");";
        return sql;
    }
    /*public void traverse(){
        InputString tmp = is;
        while(is != null){
            System.out.println(is.getLine());
            is=is.next;
        }
    }*/


}

(OutputThread.java)

import java.io.*;
public class OutputThread extends Thread{
    InputString is;
    String outFile;
    OutputThread(InputString linkedList,String outFileName){
        is=linkedList;
        outFile = outFileName;
    }
    public void run(){

        try{
            FileOutputStream fos = new FileOutputStream(outFile);
            while(is != null){              
                fos.write(is.getLine().getBytes());             
                is=is.next;
            }
            fos.close();
        }catch(Exception e){
            System.out.println("Error is :"+e);
         }
    }
}

(Main.java)

public class Main{
public static void main(String[] args){

        InputThread it = new InputThread("sasken.csv","emp","output.sql");

        it.start();     
    }
}

(DataStructure.java)

//This class represents the data structure to hold and transform input //data as a linked list of sql statements

class InputString{

    String line;
    InputString next;

    InputString(String x){
        line = x;
    }
    String getLine(){
        return line;
    }   
    void setLine(String x){
        line = x;
    }
}

output result

Answer 5

You can use JDBC to fetch the records from DB in java and then use Apache POI for exporting the data to CSV/Excel.

Additionally, you can use the desktop API of java to send email using your default email client.

Answer 6

It's difficult to export result set data from any tool.

ex: while exporting result set data to .csv file it does not export properly when data contains (,)

please refer below java code :

it works perfectly with any any query input and all type of data in result set

package com.demo.export;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class dataExportService {
    public void getDefendants(Connection con , String db) throws Exception  { 
        @SuppressWarnings("unused")
        Workbook readWorkbook = WorkbookFactory.create(new FileInputStream(".xls file path(C:\\Users\\CEPL\\Desktop\\test.xls)") );
        @SuppressWarnings("resource")
        Workbook writeWorkbook = new HSSFWorkbook();
        Sheet desSheet = writeWorkbook.createSheet("new sheet");

        Statement stmt = null;
        ResultSet rs = null;
        try{
            String query ="QUERY";

            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();

            Row desRow1 = desSheet.createRow(0);
            for(int col=0 ;col < columnsNumber;col++) {
                Cell newpath = desRow1.createCell(col);
                newpath.setCellValue(rsmd.getColumnLabel(col+1));
            }
            while(rs.next()) {
                System.out.println("Row number" + rs.getRow() );
                Row desRow = desSheet.createRow(rs.getRow());
                for(int col=0 ;col < columnsNumber;col++) {
                    Cell newpath = desRow.createCell(col);
                    newpath.setCellValue(rs.getString(col+1));  
                }
                FileOutputStream fileOut = new FileOutputStream(".xls file path(C:\\Users\\CEPL\\Desktop\\test.xls)");
                writeWorkbook.write(fileOut);
                fileOut.close();
            }
        }
        catch (SQLException e) {
            System.out.println("Failed to get data from database");
        }
    }

}