[
https://issues.apache.org/jira/browse/NIFI-13744?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
John Wise updated NIFI-13744:
-----------------------------
Description:
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.
was:
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, 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.
> 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
> Environment: RHEL 8.10, macOS Sequoia 15
> Reporter: John Wise
> Priority: Minor
> Labels: Bug, Reader
> Attachments: Excel_Reader_-_Time_Conversion_Issues.xml, test.xlsx
>
>
> 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)
