Jackson CSV mapper doesn't work for custom schema

Question

I have this code:

CsvSchema sema = CsvSchema.builder()
    .addColumn("name")
    .addColumn("year", CsvSchema.ColumnType.NUMBER)
    .build().withHeader();

ObjectReader reader = new CsvMapper().readerFor(JsonNode.class).with(sema);

JsonNode o = reader.readValue(new FileInputStream(new File("/path/to/test.csv")));
System.out.println(o);

and test.csv is:

test, year
1,    1

That code should parse CSV to JSON in next format:

{"name":"1","year":1}

but my output is:

{"name":"1","year":"1"}

Problem is: Jackson parses year as String, but I configured in CSV Schema that year is Number. Does someone know what is the problem?

Jackson version is 2.9.8, I tried it also on 2.7.1

Does this question a duplicate to [Convert map to json string by json schema](https://stackoverflow.com/questions/55064706/convert-map-to-json-string-by-json-schema)? Why have you created almost two identical questions? — Michał Ziober, Mar 08 '19 at 22:43
No, it isn't. First problem is how to convert csv to json string, and second is how to use jackson custom schema for csv. — pera.coyote, Mar 11 '19 at 08:22

score 1 · Accepted Answer · answered Mar 11 '19 at 14:33

After hours of work I found a solution for you.

I used FlexJson to configure the serialization of your json.

    <!-- https://mvnrepository.com/artifact/net.sf.flexjson/flexjson -->
<dependency>
    <groupId>net.sf.flexjson</groupId>
    <artifactId>flexjson</artifactId>
    <version>2.0</version>
</dependency>

It is not very pretty but it works.

I hope this helps you and I am sure you can improve this code

public String generateJsonFromCSV(File csvFile, File schemaJson) throws IOException, ClassNotFoundException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {

    //Get CsvSchema as Map
    Map<String, CsvSchema.ColumnType> map = getSchemaMapFromJson(schemaJson);
    //Create CsvSchema from CsvSchemaMap
    CsvSchema.Builder schemaBuilder = new CsvSchema.Builder();
    map.forEach(schemaBuilder::addColumn);
    CsvSchema schema = schemaBuilder.build();
    //read CSV
    CsvMapper csvMapper = new CsvMapper();
    MappingIterator<Map<?, ?>> mappingIterator = csvMapper.readerFor(Map.class).with(schema).readValues(csvFile);
    //Get configured JsonSerializer from CsvSchemaMap
    JSONSerializer jsonSerializer = getJsonSerializer(map);
    List<Map<?, ?>> lines = mappingIterator.readAll();
    //remove first line
    lines.remove(0);
    //serialize
    return jsonSerializer.deepSerialize(lines);
}

/**
 *
 * @param schemaMap mapping field to ColumnType
 * @return a configured JSONSerializer
 */
private JSONSerializer getJsonSerializer(Map<String, CsvSchema.ColumnType> schemaMap){
    Map<CsvSchema.ColumnType, Transformer> transformerMap = new EnumMap<>(CsvSchema.ColumnType.class);
    transformerMap.put(CsvSchema.ColumnType.STRING, new StringTransformer());
    transformerMap.put(CsvSchema.ColumnType.NUMBER, new NumberTransformer());
    JSONSerializer jsonSerializer = new JSONSerializer();
    for (Map.Entry<String, CsvSchema.ColumnType> columnTypeEntry : schemaMap.entrySet()) {
        jsonSerializer.transform(transformerMap.get(columnTypeEntry.getValue()),columnTypeEntry.getKey());
    }
    return jsonSerializer;
}

/**
 /**
 *
 * @param file JSON CsvSchema
 * @return fieldname ColumnType mapping
 * @throws ClassNotFoundException
 */
private Map<String, CsvSchema.ColumnType> getSchemaMapFromJson(File file) throws ClassNotFoundException {
    Map<String, String> schema = new JSONDeserializer<Map<String,String>>().deserialize(getResourceFileAsString(file.getName()));
    Map<String, CsvSchema.ColumnType> result = new HashMap<>(schema.size());
    for (Map.Entry<String, String> columnSchema : schema.entrySet()) {
        result.put(columnSchema.getKey(), CsvSchema.ColumnType.valueOf(columnSchema.getValue().toUpperCase()));
    }
    return result;
}

The output will be

[{"name":"foobar","year":1986},{"name":"testtest","year":777}]

score 0 · Answer 2 · answered Mar 08 '19 at 22:21

To force CsvMapper to use given type the best way is to use POJO. In this case CsvMapper knows type and converts it automatically if it is possible. Let's create an example data:

name,year
1,1
2,2
3,2

(In your example, first column name is test but I think this is just a mistake and it should be name.)

Below app shows how to parse CSV and write JSON using POJO:

import com.fasterxml.jackson.annotation.JsonCreator;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.MappingIterator;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.ObjectReader;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.dataformat.csv.CsvMapper;
import com.fasterxml.jackson.dataformat.csv.CsvSchema;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class CsvApp {

    public static void main(String[] args) throws IOException {
        File csvFile = new File("./resource/test.csv").getAbsoluteFile();

        CsvMapper csvMapper = new CsvMapper();
        CsvSchema bootstrapSchema = CsvSchema.emptySchema().withHeader();
        ObjectReader reader = csvMapper.readerFor(User.class).with(bootstrapSchema);
        MappingIterator<User> iterator = reader.readValues(csvFile);

        List<User> users = new ArrayList<>();
        iterator.forEachRemaining(users::add);

        System.out.println("Users read from CSV file:");
        users.forEach(System.out::println);

        System.out.println();
        System.out.println("Users in JSON format:");
        ObjectMapper jsonMapper = new ObjectMapper();
        jsonMapper.enable(SerializationFeature.INDENT_OUTPUT);

        System.out.println(jsonMapper.writeValueAsString(users));
    }
}

class User {

    private final String name;
    private final int year;

    @JsonCreator
    public User(@JsonProperty("name") String name, @JsonProperty("year") int year) {
        this.name = name;
        this.year = year;
    }

    public String getName() {
        return name;
    }

    public int getYear() {
        return year;
    }

    @Override
    public String toString() {
        return "User{" +
                "name='" + name + '\'' +
                ", year=" + year +
                '}';
    }
}

Above code prints:

Users read from CSV file:
User{name='1', year=1}
User{name='2', year=2}
User{name='3', year=2}

Users in JSON format:
[ {
  "name" : "1",
  "year" : 1
}, {
  "name" : "2",
  "year" : 2
}, {
  "name" : "3",
  "year" : 2
} ]

Yep, but I don't have pojo. I want to use builder to create custom scheme, not to create it from pojo — pera.coyote, Mar 11 '19 at 08:23

Jackson CSV mapper doesn't work for custom schema

2 Answers2

Linked