[
https://issues.apache.org/jira/browse/NIFI-16443?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vincenzo Lombardo updated NIFI-16443:
-------------------------------------
Description:
When the ExcelReader is configured with Schema Access Strategy "Use Starting
Row", schema inference fails if any evaluated row has more cells than the
header row:
{noformat}
SchemaNotFoundException: Row 1 has 14 cells, more than the expected 6 number of
field names
{noformat}
This is common with spreadsheets edited by hand or exported from other tools:
cells to the right of the data are often blank but formatted, and Apache POI
includes them in the row. The sheet looks correct in Excel, but cannot be read
without manually cleaning it first.
Proposed change: add an optional property "Ignore Cells Beyond Header" (default
false, so the current behavior is unchanged), visible only with the "Use
Starting Row" strategy:
* cells beyond the last header column are ignored during schema inference;
* blank cells (including whitespace-only strings and formulas evaluating to an
empty string) are ignored silently;
* when ignored cells contain a value, a single warning per sheet is logged,
reporting the number of affected rows and the first position (row and column),
so that data is never dropped silently.
Only the rows evaluated according to the "Row Evaluation Strategy" are checked
for extra cells; rows that are not evaluated during inference are not inspected.
No change is needed in ExcelRecordReader: when reading records it already skips
cells beyond the schema fields (or exposes them as unknown_field_index_N when
unknown fields are not dropped).
was:
When the ExcelReader is configured with Schema Access Strategy "Use Starting
Row", schema inference fails
if any evaluated row has more cells than the header row:
SchemaNotFoundException: Row 1 has 14 cells, more than the expected 6 number
of field names
This is common with spreadsheets edited by hand or exported from other tools:
cells to the right of the
data are often blank but formatted, and Apache POI includes them in. Row looks
correct in Excel, but cannot be read without manually cleaning it first.
Proposed change: add an optional property "Ignore Cells Beyond Header" (default
false, so the current
behavior is unchanged), visible only with the "Use Starting Row" strategy:
- cells beyond the last header column are ignored during schema inference;
- blank cells (including whitespace-only strings and formulas evaluated
ignored silently;
- when ignored cells contain a value, a single warning per sheet is logged,
reporting the number of
affected rows and the first position (row and column), so that data i
Only the rows evaluated according to the "Row Evaluation Strategy" are checked
for extra cells; rows that are not evaluated during inference are not inspected.
No change is needed in ExcelRecordReader: when reading records it already skips
cells beyond the schema fields (or exposes them as unknown_field_index_N when
unknown fields are not dropped).
> Add Ignore Cells Beyond Header property to ExcelReader
> -------------------------------------------------------
>
> Key: NIFI-16443
> URL: https://issues.apache.org/jira/browse/NIFI-16443
> Project: Apache NiFi
> Issue Type: Improvement
> Components: Extensions
> Reporter: Vincenzo Lombardo
> Priority: Major
> Attachments: excel-reader-extra-cells-sample.xlsx
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> When the ExcelReader is configured with Schema Access Strategy "Use Starting
> Row", schema inference fails if any evaluated row has more cells than the
> header row:
> {noformat}
> SchemaNotFoundException: Row 1 has 14 cells, more than the expected 6 number
> of field names
> {noformat}
> This is common with spreadsheets edited by hand or exported from other tools:
> cells to the right of the data are often blank but formatted, and Apache POI
> includes them in the row. The sheet looks correct in Excel, but cannot be
> read without manually cleaning it first.
> Proposed change: add an optional property "Ignore Cells Beyond Header"
> (default false, so the current behavior is unchanged), visible only with the
> "Use Starting Row" strategy:
> * cells beyond the last header column are ignored during schema inference;
> * blank cells (including whitespace-only strings and formulas evaluating to
> an empty string) are ignored silently;
> * when ignored cells contain a value, a single warning per sheet is logged,
> reporting the number of affected rows and the first position (row and
> column), so that data is never dropped silently.
> Only the rows evaluated according to the "Row Evaluation Strategy" are
> checked for extra cells; rows that are not evaluated during inference are not
> inspected.
> No change is needed in ExcelRecordReader: when reading records it already
> skips cells beyond the schema fields (or exposes them as
> unknown_field_index_N when unknown fields are not dropped).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
