Posted In: Java Core, String

How to read and parse CSV file using Apache Commons CSV

Splitting a comma-separated string but ignoring commas in quotes

Maven commons-csv

<dependency>
	<groupId>org.apache.commons</groupId>
	<artifactId>commons-csv</artifactId>
	<version>1.4</version>
</dependency>

Example csv

20MICRONS,EQ,34.7,34.7,33.9,34,34.2,33.7,15592,532530.3,24-AUG-2017,158,INE144J01027,
3IINFOTECH,EQ,3.9,3.95,3.9,3.95,3.95,3.9,1009743,3967754.5,24-AUG-2017,303,INE748C01020,

Example csv

"20MI,CRONS",EQ,34.7,34.7,33.9,34,34.2,33.7,15592,532530.3,24-AUG-2017,158,INE144J01027,
549,MF,17,17,17,17,17,16.5,1,17,24-AUG-2017,1,INF903J01O26,
63MOONS,EQ,62,62.55,60.8,61.15,61,62.05,56605,3468324.7,24-AUG-2017,661,"INE111,B01023",

commons csv import

import java.io.FileReader;
import java.io.Reader;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;
import org.junit.Test;

With element index

	@Test
	// With element index
	public void commonsParser1() {
		System.out.println("\ncommonsParser1 started");
		try {
			Reader in = new FileReader("E:/files/cm24AUG2017bhav.csv");
			Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in);
			for (CSVRecord record : records) {
				String SYMBOL = record.get(0);
				String ISIN = record.get(12);
				System.out.println(SYMBOL + "," + ISIN);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

With header element name

	@Test
	// With header element name
	public void commonsParser2() {
		System.out.println("\ncommonsParser2 started");
		try {
			Reader in = new FileReader("E:/files/cm24AUG2017bhav.csv");
			Iterable<CSVRecord> records = CSVFormat.EXCEL
			        .withHeader("SYMBOL", "SERIES", "OPEN", "HIGH", "LOW",
			                "CLOSE", "LAST", "PREVCLOSE", "TOTTRDQTY",
			                "TOTTRDVAL", "TIMESTAMP", "TOTALTRADES", "ISIN")
			        .parse(in);
			for (CSVRecord record : records) {
				String SYMBOL = record.get("SYMBOL");
				String ISIN = record.get("ISIN");
				System.out.println(SYMBOL + "," + ISIN);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

With header name and with commas in quotes

	@Test
	// With header name and with commas in quotes
	public void commonsParser3() {
		System.out.println("\ncommonsParser3 started");
		try {
			Reader in = new FileReader("E:/files/cm24AUG2017bhav1.csv");
			Iterable<CSVRecord> records = CSVFormat.EXCEL
			        .withHeader("SYMBOL", "SERIES", "OPEN", "HIGH", "LOW",
			                "CLOSE", "LAST", "PREVCLOSE", "TOTTRDQTY",
			                "TOTTRDVAL", "TIMESTAMP", "TOTALTRADES", "ISIN")
			        .parse(in);
			for (CSVRecord record : records) {
				String SYMBOL = record.get("SYMBOL");
				String ISIN = record.get("ISIN");
				System.out.println(SYMBOL + "," + ISIN);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

With header name RFC4180 format

@Test
// With header name
public void commonsParser4() {
	System.out.println("\ncommonsParser4 started");
	try {
		Reader in = new FileReader("E:/files/cm24AUG2017bhav.csv");
		Iterable<CSVRecord> records = CSVFormat.RFC4180
				.withHeader("SYMBOL", "SERIES", "OPEN", "HIGH", "LOW",
						"CLOSE", "LAST", "PREVCLOSE", "TOTTRDQTY",
						"TOTTRDVAL", "TIMESTAMP", "TOTALTRADES", "ISIN")
				.parse(in);
		for (CSVRecord record : records) {
			String SYMBOL = record.get("SYMBOL");
			String ISIN = record.get("ISIN");
			System.out.println(SYMBOL + "," + ISIN);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
}

Tags:

by , on August 29th, 2017

  • Categories