Create CSV file in Java Example Program

In this tutorial, we will provide a simple Java program that will generate a CSV file from a Oracle table in the database. For testing purposes, we will use the "department" table in the HR schema and I  expect my file to contain 27 rows of data. 

To generate a CSV file in Java, we will use a library openCSV. When you look at the sample program, you will really acknowledge the simplicity this library brings to the CSV generation process. (and it is open source as well). So, make sure you download this library and have a copy of opencsv-2.3.jar file in your classpath. This is required for the Java example to work.

The complete code that generates the CSV output based on a table is provided below: (note that I have added comments extensively in the code where appropriate, so that you can understand how the code works better)

 1 import java.sql.*; /* The class files under this package helps to pull data out from Oracle table */
 2 import java.io.FileWriter; /* Required for the CSV output generation */
 3 import au.com.bytecode.opencsv.CSVWriter; /* OpenCSV writer class that creates the CSV file output */
 4 class table2csv {
 5 public static void main (String[] args) throws Exception
 6    {
 7     Class.forName ("oracle.jdbc.OracleDriver"); //Oracle driver as our database is Oracle. You have to change it suitably depending on your database type.
 8  
 9     Connection conn = DriverManager.getConnection
10       ("jdbc:oracle:thin:@//localhost:1521/XE", "hr", "hr"); //login credentials to the database
11      
12     try {
13       Statement stmt = conn.createStatement();
14       try {
15         ResultSet query_set = stmt.executeQuery("select * from departments"); //fetch all the rows from the department table
16          try {          
17           FileWriter my_csv=new FileWriter("out.csv");
18           CSVWriter my_csv_output=new CSVWriter(my_csv); //Create a writer object
19           boolean includecolumnnames=true;
20           my_csv_output.writeAll(query_set,includecolumnnames); //write output by directly reading the Resultset, include column names in report
21           my_csv_output.close();
22         } 
23         finally {
24            try { query_set.close(); } catch (Exception ignore) {}
25         }
26       } 
27       finally {
28         try { stmt.close(); } catch (Exception ignore) {}
29       }
30     } 
31     finally {
32       try { conn.close(); } catch (Exception ignore) {}
33     }
34    }
35  }

This code will be very useful in automating any operational reports where you want to generate a CSV output based on a SQL table or SQL view in the DB. I have provided a screenshot of the output file generated by this code;

CSV Output generated Java Program

OpenCSV library also provides quite a number of options while generating the output file. These options are highly useful in customizing the output to suit to your requirements. We will discuss each of these options in the next post.