Can anyone recommend a simple API that will allow me to use read a CSV input file, do some simple transformations, and then write it.
A quick google has found http://flatpack.sourceforge.net/ which looks promising.
I just wanted to check what others are using before I couple myself to this API.
I've used OpenCSV in the past.
import au.com.bytecode.opencsv.CSVReader;String fileName = "data.csv"; CSVReader reader = new CSVReader(new FileReader(fileName ));// if the first line is the header String[] header = reader.readNext();
// iterate over reader.readNext until it returns null String[] line = reader.readNext();
There were some other choices in the answers to another question.
Check out Apache Common CSV.
This library reads and writes several variations of CSV, including the standard one RFC 4180. Also reads/writes Tab-delimited files.
Update: The code in this answer is for Super CSV 1.52. Updated code examples for Super CSV 2.4.0 can be found at the project website: http://super-csv.github.io/super-csv/index.html
The SuperCSV project directly supports the parsing and structured manipulation of CSV cells. From http://super-csv.github.io/super-csv/examples_reading.html you'll find e.g.
given a class
public class UserBean {
String username, password, street, town;
int zip;
public String getPassword() { return password; }
public String getStreet() { return street; }
public String getTown() { return town; }
public String getUsername() { return username; }
public int getZip() { return zip; }
public void setPassword(String password) { this.password = password; }
public void setStreet(String street) { this.street = street; }
public void setTown(String town) { this.town = town; }
public void setUsername(String username) { this.username = username; }
public void setZip(int zip) { this.zip = zip; }
}
and that you have a CSV file with a header. Let's assume the following content
username, password, date, zip, town
Klaus, qwexyKiks, 17/1/2007, 1111, New York
Oufu, bobilop, 10/10/2007, 4555, New York
You can then create an instance of the UserBean and populate it with values from the second line of the file with the following code
class ReadingObjects {
public static void main(String[] args) throws Exception{
ICsvBeanReader inFile = new CsvBeanReader(new FileReader("foo.csv"), CsvPreference.EXCEL_PREFERENCE);
try {
final String[] header = inFile.getCSVHeader(true);
UserBean user;
while( (user = inFile.read(UserBean.class, header, processors)) != null) {
System.out.println(user.getZip());
}
} finally {
inFile.close();
}
}
}
using the following "manipulation specification"
final CellProcessor[] processors = new CellProcessor[] {
new Unique(new StrMinMax(5, 20)),
new StrMinMax(8, 35),
new ParseDate("dd/MM/yyyy"),
new Optional(new ParseInt()),
null
};
Reading CSV format description makes me feel that using 3rd party library would be less headache than writing it myself:
Wikipedia lists 10 or something known libraries:
I compared libs listed using some kind of check list. OpenCSV turned out a winner to me (YMMV) with the following results:
+ maven
+ maven - release version // had some cryptic issues at _Hudson_ with snapshot references => prefer to be on a safe side
+ code examples
+ open source // as in "can hack myself if needed"
+ understandable javadoc // as opposed to eg javadocs of _genjava gj-csv_
+ compact API // YAGNI (note *flatpack* seems to have much richer API than OpenCSV)
- reference to specification used // I really like it when people can explain what they're doing
- reference to _RFC 4180_ support // would qualify as simplest form of specification to me
- releases changelog // absence is quite a pity, given how simple it'd be to get with maven-changes-plugin // _flatpack_, for comparison, has quite helpful changelog
+ bug tracking
+ active // as in "can submit a bug and expect a fixed release soon"
+ positive feedback // Recommended By 51 users at sourceforge (as of now)
We use JavaCSV, it works pretty well
For the last enterprise application I worked on that needed to handle a notable amount of CSV -- a couple of months ago -- I used SuperCSV at sourceforge and found it simple, robust and problem-free.
You can use csvreader api & download from following location:
http://sourceforge.net/projects/javacsv/files/JavaCsv/JavaCsv%202.1/javacsv2.1.zip/download
or
http://sourceforge.net/projects/javacsv/
Use the following code:
/ ************ For Reading ***************/
import java.io.FileNotFoundException;
import java.io.IOException;
import com.csvreader.CsvReader;
public class CsvReaderExample {
public static void main(String[] args) {
try {
CsvReader products = new CsvReader("products.csv");
products.readHeaders();
while (products.readRecord())
{
String productID = products.get("ProductID");
String productName = products.get("ProductName");
String supplierID = products.get("SupplierID");
String categoryID = products.get("CategoryID");
String quantityPerUnit = products.get("QuantityPerUnit");
String unitPrice = products.get("UnitPrice");
String unitsInStock = products.get("UnitsInStock");
String unitsOnOrder = products.get("UnitsOnOrder");
String reorderLevel = products.get("ReorderLevel");
String discontinued = products.get("Discontinued");
// perform program logic here
System.out.println(productID + ":" + productName);
}
products.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Write / Append to CSV file
Code:
/************* For Writing ***************************/
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import com.csvreader.CsvWriter;
public class CsvWriterAppendExample {
public static void main(String[] args) {
String outputFile = "users.csv";
// before we open the file check to see if it already exists
boolean alreadyExists = new File(outputFile).exists();
try {
// use FileWriter constructor that specifies open for appending
CsvWriter csvOutput = new CsvWriter(new FileWriter(outputFile, true), ',');
// if the file didn't already exist then we need to write out the header line
if (!alreadyExists)
{
csvOutput.write("id");
csvOutput.write("name");
csvOutput.endRecord();
}
// else assume that the file already has the correct header line
// write out a few records
csvOutput.write("1");
csvOutput.write("Bruce");
csvOutput.endRecord();
csvOutput.write("2");
csvOutput.write("John");
csvOutput.endRecord();
csvOutput.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
There is also CSV/Excel Utility. It assumes all thos data is table-like and delivers data from Iterators.
The CSV format sounds easy enough for StringTokenizer but it can become more complicated. Here in Germany a semicolon is used as a delimiter and cells containing delimiters need to be escaped. You're not going to handle that easily with StringTokenizer.
I would go for http://sourceforge.net/projects/javacsv
If you intend to read csv from excel, then there are some interesting corner cases. I can't remember them all, but the apache commons csv was not capable of handling it correctly (with, for example, urls).
Be sure to test excel output with quotes and commas and slashes all over the place.
