Posted In: Java Core, String

How to read and parse CSV file using Open CSV

Splitting a comma-separated string but ignoring commas in quotes

Maven opencsv

<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>4.0</version>
</dependency>

Example csv (cm24AUG2017bhav.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 commas in quotes (cm24AUG2017bhav1.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",

opencsv import

import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.RFC4180Parser;
import com.opencsv.bean.ColumnPositionMappingStrategy;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;

With deprecated CSVReader. Use CSVReaderBuilder

@SuppressWarnings("deprecation")
@Test
// With deprecated CSVReader. Use CSVReaderBuilder
public void opencsvParser1() throws Exception {
	System.out.println("\nopencsvParser1 started");
	CSVReader reader = null;
	try {
		reader = new CSVReader(
				new FileReader("E:/files/cm24AUG2017bhav.csv"), ',');
		String[] record = null;
		while ((record = reader.readNext()) != null) {
			String SYMBOL = record[0];
			String ISIN = record[12];
			System.out.println(SYMBOL + "," + ISIN);
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		reader.close();
	}
}

With deprecated CSVReader. Use CSVReaderBuilder. commas in quotes data

@SuppressWarnings("deprecation")
@Test
// With deprecated CSVReader. Use CSVReaderBuilder. commas in quotes data
public void opencsvParser2() throws Exception {
	System.out.println("\nopencsvParser2 started");
	CSVReader reader = null;
	try {
		reader = new CSVReader(
				new FileReader("E:/files/cm24AUG2017bhav1.csv"), ',');
		String[] record = null;
		while ((record = reader.readNext()) != null) {
			String SYMBOL = record[0];
			String ISIN = record[12];
			System.out.println(SYMBOL + "," + ISIN);
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		reader.close();
	}
}

With CSVReaderBuilder

@Test
// With CSVReaderBuilder
public void opencsvParser3() throws Exception {
	System.out.println("\nopencsvParser3 started");
	CSVReaderBuilder readerBuilder = null;
	CSVReader reader = null;
	try {
		readerBuilder = new CSVReaderBuilder(
				new FileReader("E:/files/cm24AUG2017bhav.csv"));

		reader = readerBuilder.build();
		String[] record = null;
		while ((record = reader.readNext()) != null) {
			String SYMBOL = record[0];
			String ISIN = record[12];
			System.out.println(SYMBOL + "," + ISIN);
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		reader.close();
	}
}

With CSVReaderBuilder. commas in quotes data

@Test
// With CSVReaderBuilder. commas in quotes data
public void opencsvParser4() throws Exception {
	System.out.println("\nopencsvParser4 started");
	CSVReaderBuilder readerBuilder = null;
	CSVReader reader = null;
	try {
		readerBuilder = new CSVReaderBuilder(
				new FileReader("E:/files/cm24AUG2017bhav1.csv"))
						.withCSVParser(new RFC4180Parser());

		reader = readerBuilder.build();
		String[] record = null;
		while ((record = reader.readNext()) != null) {
			String SYMBOL = record[0];
			String ISIN = record[12];
			System.out.println(SYMBOL + "," + ISIN);
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		reader.close();
	}
}

With CsvToBean and ColumnPositionMappingStrategy. CsvToBean.parse is deprecated

@SuppressWarnings("deprecation")
@Test
// With CsvToBean and ColumnPositionMappingStrategy
// CsvToBean.parse is deprecated
public void opencsvParser5() throws Exception {
	System.out.println("\nopencsvParser5 started");
	CSVReaderBuilder readerBuilder = null;
	CSVReader reader = null;
	try {
		readerBuilder = new CSVReaderBuilder(
				new FileReader("E:/files/cm24AUG2017bhav.csv"));

		reader = readerBuilder.build();
		ColumnPositionMappingStrategy<Stock> beanStrategy = new ColumnPositionMappingStrategy<Stock>();
		beanStrategy.setType(Stock.class);
		beanStrategy.setColumnMapping(
				new String[] { "symbol", "series", "open", "high", "low",
						"close", "last", "prevclose", "tottrdqty",
						"tottrdval", "timestamp", "totaltrades", "isin" });

		CsvToBean<Stock> csvToBean = new CsvToBean<Stock>();

		List<Stock> stocks = csvToBean.parse(beanStrategy, reader);
		System.out.println(stocks);
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		reader.close();
	}
}

With header element name

@Test
// With CsvToBeanBuilder and ColumnPositionMappingStrategy
public void opencsvParser6() throws Exception {
	System.out.println("\nopencsvParser6 started");
	try {
		ColumnPositionMappingStrategy<Stock> beanStrategy = new ColumnPositionMappingStrategy<Stock>();
		beanStrategy.setType(Stock.class);
		beanStrategy.setColumnMapping(
				new String[] { "symbol", "series", "open", "high", "low",
						"close", "last", "prevclose", "tottrdqty",
						"tottrdval", "timestamp", "totaltrades", "isin" });

		CsvToBeanBuilder<Stock> csvToBeanBuilder = new CsvToBeanBuilder<Stock>(
				new FileReader("E:/files/cm24AUG2017bhav1.csv"));
		List<Stock> stocks = csvToBeanBuilder
				.withMappingStrategy(beanStrategy).build().parse();
		System.out.println(stocks);
	} catch (Exception e) {
		e.printStackTrace();
	}
}

Tags:

by , on August 29th, 2017

  • Categories