How to convert results in csv?

Question

How to convert results in csv?

I connect to Oracle and get a ResultSet

ResultSet rs ...

I'm trying to convert a ResultSet to CSV. I use openCSV API

 CSVWriter wr = new CSVWriter(new FileWriter("Report.csv"), ','); wr.writeAll(rs, true); wr.flush(); wr.close(); ( "Report.csv"), ','); CSVWriter wr = new CSVWriter(new FileWriter("Report.csv"), ','); wr.writeAll(rs, true); wr.flush(); wr.close();

however, every time I do this, data is added. But I need it to be overwritten. How can I overwrite a file.

Is there a better way?

+6

java oracle

Andrei Vasilev Mar 16 '14 at 16:48

source share

3 answers

established

  wr.writeAll(rs,true);

Using

  wr.writeAll(rs);

+3

ashokramcse Mar 16 '14 at 16:52

source share

It's clean, the Java method without any external API. All comments are included.

 public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } in UTF8 public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } f)) { public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } new OutputStreamWriter (os, "UTF- public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } be skipped public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } is reached public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } ) { public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } names public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } will be written public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } } count public void rsToCSV(ResultSet rs, File f, boolean columnNames) throws SQLException, FileNotFoundException, IOException{ //get metadata from ResultSet ResultSetMetaData metaData = rs.getMetaData(); //get number of columns from metadata int columnCount = metaData.getColumnCount(); //write 3 bytes to output stream to encode csv file in UTF8 try (OutputStream os = new FileOutputStream(f)) { os.write(239); os.write(187); os.write(191); //initialize PrintWriter class try (PrintWriter pw = new PrintWriter(new OutputStreamWriter(os,"UTF-8"))) { //if columnNames is true column names will be written in csv file //if columnNames is false column names will be skipped if(columnNames){ //this will loop until number of columns is reached for(int i = 1; i<=columnCount; i++){ //this writes column name to csv file pw.print(metaData.getColumnName(i)); //if i is less then column count //comma will be written to separate column names if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } //while result set holds rows while (rs.next()){ //this will loop until column count is reached for (int i = 1; i <=columnCount; i++){ //data from column of index i is written to csv pw.print(rs.getObject(i)); //if columns index is less then column count //comma will be written to separate data if(i<columnCount){ pw.print(","); pw.flush(); } //if column count is reached //new line will be written if(i==columnCount){ pw.println(); pw.flush(); } } } } } }

+3

Zoka Jun 27 '16 at 18:43

source share

Alex · Accepted Answer · 2014-03-16T16:54:51+0000

As I mentioned in the comments, you can directly write data

 puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); } args) { puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); } is puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); } throws SQLException, FileNotFoundException { puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); } ( "whatever.csv")); puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); } meta.getColumnName ( puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); } + meta.getColumnName (i) .replaceAll (" \ "", "\\\" ") +" \ ""; puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); } rs.getString ( puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); } "\\\" ") +" \ ""; puclic static void main (Stirng [] args) { ResulSet rs = ... // whatever your code is convertToCsv ( rs ) ; } public static void convertToCsv(ResultSet rs) throws SQLException, FileNotFoundException { PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ; 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).replaceAll("\"","\\\"") + "\"" ; for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) { row += ",\"" + rs.getString(i).replaceAll("\"","\\\"") + "\"" ; } csvWriter.println(row) ; } csvWriter.close(); }

This will overwrite the file, and you do not have to invoke a third-party API to only one method

How to convert results in csv?

More articles: