Export Oracle table to CSV using OpenCSV and Resultset

Question

I'm trying to export oracle table into csv file and I have created a Class doing so but the output file format was as follow:

12345 1002988846 1 Salary is Here 67891 1009007305 0 Ma3ash is Here! 55555 1095003139 0 Ma3ash is Here! 77777 1023456789 1 Salary is Here

and here is the class:

import java.io.*;
import java.sql.*;
public class newway {

public void  mymethod() throws Exception {
try
{
Class.forName("oracle.jdbc.OracleDriver");

Connection conn= DriverManager.getConnection("jdbc:oracle:thin:@172.17.60.225:1521/FRSTEST", "TRASSET", "TRASSET");
conn.setAutoCommit(false);
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery("Select * from Table1");


ResultSetMetaData rsmd = rs.getMetaData();
FileWriter cname = new FileWriter("D:\\asd.csv");
BufferedWriter bwOutFile = new BufferedWriter(cname);
StringBuffer sbOutput = new StringBuffer();
sbOutput.append("S_DATE");
bwOutFile.append(sbOutput);
bwOutFile.append(System.getProperty("line.separator"));
System.out.println("No of columns in the table:"+ rsmd.getColumnCount());

for (int i = 1; i <= rsmd.getColumnCount(); i++) 
{
String  fname = rsmd.getColumnName(i);
}

System.out.println();



while(rs.next())
{
for(int i=1; i<5;i++){  
System.out.print(rs.getString(i));
bwOutFile.append(rs.getString(i));
bwOutFile.append(System.getProperty("line.separator"));
}
bwOutFile.flush();
System.out.println();
} 
conn.close();

}
catch(SQLException se)
{
se.printStackTrace();
}
catch(Exception e)
    {
System.out.println("Unable to connect to database" +e);
}

}


}

I want the output to be separated by comma and each record in a line. Any Help Please?!

System.getProperty("line.separator") is new line. What you want to do is read each column from a single result set and the append it by comma. outside that loop you should use System.getProperty("line.separator") — StackFlowed, Sep 16 '15 at 13:04

score 0 · Answer 1 · answered Sep 25 '15 at 19:15

Where are you using openCSV?

Here is one of the test from CSVWriter in openCSV that tests out writing records from ResultSet. Just modify it to meet your needs.

@Test
public void testResultSetWithHeaders() throws SQLException, IOException {
  String[] header = {"Foo", "Bar", "baz"};
  String[] value = {"v1", "v2", "v3"};

  StringWriter sw = new StringWriter();
  CSVWriter csvw = new CSVWriter(sw);
  csvw.setResultService(new ResultSetHelperService());

  ResultSet rs = MockResultSetBuilder.buildResultSet(header, value, 1);

  int linesWritten = csvw.writeAll(rs, true); // don't need a result set since I am mocking the result.
  assertFalse(csvw.checkError());
  String result = sw.toString();

  assertNotNull(result);
  assertEquals("\"Foo\",\"Bar\",\"baz\"\n\"v1\",\"v2\",\"v3\"\n", result);
  assertEquals(2, linesWritten);
}

Export Oracle table to CSV using OpenCSV and Resultset

1 Answers1