Failing with InvalidFormatException when using date fields with jackson-dataformat-csv

Question

I am trying to use the CSVMapper utility from jackson-dataformat-csv to parse a CSV file and create POJO records to insert to mysql database.

When doing so, I am facing issues if a date information is present as one of the columns in the CSV to be mapped to a date field in POJO.

I have the following code in place from reading the CSV file and converting it to a POJO.

Bug.java

@Entity
@JsonIgnoreProperties(ignoreUnknown = true)
public class Bug implements Serializable {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;

    @JsonProperty
    @JsonFormat(shape = JsonFormat.Shape.NUMBER)
    private Long Num;

    @JsonProperty("Show Bug")
    private String URL;

    @JsonProperty
    private String Subject;

    @JsonProperty
    private String Customer;

    @JsonProperty
    private String Tags;

    @JsonProperty
    private String St;

    @JsonProperty("Rep. Release")
    private String ReportedRelease;

    @JsonProperty("Reported")
    private Date reportedDate;

    @JsonProperty
    private String Sev;

    @JsonProperty
    private String Assignee;

    @JsonProperty
    private String Component;

    public Bug() {}

        /* getters and setters */

Controller code


@PostMapping(value = "/upload", consumes = "multipart/form-data")
    public void uploadMultipart(@RequestParam("file") MultipartFile file) throws IOException {

try {

            CsvSchema bootstrap = CsvSchema.builder().setUseHeader(true)
                        .addColumn("Sl No.", CsvSchema.ColumnType.NUMBER)
                        .addColumn("Num", CsvSchema.ColumnType.NUMBER)
                        .addColumn("Show Bug", CsvSchema.ColumnType.STRING)
                        .addColumn("Customer", CsvSchema.ColumnType.STRING)
                        .addColumn("Rep. Release", CsvSchema.ColumnType.STRING)
                        .addColumn("Reported")
                        .addColumn("Component", CsvSchema.ColumnType.STRING)
                        .addColumn("Assignee", CsvSchema.ColumnType.STRING)
                        .addColumn("Sev", CsvSchema.ColumnType.STRING)
                        .addColumn("St", CsvSchema.ColumnType.STRING)
                        .addColumn("Tags", CsvSchema.ColumnType.STRING)
                        .addColumn("Subject", CsvSchema.ColumnType.STRING)
                        .build().withHeader();

            CsvMapper csvMapper = new CsvMapper();

            MappingIterator<Bug> bugInfo = 
                    csvMapper.readerFor(Bug.class).with(bootstrap).readValues(file.getInputStream());

            System.out.println("************************ Bug Information ************************");

            while(bugInfo.hasNext()) {
                Bug bugRec = bugInfo.next();
                System.out.println("Customer : " + bugRec.getCustomer());
                System.out.println("URL : " + bugRec.getURL());
            }


} catch(IOException exception) {
            throw exception;
        }
    }

This did work for quite some time and I was able to print the records in the while loop and also thereafter inserted the records to mysql table.

Not sure what changed in the code, now I hit the following issue


com.fasterxml.jackson.databind.exc.InvalidFormatException: Can not construct instance of java.sql.Date from String value ("20-MAY-19"): not a valid representation (error: Failed to parse Date value '20-MAY-19': Can not parse date "20-MAY-19": not compatible with any of standard forms ("yyyy-MM-dd'T'HH:mm:ss.SSSZ", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "EEE, dd MMM yyyy HH:mm:ss zzz", "yyyy-MM-dd"))
 at [Source: com.fasterxml.jackson.dataformat.csv.impl.UTF8Reader@411358fc; line: 2, column: 147] (through reference chain: com.app.oracle.OMBugAnalyzerServices.entity.Bug["Reported"])
    at com.fasterxml.jackson.databind.exc.InvalidFormatException.from(InvalidFormatException.java:74) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.DeserializationContext.weirdStringException(DeserializationContext.java:1021) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.deser.std.StdDeserializer._parseDate(StdDeserializer.java:788) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.deser.std.DateDeserializers$DateBasedDeserializer._parseDate(DateDeserializers.java:175) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.deser.std.DateDeserializers$SqlDateDeserializer.deserialize(DateDeserializers.java:284) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.deser.std.DateDeserializers$SqlDateDeserializer.deserialize(DateDeserializers.java:269) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.deser.SettableBeanProperty.deserialize(SettableBeanProperty.java:490) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.deser.impl.MethodProperty.deserializeAndSet(MethodProperty.java:95) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.deser.BeanDeserializer.vanillaDeserialize(BeanDeserializer.java:260) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserialize(BeanDeserializer.java:125) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.MappingIterator.nextValue(MappingIterator.java:277) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.fasterxml.jackson.databind.MappingIterator.next(MappingIterator.java:192) ~[jackson-databind-2.7.4.jar:2.7.4]
    at com.app.oracle.OMBugAnalyzerServices.controller.BugAnalyzerController.uploadMultipart(BugAnalyzerController.java:129) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_211]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_211]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_211]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_211]

score 4 · Accepted Answer · answered May 23 '19 at 18:51

4

You can set format pattern in jackson like below:

@JsonFormat(pattern="yy-MM-dd")
@JsonProperty("Reported")
private Date reportedDate;

answered May 23 '19 at 18:51

Ebrahim Pasbani

9,168
2
23
30

Thanks a ton. Your recommendation worked! But I am still wondering, how it worked when i never had this annotation added on the POJO class earlier. Any thoughts if it could work even without this? It's only for my learning purpose because I am certain that I did not add this annotation earlier. – svijay.aug12 May 23 '19 at 18:57
@svijay.aug12 You're welcome. Maybe the dates before you used are in the supported formats. Or there is a jackson customizer existed before. The supported formats are `"yyyy-MM-dd'T'HH:mm:ss.SSSZ", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "EEE, dd MMM yyyy HH:mm:ss zzz", "yyyy-MM-dd"` as said in exception stack trace – Ebrahim Pasbani May 23 '19 at 19:01

score 0 · Answer 2 · answered May 23 '19 at 18:38

0

As the below exception says,

com.fasterxml.jackson.databind.exc.InvalidFormatException: Can not construct instance of java.sql.Date from String value ("20-MAY-19"): not a valid representation (error: Failed to parse Date value '20-MAY-19': Can not parse date "20-MAY-19": not compatible with any of standard forms ("yyyy-MM-dd'T'HH:mm:ss.SSSZ", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "EEE, dd MMM yyyy HH:mm:ss zzz", "yyyy-MM-dd"))

Probably, you changed the below.

@JsonProperty("Reported")
private Date reportedDate;

answered May 23 '19 at 18:38

Sambit

7,625
7
34
65

Hi Sambit. Thanks for your prompt response. I was hitting the same issue at an earlier point as well. At that point, the reportedDate field data type was pointing to java.util.date. When it did not work, I changed it java.sql.Date and it started working. However, currently i see that exception for both java.util.Date as well as java.sql.Date. – svijay.aug12 May 23 '19 at 18:41
Hi Sambit, I cannot change the type to String. It is a Date field on the database. – svijay.aug12 May 23 '19 at 18:43
Only option is convert the date to String with the required format(mm-dd-yyyy) like this. – Sambit May 23 '19 at 18:45
does csv-mapper not have a way to work with date data types? Because, if the date field maps to an underlying column in the database and I cannot change its type. Also, it did work earlier, which makes me believe that something has got changed in the code because of which its now not working. I did insert more than 500 records just 3 hours before. Now something got changed and I dont have the backup. – svijay.aug12 May 23 '19 at 18:46
CSV file understands mostly number, string. Date is upto us about how to interpret it. CSV is different from excel. – Sambit May 23 '19 at 18:50

Failing with InvalidFormatException when using date fields with jackson-dataformat-csv

2 Answers2