OpenCSV - How to map selected columns to Java Bean regardless of order?

Question

I have a CSV file with the following columns: id, fname, telephone, lname, address.

I have a Person class with id, fname and lname data members. I want to map only these columns to Person object from a CSV file and discard telephone and address columns. How can I do this? The solution must scale as more columns are added in future. And should work regardless of column position.

In an ideal solution user will only specify columns to read and it should just work.

Solution 1

You can use HeaderColumnNameTranslateMappingStrategy. Lets assume your CSV has the following columns: Id, Fname, Telephone, Lname, Address for the sake of simplicity.

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

Map<String, String> columnMapping = new HashMap<String, String>();
columnMapping.put("Id", "id");
columnMapping.put("Fname", "fname");
columnMapping.put("Lname", "lname");

HeaderColumnNameTranslateMappingStrategy<Person> strategy = new HeaderColumnNameTranslateMappingStrategy<Person>();
strategy.setType(Person.class);
strategy.setColumnMapping(columnMapping);

List<Person> list = null;
CSVReader reader = new CSVReader(new InputStreamReader(ClassLoader.getSystemResourceAsStream("test.csv")));
list = csvToBean.parse(strategy, reader);

The columnMapping will map the columns with your Person object.

Solution 2

Recent versions of OpenCSV deprecate the method parse(X, Y) and it's recommenced to use BeanBuilder instead, so the top answer is out of date.

try {
    CsvToBeanBuilder<PersonCSV> beanBuilder = new CsvToBeanBuilder<>(new InputStreamReader(new FileInputStream("your.csv")));

    beanBuilder.withType(PersonCSV.class);
    // build methods returns a list of Beans
    beanBuilder.build().parse().forEach(e -> log.error(e.toString()));

} catch (FileNotFoundException e) {
    log.error(e.getMessage(), e);
}

This methods allows you to clean up the code and remove MappingStrategy (you can still use it if you like spaghetti), so you can annotate your CSV class as follows:

@CsvDate("dd/MM/yyyy hh:mm:ss")
@CsvBindByName(column = "Time Born", required = true)
private Date birthDate;

Solution 3

I can't speak for opencsv, but this is easily achievable using Super CSV, which has two different readers that support partial reading (ignoring columns), as well as reading into a Javabean. CsvDozerBeanReader is even capable of deep and index-based mapping, so you can map to nested fields.

We (the Super CSV team) have just released version 2.0.0, which is available from Maven central or SourceForge.

Update

Here's an example (based on the test in the GitHub project you've created), that uses Super CSV instead of opencsv. Note the CSV preferences needed the surroundingSpacesNeedQuotes flag enabled as your example CSV file isn't valid (it has spaces between the fields - spaces are considered part of the data in CSV).

ICsvBeanReader beanReader = null;
try {
    beanReader = new CsvBeanReader(
            new InputStreamReader(
                    ClassLoader.getSystemResourceAsStream("test.csv")),
            new CsvPreference.Builder(CsvPreference.STANDARD_PREFERENCE)
                    .surroundingSpacesNeedQuotes(true).build());

    List<String> columnsToMap = Arrays.asList("fname", "telephone", "id");

    // read the CSV header (and set any unwanted columns to null)
    String[] header = beanReader.getHeader(true);
    for (int i = 0; i < header.length; i++) {
        if (!columnsToMap.contains(header[i])) {
            header[i] = null;
        }
    }

    Person person;
    while ((person = beanReader.read(Person.class, header)) != null) {
        System.out.println(person);
    }

} finally {
    beanReader.close();
}

Solution 4

Use uniVocity-parsers and be done with it. It doesn't matter how the columns are organized in the input CSV, only the ones you need will be parsed.

If writing, the columns you have in class will be written to the correct columns, while the others will be empty.

Here's a class with some examples:

class TestBean {

    // if the value parsed in the quantity column is "?" or "-", it will be replaced by null.
    @NullString(nulls = { "?", "-" })
    // if a value resolves to null, it will be converted to the String "0".
    @Parsed(defaultNullRead = "0")
    private Integer quantity;   // The attribute type defines which conversion will be executed when processing the value.

    @Trim
    @LowerCase
    // the value for the comments attribute is in the column at index 4 (0 is the first column, so this means fifth column in the file)
    @Parsed(index = 4)
    private String comments;

    // you can also explicitly give the name of a column in the file.
    @Parsed(field = "amount")
    private BigDecimal amount;

    @Trim
    @LowerCase
    // values "no", "n" and "null" will be converted to false; values "yes" and "y" will be converted to true
    @BooleanString(falseStrings = { "no", "n", "null" }, trueStrings = { "yes", "y" })
    @Parsed
    private Boolean pending;
}

Here's how to get a list of TestBean

BeanListProcessor<TestBean> rowProcessor = new BeanListProcessor<TestBean>(TestBean.class);

CsvParserSettings parserSettings = new CsvParserSettings();
parserSettings.setRowProcessor(rowProcessor);
parserSettings.setHeaderExtractionEnabled(true);

CsvParser parser = new CsvParser(parserSettings);
parser.parse(getReader("/examples/bean_test.csv"));

List<TestBean> beans = rowProcessor.getBeans();

Disclosure: I am the author of this library. It's open-source and free (Apache V2.0 license).

Solution 5

With opencsv, you can create a generic function like :

public static <T> void csvWriterUtil(Class<T> beanClass, List<T> data, String outputFile, String[] columnMapping) {
    try {
        Writer writer = new BufferedWriter(new FileWriter(outputFile));
        ColumnPositionMappingStrategy<T> strategy = new ColumnPositionMappingStrategy<>();
        strategy.setType(beanClass);
        strategy.setColumnMapping(columnMapping);
        StatefulBeanToCsv<T> statefulBeanToCsv =new StatefulBeanToCsvBuilder<T>(writer)
                .withMappingStrategy(strategy)
                .build();
        writer.write(String.join(",",columnMapping)+"\n");
        statefulBeanToCsv.write(data);
        writer.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (CsvRequiredFieldEmptyException e) {
        e.printStackTrace();
    } catch (CsvDataTypeMismatchException e) {
        e.printStackTrace();
    }
}

Here you can pass only required columns through columnMapping parameter.

Code example is available in https://github.com/soumya-kole/JavaUtils/tree/master/CsvUtil

View more solutions

Comments

[root]

Would it be possible to map a String in the csv "2015-01-02 23:59:50" to a joda DateTime object within a bean?

[Teepeemm]

Welcome to SO. This would be a much better answer if you included some code here, instead of behind a link.

[agilob]

This method is outdated so I wrote new answer which shows how to use BeanBuilder stackoverflow.com/a/48227474/1238944

[djangofan]

Can do the same with Apache MetaModel

[Piyush N]

What if we even don't need empty column. We just want to remove unwanted columns? Is it possible with univocity?

[Jeronimo Backes]

use settings.selectFields or settings.selectIndexes to choose what columns to parse.

[Shubham]

Getting a java.lang.NoSuchMethodError: org.apache.commons.beanutils.ConvertUtilsBean.register(ZZI)V at the beanBuilder.build() line. Where am i going wrong?

[Jignesh M. Khatri]

How to ignore unmapped CSV columns? I mean if in CSV there are 10 columns and I want to read only 5 (these 5 are there in bean) of them then? Currently it is throwing exception Number of data fields does not match number of headers.