CODEWITHSUNDEEP
javacsvopencsvsupercsv
jsf
jsf

Reputation: 2981

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

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.

Upvotes: 30

Views: 79422

Answers (10)

soumya-kole
soumya-kole

Reputation: 1355

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

Upvotes: 1

Adarsh Mohan
Adarsh Mohan

Reputation: 11

Use @CsvIgnore annotation above the field which need not to be written to csv file.

Upvotes: 1

user3996996
user3996996

Reputation: 342

SimpleFlatMapper can do that easily see Getting Started csv using the headers of the csv or by manually specifying which columns map to what property.

CsvParser
    .mapTo(MyObject.class)
    .forEach(file, System.out::println);

Upvotes: 0

agilob
agilob

Reputation: 6223

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;

Upvotes: 9

James Bassett
James Bassett

Reputation: 9868

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();
}

Upvotes: 4

Paul Hilliar
Paul Hilliar

Reputation: 738

Here is a nice way to do use OpenCSV to do the mapping to POJO generically:

protected <T> List<T> mapToCSV(String csvContent, Class<T> mapToClass) {
    CsvToBean<T> csvToBean = new CsvToBean<T>();

    Map<String, String> columnMapping = new HashMap<>();
    Arrays.stream(mapToClass.getDeclaredFields()).forEach(field -> {
        columnMapping.put(field.getName(), field.getName()); 
    });

    HeaderColumnNameTranslateMappingStrategy<T> strategy = new HeaderColumnNameTranslateMappingStrategy<T>();
    strategy.setType(mapToClass);
    strategy.setColumnMapping(columnMapping);

    CSVReader reader = new CSVReader(new StringReader(csvContent));
    return csvToBean.parse(strategy, reader);
}


public static class MyPojo {
    private String foo, bar;

    public void setFoo(String foo) {
        this.foo = foo;
    }

    public void setBar(String bar) {
        this.bar = bar;
    }
}

Then from your test you can use:

List<MyPojo> list = mapToCSV(csvContent, MyPojo.class);

Upvotes: 1

lex404
lex404

Reputation: 1

jcvsdao example usage

Sample User CSV file

Username, Email, Registration Date, Age, Premium User
Jimmy, [email protected], 04-05-2016, 15, Yes, M
Bob, [email protected], 15-01-2012, 32, No, M
Alice, [email protected], 22-09-2011, 24, No, F
Mike, [email protected], 11-03-2012, 18, Yes, M
Helen, [email protected], 02-12-2013, 22, Yes, F
Tom, [email protected], 08-11-2015, 45, No, M

Create A CsvDao

CSVDaoFactory factory = new CSVDaoFactory("/csv-config.xml");
CSVDao dao = new CSVDao(factory);
List<UserDetail> users = dao.find(UserDetail.class);

csv-config.xml

<CSVConfig>
    <mappingFiles fileType="resource">
        <mappingFile>/example01/mapping/UserDetail.csv.xml</mappingFile>
    </mappingFiles>
</CSVConfig>

UserDetail.csv.xml

<CSVMapping className="org.jcsvdao.examples.example01.model.UserDetail" csvFile="csv-examples/example01/users.txt" delimiter="," ignoreFirstLine="true">
    <matchAll/>
    <properties>
        <property index="0" property="username" primaryKey="true"/>
        <property index="1" property="email"/>
        <property index="2" property="registrationDate" converter="myDateConverter"/>
        <property index="3" property="age"/>
        <property index="4" property="premiumUser" converter="yesNoConverter"/>
        <property index="5" property="gender" converter="myGenderConverter"/>
    </properties>
    <converters>
        <dateConverter converterName="myDateConverter" format="dd-MM-yyyy"/>
        <booleanConverter converterName="yesNoConverter" positive="Yes" negative="No"/>
        <customConverter converterName="myGenderConverter" converterClass="org.jcsvdao.examples.example01.converter.GenderCustomerConverter"/>
    </converters>
</CSVMapping>

Upvotes: 0

baskar_p
baskar_p

Reputation: 665

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.

Upvotes: 27

lex404
lex404

Reputation: 1

Have a look at jcsvdao, https://github.com/eric-mckinley/jcsvdao/ , uses hibernate style mapping files and can handle 1to1 and 1toMany relations. Good if you dont own the csv files as has flexible matching strategies.

Upvotes: 0

Jeronimo Backes
Jeronimo Backes

Reputation: 6289

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).

Upvotes: 4

Related Questions