[jira] [Comment Edited] (NIFI-13744) ExcelReader time conversion issues

["Daniel Stieglitz (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-13744?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17890171#comment-17890171 ] 

Daniel Stieglitz edited comment on NIFI-13744 at 10/16/24 4:30 PM:
-------------------------------------------------------------------

[~john.wise] There has been a better fix by [~exceptionfactory] to handle your use case. With his fix as long as the column is an Excel date format, the ExcelReader will infer all dates without having to specify any date, time or timestamp patterns. It is only necessary to specify date, time or timestamp patterns when the column is in an Excel text format. The dates are still saved as an epoch but to format them specify date, time or timestamp patterns in the specified writer.


was (Author: JIRAUSER294662):
[~john.wise] There has been a better fix by [~exceptionfactory] to handle your use case. With his fix as long as the Excel format for that column is a date format, the ExcelReader will infer all dates without having to specify any date, time or timestamp patterns. It is only necessary to specify date, time or timestamp patterns when the column is in an Excel text format.

> ExcelReader time conversion issues
> ----------------------------------
>
>                 Key: NIFI-13744
>                 URL: https://issues.apache.org/jira/browse/NIFI-13744
>             Project: Apache NiFi
>          Issue Type: Bug
>          Components: Core Framework
>    Affects Versions: 1.27.0, 2.0.0-M4
>         Environment: RHEL 8.10, macOS Sequoia 15
>            Reporter: John Wise
>            Assignee: Daniel Stieglitz
>            Priority: Major
>              Labels: Bug, Reader
>             Fix For: 1.28.0, 2.0.0-M5
>
>         Attachments: Excel_Reader_-_Time_Conversion_Issues.xml, test.xlsx
>
>          Time Spent: 23h 50m
>  Remaining Estimate: 0h
>
> [~dstiegli1] - There are actually two issues with time conversion in ExcelReader.  I'm using the following sample set of data to illustrate both issues:
> {code:java}
> Date_Standard: "07/24/24",
> Date_Custom: "07/25/24",
> Time_Standard: "4:00:00 PM",
> Time_Custom: "16:00:00",
> Timestamp: "07/26/24 16:00:00"{code}
> 1. When inferring the schema, all of the Avro types are incorrectly identified as strings:
> {code:java}
> {
>   "type":"record",
>   "name":"nifiRecord",
>   "namespace":"org.apache.nifi",
>   "fields":[
>     {"name":"column_0","type":["string","null"]},
>     {"name":"column_1","type":["string","null"]},
>     {"name":"column_2","type":["string","null"]},
>     {"name":"column_3","type":["string","null"]},
>     {"name":"column_4","type":["string","null"]}
>   ]
> }{code}
> The output consists solely of epoch strings; the fourth value, {{{}"-2208999600000"{}}}, appears to be incorrect, since it isn't inline with the other values at all.
> *Inferred output:*
> {code:java}
> [ {
>   "column_0" : "1721793600000",
>   "column_1" : "1721880000000",
>   "column_2" : "1721851200000",
>   "column_3" : "-2208999600000",
>   "column_4" : "1722024000000"
> } ]{code}
>  
> 2. The second issue occurs when the Avro schema is provided:
> {code:java}
> {
>   "type": "record",
>   "name": "nifiRecord",
>   "namespace": "org.apache.nifi",
>   "fields": [
>     {
>       "name": "Date_Standard",
>       "type": [ "null", { "type": "int", "logicalType": "date" } ]
>     },
>     {
>       "name": "Date_Custom",
>       "type": [ "null", { "type": "int", "logicalType": "date" } ]
>     },
>     {
>       "name": "Time_Standard",
>       "type": [ "null", { "type": "int", "logicalType": "time-millis" } ]
>     },
>     {
>       "name": "Time_Custom",
>       "type": [ "null", { "type": "long", "logicalType": "time-micros" } ]
>     },
>     {
>       "name": "Timestamp",
>       "type": [ "null", { "type": "long", "logicalType": "timestamp-millis" } ]
>     }
>   ]
> } 
> {code}
> Conversion of the {{time-millis}} and {{time-micros}} fields both fail with errors similar to this:
> {code:java}
> • 18:01:24 EDT ERROR
> ConvertRecord[id=a098dabc-0191-1000-6d17-3aaa911b2130] Failed to process FlowFile [filename=test.xIsx]; 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
> - Caused by: org.apache.nifi.serialization.record.util.IllegalTypeConversionException: Cannot convert value [Sun Dec 31 16:00:00 EST 1899] of type class java.util.Date to Time for field Time_Custom
> {code}
> Changing the failed types to "string" results in order to allow conversion of the other values results in an epoch output for those values:
> {code:java}
> [ {
>   "Date_Standard" : "07/24/2024",
>   "Date_Custom" : "07/25/2024",
>   "Time_Standard" : "1721851200000",
>   "Time_Custom" : "-2208999600000",
>   "Timestamp" : "07/26/2024 16:00:00"
> } ]{code}
>  
> Given the same data in both JSON and CSV formats, with corresponding Readers, both the inferred and schema-provided outputs are as expected.  This appears to be an issue in ExcelReader.
> I've attached the spreadsheet & a template of the NiFi flow that I've been troubleshooting this with.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)