ExcelReader Controller Failing on valid date with proper Date Format setting

[#1]

I am getting an error when using the ExcelReader to convert an Excel sheet to JSON. Everything works except for some dates. If I remove the Date Format setting on the controller, no issue, but get the date in milliseconds from 1/1/1970, which I do not want. The ExcelReader is failing on the date 05/01/2017. This is a valid MM/dd/yyyy date. The error I get is: 

ERROR
015de330-01a0-1000-0000-00001cb4e04e
All Nodes
ConvertRecord[id=015de330-01a0-1000-0000-00001cb4e04e] Failed to process FlowFile[filename=Test_File.xlsx]; will route to failure: org.apache.nifi.processor.exception.ProcessException: Could not parse incoming data
- Caused by: org.apache.nifi.serialization.MalformedRecordException: Read next Record from Excel XLSX failed on row 59 in sheet Data
- Caused by: org.apache.nifi.serialization.record.field.FieldConversionException: Conversion failed for [05/01/2017] named [column_9] to [java.time.LocalDate] [java.time.format.DateTimeParseException] Text '05/01/2017' could not be parsed at index 0

It did not fail on other dates like 6/30/2026. Is it possible that NiFi ExcelReader cannot support a leading 0 on months? This could be a big issue for us as a leading 0 on single digit months is standard.

I would be grateful for any insights.

I am using NiFi 2.0 with Cloudera Data Flow 2.6.0.4.12.0.1-9. I attached my ExcelReader and JsonRecordSetWritter settings.

[#2]

Hello @BobKing, 

Thanks for being part of our community and reaching us. 

I do not think the issue is the 0 at the beginning directly. 
The format specified is MM/dd/yyyy so the processor expects the 0, in fact, the working dates do have the 0 added when it get's converted. 

What I'm suspecting, is that Excel have different formats for the cells. 
If the same file have some dates with the 0 and without it, it could be a mismatch on the format. 
Check and compare the file, does all the cells have the same date format? Or some are saved as string or text? 
Do one test, replace one of the failed cells with one of the working cells and then modified to reflect the proper date. 

[#3]

I did verify that the Excel cell is a formatted date. All cells that have a date value are formatted as dates (*m/dd/yyyy) in Excel. This is a weird issue. I have one date formatted cell with 6/30/2026 and another date formatted cell with 05/01/2017. 

If I leave the Date Format blank, the 6/30/2026 is translated to an integer representing milliseconds from 1/1/1970. The 05/17/2017 is fine. If I add the Date Format, it accepts the 6/30/2026 but the 05/01/2017 causes the error.

I cannot change the formatting of the Excel because these Excel templates are not changeable due to legal contracts. It will take months to go through the legal process to get the Excel templates changed in my industry.

[#4 · Accepted solution]

I removed the Date Format from the ExcelReader controller and kept the Data Format in the JsonRecordSetWriter and all dates cam out as expected. It appears by leaving the Date Format empty in the ExcelReader but putting MM/dd/yyyy in the JsonRecordSetWriter will work.