[jira] [Commented] (NIFI-14106) Loss of dates and times precision when running Excel with Dates through SplitExcel

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

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

Daniel Stieglitz commented on NIFI-14106:
-----------------------------------------

[~exceptionfactory] I determined the cause of this ticket was as a result of the changes made in NIFI-13726. Once the cell styles are not copied the formatting for date/time are lost. I am not sure how to proceed as this is a bug I discovered and no one is complaining about it so far. A potential fix I thought of was to to take the current POI code which I used to copy the rows and retrofit it to at least preserve the date/time as an epoch in milliseconds. Is that something worth pursuing or perhaps is this something to ask the POI folks to incorporate in their code?

> Loss of dates and times precision when running Excel with Dates through SplitExcel
> ----------------------------------------------------------------------------------
>
>                 Key: NIFI-14106
>                 URL: https://issues.apache.org/jira/browse/NIFI-14106
>             Project: Apache NiFi
>          Issue Type: Bug
>            Reporter: Daniel Stieglitz
>            Assignee: Daniel Stieglitz
>            Priority: Major
>         Attachments: fileWithIssue.xlsx
>
>
> The SplitExcel processor seems to be dropping date format information needed in order to correctly determine dates and times. I set up two flows sending the attached file to both:
> +Flow without SplitExcel+
> GetFile -> ConvertRecord (configured with ExcelReader with Schema Access Strategy 'Use Starting Row' and CSVRecordSetWriter (configured with Schema Access Strategy 'Inherit Record Schema', Date Format MM/dd/yyyy, Time Format HH:mm:ss and Timestamp Format MM/dd/yyyy'T'hh:mm:ss)
> +Flow with SplitExcel+
> GetFile -> SplitExcel -> ConvertRecord (configured with ExcelReader with Schema Access Strategy 'Use Starting Row' and CSVRecordSetWriter (configured with Schema Access Strategy 'Inherit Record Schema', Date Format MM/dd/yyyy, Time Format HH:mm:ss and Timestamp Format MM/dd/yyyy'T'hh:mm:ss)
> The contents from the flow without SplitExcel correctly outputs the dates and times
> {code:java}
> transaction_id,transaction_date,transaction_time
> 75,01/01/2023,09:53:44
> 78,01/01/2023,09:55:16
> 80,01/01/2023,10:00:39
> 81,01/01/2023,10:03:55
> 82,01/01/2023,10:14:49{code}
> and its schema is
> {code:java}
> {"type":"record","name":"nifiRecord","namespace":"org.apache.nifi","fields":[{"name":"transaction_id","type":["long","null"]},{"name":"transaction_date","type":[{"type":"int","logicalType":"date"},"null"]},{"name":"transaction_time","type":[{"type":"int","logicalType":"time-millis"},"null"]}]}{code}
> while the contents from the flow with SplitExcel does not correctly output the dates and times
> {code:java}
> transaction_id,transaction_date,transaction_time
> 75,44927,-1
> 78,44927,-1
> 80,44927,-1
> 81,44927,-1
> 82,44927,-1{code}
> and its schema is
> {code:java}
> {"type":"record","name":"nifiRecord","namespace":"org.apache.nifi","fields":[{"name":"transaction_id","type":["long","null"]},{"name":"transaction_date","type":["long","null"]},{"name":"transaction_time","type":["long","null"]}]}{code}
> Even if the schema for the SplitExcel flow is specified as the one produced by flow without the SplitExcel processor and the header from the attached file is removed, the results are not the correct date and times
> {code:java}
> 75,01/01/1970,23:59:59
> 78,01/01/1970,23:59:59
> 80,01/01/1970,23:59:59
> 81,01/01/1970,23:59:59
> 82,01/01/1970,23:59:59{code}
> It would seem when copying the contents from the original tab into a new Excel spreadsheet (with one tab) that for all dates/times/timestamps the correct epoch must be copied over.



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