Ricebridge Java Components - user friendly and well documented

CSV Manager is a Java component that makes it easy to save and load text files containing data in a CSV (Comma Separated Values) format. This includes not only Excel files saved as CSV, but also any plain text format with separated data fields, such as tab separated data and the UNIX passwd file.

You use CSV Manager by calling the methods of the com.ricebridge.csvman.CsvManager class. This is the main entry point to the CSV Manager component. This guide will show you some simple examples of how to use the CsvManager class.

Update Your Classpath

For installation instructions you should read the Installation Guide. Assuming you have unzipped the CSV Manager download file into a folder of your choice, simply copy the csvman.jar jar file, found in the lib folder of the distribution, into a folder in your CLASSPATH. Then add a reference to csvman.jar in your CLASSPATH. This ensures that the classes in csvman.jar can be found by your Java program. Depending on your development environment, you may need to alter your CLASSPATH setting in your IDE.

Loading CSV Data

To load CSV data using CSV Manager, you create a new instance of the CsvManager class and call one of its load methods. The data is then returned as a Java data structure. You have four built-in data structures to choose from: a List of String[] arrays, a List of Lists of Strings, a ResultSet, and a TableModel. We will use the simplest data structure, a List of String[] arrays.

Here is a complete code example. You can try this example out by compiling the ExampleOne.java file in the doc folder of the distribution (the folder that contains this guide).

ExampleOne.java


import com.ricebridge.csvman.CsvManager;
import java.util.*;
import java.io.*;

public class ExampleOne {

  public static void main( String[] args ) throws Exception {
    CsvManager   csvManager = new CsvManager();
    List         data       = csvManager.load( "example.csv" );
    StringBuffer htmlTable  = new StringBuffer("<html><body><table>\n");

    for( int line = 0; line < data.size(); line++ ) {
      htmlTable.append( "<tr>\n" );

      String[] fields = (String[]) data.get(line);
      for( int field = 0; field < fields.length; field++ ) {
        String celltype = (0==line?"th":"td");
        htmlTable.append( "<"+celltype+" align=\"left\">"+fields[field]+"</"+celltype+">\n" );
      }

      htmlTable.append( "</tr>\n" );
    }
    htmlTable.append( "</table></body></html>\n" );

    FileWriter htmlFile = new FileWriter( "example.htm" );
    htmlFile.write( htmlTable.toString() );
    htmlFile.close();
  }
}

This class generates a HTML table (saved to the file example.htm) from the CSV data in the file example.csv. The following lines show how CsvManager is used:

All you need to do to load CSV data is to create a new CsvManager and call its load method with the name of the CSV file.

The CSV data is returned to you as a List of String[] arrays. Each element of the List corresponds to each data line in the CSV file and each data field corresponds to an element in the String[] array. This is the simplest and most basic data structure for your CSV data. However you can also get your data as a List of Lists of Strings, as a java.sql.ResultSet, and as a javax.swing.table.TableModel.

To load your data from a file, CsvManager provides two options; you can specify the file as a java.io.File object, with CsvManager.load(File) or, if it is more convenient, as a simple Java String containing the file path, with CsvManager.load(String).

Saving CSV Data

To save CSV data using CSV Manager, you need an instance of the CsvManager class, and you call one its the save methods. The CSV data is then output to a file. You can provide the CSV data using a List of String[] arrays, a List of Lists of Strings, a ResultSet, and a TableModel. For our example, we will use the simplest data structure, a List of String[] arrays.

Here is a complete code example. You can try this example out by compiling the ExampleTwo.java file in the doc folder of the distribution (the folder that contains this guide).

ExampleTwo.java


import com.ricebridge.csvman.CsvManager;
import java.util.*;
import java.io.*;

public class ExampleTwo {

  public static void main( String[] args ) throws Exception {
    CsvManager csvManager = new CsvManager();

    // create some test data to save
    List data = new ArrayList();
    data.add( new String[] {"Integer", "Even", "Odd", "Square"} );
    for( int line = 0; line < 10; line++ ) {
      String[] fields 
        = new String[] { ""+line, ""+(line*2), ""+(line*2+1), ""+(line*line) };
      data.add( fields );
    }

    csvManager.save( "integers.csv", data );
  }
}

This class creates a CSV file (integers.csv) based on some simple formulas. The following lines show how CsvManager is used:

All you need to do to save CSV data is to use a CsvManager and call its save method with the name of the CSV file, and a List containing the CSV data.

You provide the CSV data as a List of String[] arrays. Each element of the List corresponds to each data line in the CSV file and each data field corresponds to an element in the String[] array. This is the simplest and most basic data structure for your CSV data. However you can also provide your data as a List of Lists of Strings, as a java.sql.ResultSet, and as a javax.swing.table.TableModel.

Importing and Exporting CSV Data from a Database

One of the most practical applications of the CSV file format is to transfer table data between databases in a platform-independent manner. CSV Manager makes this really easy. For this example, assume we have a database table (product.sql) defined by:

product.sql


CREATE TABLE product ( 
id    integer primary key, 
code  varchar(20), 
name  varchar(255), 
price integer 
);

products.csv


id,code,name,price
1,A01,Apple,99
2,O01,Orange,149
3,O02,Satsuma,219
4,B01,Banana,29
5,P01,Pear,129

We want to import this data into the database table product and then export the data in the product table to the file products-export.csv. If it all works correctly, the data in products.csv will match the data in products-export.csv. Here is the code:

ExampleThree.java


import com.ricebridge.csvman.CsvManager;
import java.sql.*;
import java.util.*;
import java.io.*;

public class ExampleThree {

  public static void main( String[] args ) throws Exception {
    CsvManager csvManager = new CsvManager();

    // edit these for your local database
    String dburl 
      = "jdbc:mysql://localhost/mydatabase?"
      + "user=username;password=password";
    Driver d 
      = (Driver) Class.forName( "org.gjt.mm.mysql.Driver" ).newInstance(); 
    DriverManager.registerDriver( d );
    Connection con = DriverManager.getConnection(dburl);

    // import data into database (first line is column names, so start from 1)
    PreparedStatement ps = con.prepareStatement( "INSERT INTO Product VALUES (?,?,?,?)" ); 
    List data = csvManager.load( "products.csv" );
    for( int line = 1; line < data.size(); line++ ) {
      String[] fields = (String[]) data.get(line);
      ps.setInt(    1, Integer.parseInt( fields[0] ) );
      ps.setString( 2, fields[1] );
      ps.setString( 3, fields[2] );
      ps.setInt(    4, Integer.parseInt( fields[3] ) );
      ps.executeUpdate();
    }

    // export data from database
    ps = con.prepareStatement( "SELECT * FROM Product" ); 
    ResultSet rs = ps.executeQuery();
    csvManager.save( "products-export.csv", rs, true );
  }
}

This class imports the data from products.csv into the database and the exports it out again to products-export.csv. The following lines show how CsvManager is used:

    CsvManager csvManager = new CsvManager();
    ...
    List data = csvManager.load( "products.csv" );
    ...
    csvManager.save( "products-export.csv", rs, true );

So to convert data in a database to and from CSV format, all you need to do is use the standard JDBC API calls and the load(File) and save(File,ResultSet,boolean) methods. The boolean parameter on the save method tells CSV Manager to include the column headers as the first line of the CSV file.

Setting the CSV File Format

CSV Manager uses the Excel CSV file format by default. While this covers most CSV files, you may encounter files that use a different format. CSV Manager has a number of settings for files that use a format similar to CSV, and various settings can be altered to suit the file in question.

The most common setting is for the separator characters - the characters that separate data fields. By default this is a comma (,) , but you will encounter files that use tabs, colons (:), semi-colons (;), or even pipes (|). In fact, any character or set of characters could be used as a separator. With the setSeparator(String) method you can set the exact separator character that you need. This can even be a list of characters, so that data fields can be separated by different characters.

Another important issue relates to end-of-line characters on different platforms. CSV Manager will handle Windows, UNIX and Mac files out of the box, but you may wish to make the end-of-file characters explicit using setEndOfFile(String). In particular, this setting can help with unusual end-of-file characters, such as control characters or special data formats.

Data fields that contain separator, end-of-line or quote characters must be handled carefully. By default, any such data fields are enclosed in quotes ("), and any quotes inside the data fields are doubled (as in SQL), to represent one quote. End-of-line characters are not changed. Dealing with end-of-line characters inside quoted data fields is one reason why the CSV format is more complex that it first appears. While quoting is the norm, another acceptable variation is to escape these troublesome characters, usually with a preceding backslash. In this case quoting may not be necessary. CSV Manager allows you to handle all these variations.

More Information

This Getting Started guide only covers the basics of what you can do with CSV Manager. For more information on settings, refer to the reference documentation. For a list of examples, including a Swing example with JTable, see the examples page. For a detailed description of any feature or method, check out the API documentation. The API documentation also includes the source code for a number of classes in the system, as part of the Ricebridge Open Source API policy. This source code will help you to understand the system faster and help you to develop your own applications by providing definitive examples. Finally, visit the Ricebridge website for up-to-date information about CSV Manager.

Please review the licensing terms and conditions under which the Ricebridge CSV Manager component can be used. We're always happy to come up with licensing solutions that suit your needs, so please contact us by mail at sales@ricebridge.com if our existing licensing system isn't quite what you need.

Thanks for using CSV Manager! If you have any problems or questions feel free to mail us at csvman@ricebridge.com.

Tutorials

CSV Manager

Ratings

Quality

Recommendations

Questions

Bookmarks