This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new c7230593bb Extra option to perserve null values, fixes #5725 (#7826)
c7230593bb is described below
commit c7230593bbf0a735b8174263d97c25612cd3ddc6
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Fri Aug 7 12:55:54 2026 +0200
Extra option to perserve null values, fixes #5725 (#7826)
---
.../pages/pipeline/transforms/textfileinput.adoc | 1 +
.../transforms/common/ICsvInputAwareMeta.java | 10 +
.../0040-text-file-input-null-if-not-enclosed.hpl | 537 +++++++++++++++++++++
.../files/text-file-input-null-if-not-enclosed.csv | 4 +
.../transforms/main-0040-text-file-input.hwf | 33 ++
.../fileinput/text/TextFileInputDialog.java | 27 +-
.../fileinput/text/TextFileInputMeta.java | 16 +
.../fileinput/text/TextFileInputUtils.java | 25 +-
.../text/messages/messages_en_US.properties | 3 +
.../text/TextFileInputContentParsingTest.java | 90 ++++
.../fileinput/text/files/null-if-not-enclosed.csv | 5 +
11 files changed, 745 insertions(+), 6 deletions(-)
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/textfileinput.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/textfileinput.adoc
index f71f84eb24..bcc785786a 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/textfileinput.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/textfileinput.adoc
@@ -127,6 +127,7 @@ Special characters (e.g. CHAR ASCII HEX01) can be set with
the format $[value],
For example, if you have \ as an escape character, the text 'Not the nine
o\'clock news' (where ' is the the enclosure character) gets parsed as Not the
nine o'clock news instead of splitting the field value.
Special characters (e.g. CHAR HEX01) can be set with the format $[value], e.g.
$[01] or $[6F,FF,00,1F].
The Escape character may be left empty, or set to the same character as the
Enclosure. In both cases a doubled enclosure inside an enclosed field (for
example "" when " is the enclosure) is treated as a single, literal enclosure
character and is de-duplicated in the resulting value, following the common CSV
convention (so "she said ""hi""" is read as she said "hi"). This also works
when "Allow breaks in enclosed fields?" is enabled.
+|Return null for empty values which are not enclosed?|Enable to make a
difference between an empty value and a missing value. An empty value which is
not enclosed (`a;;b`) is then returned as null, while an empty enclosed value
(`a;"";b`) is returned as an empty string. A line ending on a separator
(`a;b;`) also returns null for that last value. When this option is disabled
(the default) both are handled the same way, as they always were. To keep the
difference between an empty string an [...]
|Prepend filename to headers|Enable to add the filename to each field in the
file. This can help when you are joining data by making it clear which file
each field comes from.
|Header|Enable if your text file has a header row (first lines in the file).
These typically determine the column names used in the transform. If you set
this on a file that does not contain headers, the data values in the first row
are interpreted as headers, which will likely produce unexpected results. In
this case, if the field is blank, it will be named EmptyField_n, where n is the
column's position in the row. Always check the field set to ensure the column
names and data types hav [...]
|Number of header lines|Specify the number of rows encompass the header in the
file.
diff --git
a/engine/src/main/java/org/apache/hop/pipeline/transforms/common/ICsvInputAwareMeta.java
b/engine/src/main/java/org/apache/hop/pipeline/transforms/common/ICsvInputAwareMeta.java
index 99ae4a5b72..1cc4d19b8b 100644
---
a/engine/src/main/java/org/apache/hop/pipeline/transforms/common/ICsvInputAwareMeta.java
+++
b/engine/src/main/java/org/apache/hop/pipeline/transforms/common/ICsvInputAwareMeta.java
@@ -52,6 +52,16 @@ public interface ICsvInputAwareMeta<T extends
ITextFileInputField> {
boolean isBreakInEnclosureAllowed();
+ /**
+ * When enabled, an empty field that is not enclosed (a;;b) is returned as
null while an empty
+ * enclosed field (a;"";b) is returned as an empty string.
+ *
+ * @return true if empty values without enclosure have to be returned as null
+ */
+ default boolean isNullIfNotEnclosed() {
+ return false;
+ }
+
int getFileFormatTypeNr();
boolean hasHeader();
diff --git
a/integration-tests/transforms/0040-text-file-input-null-if-not-enclosed.hpl
b/integration-tests/transforms/0040-text-file-input-null-if-not-enclosed.hpl
new file mode 100644
index 0000000000..152e5b24e4
--- /dev/null
+++ b/integration-tests/transforms/0040-text-file-input-null-if-not-enclosed.hpl
@@ -0,0 +1,537 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <pipeline_version/>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <pipeline_type>Normal</pipeline_type>
+ <pipeline_status>0</pipeline_status>
+ <parameters/>
+ <name>0040-text-file-input-null-if-not-enclosed</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Issue #5725: with the "Return null for empty values which are
not enclosed" option (null_if_not_enclosed) an empty value which is not
enclosed (a;;b) is a null while an empty enclosed value (a;"";b) is an empty
string. The Coalesce transform decides on the real Java null (it does not use
the HOP_EMPTY_STRING_DIFFERS_FROM_NULL interpretation), so it maps every null
to the WAS_NULL marker and leaves the empty strings alone. Every row of the
file has the same shape: empty_v [...]
+ <extended_description/>
+ <created_user>-</created_user>
+ <modified_user>-</modified_user>
+ <created_date>2026/08/07 12:00:00.000</created_date>
+ <modified_date>2026/08/07 12:00:00.000</modified_date>
+ </info>
+ <transform>
+ <type>TextFileInput2</type>
+ <name>Read CSV</name>
+ <file_type>CSV</file_type>
+ <compression>None</compression>
+ <separator>;</separator>
+ <enclosure>"</enclosure>
+ <enclosure_breaks>N</enclosure_breaks>
+ <escapechar/>
+ <null_if_not_enclosed>Y</null_if_not_enclosed>
+ <header>Y</header>
+ <prependFileName>N</prependFileName>
+ <nr_headerlines>1</nr_headerlines>
+ <footer>N</footer>
+ <nr_footerlines>1</nr_footerlines>
+ <line_wrapped>N</line_wrapped>
+ <nr_wraps>1</nr_wraps>
+ <layout_paged>N</layout_paged>
+ <nr_lines_per_page>80</nr_lines_per_page>
+ <nr_lines_doc_header>0</nr_lines_doc_header>
+ <noempty>Y</noempty>
+ <include>N</include>
+ <include_field/>
+ <rownum>N</rownum>
+ <rownum_field/>
+ <rownumByFile>N</rownumByFile>
+ <format>mixed</format>
+ <encoding>UTF-8</encoding>
+ <limit>0</limit>
+ <date_format_lenient>Y</date_format_lenient>
+ <date_format_locale>en_US</date_format_locale>
+ <length>Characters</length>
+ <filters/>
+ <error_count_field/>
+ <error_fields_field/>
+ <error_text_field/>
+ <error_line_skipped>N</error_line_skipped>
+ <schema_definition/>
+ <ignore_fields>N</ignore_fields>
+ <shortFileFieldName/>
+ <extensionFieldName/>
+ <pathFieldName/>
+ <sizeFieldName/>
+ <hiddenFieldName/>
+ <lastModificationTimeFieldName/>
+ <uriNameFieldName/>
+ <rootUriNameFieldName/>
+ <files>
+ <file>
+
<name>${PROJECT_HOME}/files/text-file-input-null-if-not-enclosed.csv</name>
+ <filemask/>
+ <exclude_filemask/>
+ <file_required>N</file_required>
+ <include_subfolders>N</include_subfolders>
+ </file>
+ </files>
+ <accept_filenames>N</accept_filenames>
+ <accept_transform_name/>
+ <passing_through_fields>N</passing_through_fields>
+ <accept_field/>
+ <add_to_result_filenames>N</add_to_result_filenames>
+ <error_ignored>N</error_ignored>
+ <file_error_field/>
+ <file_error_message_field/>
+ <skip_bad_files>N</skip_bad_files>
+ <bad_line_files_destination_directory/>
+ <bad_line_files_extension>warning</bad_line_files_extension>
+ <error_line_files_destination_directory/>
+ <error_line_files_extension>error</error_line_files_extension>
+ <line_number_files_destination_directory/>
+ <line_number_files_extension>line</line_number_files_extension>
+ <fields>
+ <field>
+ <name>id</name>
+ <position>-1</position>
+ <length>-1</length>
+ <type>String</type>
+ <ignore>N</ignore>
+ <format/>
+ <trim_type>none</trim_type>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <repeat>N</repeat>
+ <null_string/>
+ <if_null_value/>
+ </field>
+ <field>
+ <name>empty_value</name>
+ <position>-1</position>
+ <length>-1</length>
+ <type>String</type>
+ <ignore>N</ignore>
+ <format/>
+ <trim_type>none</trim_type>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <repeat>N</repeat>
+ <null_string/>
+ <if_null_value/>
+ </field>
+ <field>
+ <name>null_value</name>
+ <position>-1</position>
+ <length>-1</length>
+ <type>String</type>
+ <ignore>N</ignore>
+ <format/>
+ <trim_type>none</trim_type>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <repeat>N</repeat>
+ <null_string/>
+ <if_null_value/>
+ </field>
+ <field>
+ <name>plain_value</name>
+ <position>-1</position>
+ <length>-1</length>
+ <type>String</type>
+ <ignore>N</ignore>
+ <format/>
+ <trim_type>none</trim_type>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <repeat>N</repeat>
+ <null_string/>
+ <if_null_value/>
+ </field>
+ <field>
+ <name>trailing_value</name>
+ <position>-1</position>
+ <length>-1</length>
+ <type>String</type>
+ <ignore>N</ignore>
+ <format/>
+ <trim_type>none</trim_type>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <repeat>N</repeat>
+ <null_string/>
+ <if_null_value/>
+ </field>
+ </fields>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>128</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Constant</type>
+ <name>Add marker</name>
+ <fields>
+ <field>
+ <currency/>
+ <decimal/>
+ <group/>
+ <nullif>WAS_NULL</nullif>
+ <name>marker</name>
+ <type>String</type>
+ <format/>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>320</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Coalesce</type>
+ <name>Mark the nulls</name>
+ <fields>
+ <field>
+ <name>empty_check</name>
+ <type>String</type>
+ <remove>N</remove>
+ <input>empty_value, marker</input>
+ </field>
+ <field>
+ <name>null_check</name>
+ <type>String</type>
+ <remove>N</remove>
+ <input>null_value, marker</input>
+ </field>
+ <field>
+ <name>trailing_check</name>
+ <type>String</type>
+ <remove>N</remove>
+ <input>trailing_value, marker</input>
+ </field>
+ </fields>
+ <empty_is_null>N</empty_is_null>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>512</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>FilterRows</type>
+ <name>Anything wrong?</name>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <function>=</function>
+ <conditions>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <leftvalue>empty_check</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>WAS_NULL</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ <mask/>
+ </value>
+ <conditions/>
+ </condition>
+ <condition>
+ <negated>N</negated>
+ <operator>OR</operator>
+ <leftvalue>null_check</leftvalue>
+ <function><></function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>WAS_NULL</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ <mask/>
+ </value>
+ <conditions/>
+ </condition>
+ <condition>
+ <negated>N</negated>
+ <operator>OR</operator>
+ <leftvalue>trailing_check</leftvalue>
+ <function><></function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>WAS_NULL</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ <mask/>
+ </value>
+ <conditions/>
+ </condition>
+ <condition>
+ <negated>N</negated>
+ <operator>OR</operator>
+ <leftvalue>plain_value</leftvalue>
+ <function><></function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>abc</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ <mask/>
+ </value>
+ <conditions/>
+ </condition>
+ </conditions>
+ </condition>
+ </compare>
+ <send_true_to>Wrong values - Abort</send_true_to>
+ <send_false_to>Count rows</send_false_to>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>704</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Abort</type>
+ <name>Wrong values - Abort</name>
+ <row_threshold>0</row_threshold>
+ <message>An enclosed empty value ("") must stay an empty string and a
non-enclosed empty value must become null (empty_check may not be WAS_NULL,
null_check and trailing_check must be)</message>
+ <always_log_rows>Y</always_log_rows>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>704</xloc>
+ <yloc>272</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>GroupBy</type>
+ <name>Count rows</name>
+ <all_rows>N</all_rows>
+ <directory>${java.io.tmpdir}</directory>
+ <prefix>grp</prefix>
+ <ignore_aggregate>N</ignore_aggregate>
+ <field_ignore/>
+ <group/>
+ <fields>
+ <field>
+ <aggregate>num_rows</aggregate>
+ <subject>id</subject>
+ <type>COUNT_ANY</type>
+ <valuefield/>
+ </field>
+ </fields>
+ <add_linenr>N</add_linenr>
+ <linenr_fieldname/>
+ <give_back_row>N</give_back_row>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>896</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>FilterRows</type>
+ <name>Check row count</name>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <leftvalue>num_rows</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>3</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ <conditions/>
+ </condition>
+ </compare>
+ <send_true_to>Success</send_true_to>
+ <send_false_to>Wrong row count - Abort</send_false_to>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>1088</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Abort</type>
+ <name>Wrong row count - Abort</name>
+ <row_threshold>0</row_threshold>
+ <message>Unexpected row count (expected the 3 data rows of the
file)</message>
+ <always_log_rows>Y</always_log_rows>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>1088</xloc>
+ <yloc>272</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Dummy</type>
+ <name>Success</name>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>1280</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <order>
+ <hop>
+ <from>Read CSV</from>
+ <to>Add marker</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Add marker</from>
+ <to>Mark the nulls</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Mark the nulls</from>
+ <to>Anything wrong?</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Anything wrong?</from>
+ <to>Wrong values - Abort</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Anything wrong?</from>
+ <to>Count rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Count rows</from>
+ <to>Check row count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Check row count</from>
+ <to>Success</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Check row count</from>
+ <to>Wrong row count - Abort</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <notepads/>
+ <attributes/>
+ <transform_error_handling/>
+</pipeline>
diff --git
a/integration-tests/transforms/files/text-file-input-null-if-not-enclosed.csv
b/integration-tests/transforms/files/text-file-input-null-if-not-enclosed.csv
new file mode 100644
index 0000000000..16c1d939be
--- /dev/null
+++
b/integration-tests/transforms/files/text-file-input-null-if-not-enclosed.csv
@@ -0,0 +1,4 @@
+id;empty_value;null_value;plain_value;trailing_value
+1;"";;abc;
+2;"";;abc;
+3;"";;abc;
diff --git a/integration-tests/transforms/main-0040-text-file-input.hwf
b/integration-tests/transforms/main-0040-text-file-input.hwf
index 6214b15545..9dfd993b1a 100644
--- a/integration-tests/transforms/main-0040-text-file-input.hwf
+++ b/integration-tests/transforms/main-0040-text-file-input.hwf
@@ -115,6 +115,32 @@ limitations under the License.
<parallel>N</parallel>
<attributes_hac/>
</action>
+ <action>
+
<filename>${PROJECT_HOME}/0040-text-file-input-null-if-not-enclosed.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <create_parent_folder>N</create_parent_folder>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
+ <name>0040-text-file-input-null-if-not-enclosed.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>1280</xloc>
+ <yloc>112</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
</actions>
<hops>
<hop>
@@ -138,6 +164,13 @@ limitations under the License.
<unconditional>N</unconditional>
<enabled>Y</enabled>
</hop>
+ <hop>
+ <from>0040-text-file-input-enclosure.hpl</from>
+ <to>0040-text-file-input-null-if-not-enclosed.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ <enabled>Y</enabled>
+ </hop>
</hops>
<notepads>
<notepad>
diff --git
a/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputDialog.java
b/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputDialog.java
index d24a1d8b69..8600fc8558 100644
---
a/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputDialog.java
+++
b/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputDialog.java
@@ -175,6 +175,8 @@ public class TextFileInputDialog extends BaseTransformDialog
private Button wEnclBreaks;
+ private Button wNullIfNotEnclosed;
+
private Label wlNrHeader;
private Text wNrHeader;
@@ -994,6 +996,27 @@ public class TextFileInputDialog extends
BaseTransformDialog
fdEscape.right = new FormAttachment(100, 0);
wEscape.setLayoutData(fdEscape);
+ // Return null for empty values which are not enclosed
+ Label wlNullIfNotEnclosed = new Label(wContentComp, SWT.RIGHT);
+ wlNullIfNotEnclosed.setText(
+ BaseMessages.getString(PKG,
"TextFileInputDialog.NullIfNotEnclosed.Label"));
+ wlNullIfNotEnclosed.setToolTipText(
+ BaseMessages.getString(PKG,
"TextFileInputDialog.NullIfNotEnclosed.Tooltip"));
+ PropsUi.setLook(wlNullIfNotEnclosed);
+ FormData fdlNullIfNotEnclosed = new FormData();
+ fdlNullIfNotEnclosed.left = new FormAttachment(0, 0);
+ fdlNullIfNotEnclosed.top = new FormAttachment(wEscape, margin);
+ fdlNullIfNotEnclosed.right = new FormAttachment(middle, -margin);
+ wlNullIfNotEnclosed.setLayoutData(fdlNullIfNotEnclosed);
+ wNullIfNotEnclosed = new Button(wContentComp, SWT.CHECK);
+ wNullIfNotEnclosed.setToolTipText(
+ BaseMessages.getString(PKG,
"TextFileInputDialog.NullIfNotEnclosed.Tooltip"));
+ PropsUi.setLook(wNullIfNotEnclosed);
+ FormData fdNullIfNotEnclosed = new FormData();
+ fdNullIfNotEnclosed.left = new FormAttachment(middle, 0);
+ fdNullIfNotEnclosed.top = new FormAttachment(wlNullIfNotEnclosed, 0,
SWT.CENTER);
+ wNullIfNotEnclosed.setLayoutData(fdNullIfNotEnclosed);
+
// Addition Prepend file name
Label wlPrependFileName = new Label(wContentComp, SWT.RIGHT);
wlPrependFileName.setText(
@@ -1001,7 +1024,7 @@ public class TextFileInputDialog extends
BaseTransformDialog
PropsUi.setLook(wlPrependFileName);
FormData fdlPrependFileName = new FormData();
fdlPrependFileName.left = new FormAttachment(0, 0);
- fdlPrependFileName.top = new FormAttachment(wEscape, margin);
+ fdlPrependFileName.top = new FormAttachment(wNullIfNotEnclosed, margin);
fdlPrependFileName.right = new FormAttachment(middle, -margin);
wlPrependFileName.setLayoutData(fdlPrependFileName);
wPrependFileName = new Button(wContentComp, SWT.CHECK);
@@ -2317,6 +2340,7 @@ public class TextFileInputDialog extends
BaseTransformDialog
wEnclosure.setText(Const.NVL(meta.getContent().getEnclosure(), ""));
wEscape.setText(Const.NVL(meta.getContent().getEscapeCharacter(), ""));
wEnclBreaks.setSelection(meta.getContent().isBreakInEnclosureAllowed());
+ wNullIfNotEnclosed.setSelection(meta.getContent().isNullIfNotEnclosed());
wPrependFileName.setSelection(meta.getContent().isPrependFileName());
wHeader.setSelection(meta.getContent().isHeader());
wNrHeader.setText("" + meta.getContent().getNrHeaderLines());
@@ -2534,6 +2558,7 @@ public class TextFileInputDialog extends
BaseTransformDialog
meta.getContent().setEnclosure(wEnclosure.getText());
meta.getContent().setEscapeCharacter(wEscape.getText());
meta.getContent().setBreakInEnclosureAllowed(wEnclBreaks.getSelection());
+ meta.getContent().setNullIfNotEnclosed(wNullIfNotEnclosed.getSelection());
meta.getContent().setRowLimit(Const.toLongExpanded(wLimit.getText(), 0L));
meta.getContent().setFilenameField(wInclFilenameField.getText());
meta.getContent().setRowNumberField(wInclRownumField.getText());
diff --git
a/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputMeta.java
b/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputMeta.java
index 4c123242a9..2001830aeb 100644
---
a/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputMeta.java
+++
b/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputMeta.java
@@ -139,6 +139,16 @@ public class TextFileInputMeta
injectionKeyDescription = "TextFileInput.Injection.ESCAPE_CHAR")
private String escapeCharacter;
+ /**
+ * Flag indicating that an empty value which is not enclosed has to be
returned as null. An
+ * empty value between enclosures then stays an empty string.
+ */
+ @HopMetadataProperty(
+ key = "null_if_not_enclosed",
+ injectionKey = "NULL_IF_NOT_ENCLOSED",
+ injectionKeyDescription =
"TextFileInput.Injection.NULL_IF_NOT_ENCLOSED")
+ private boolean nullIfNotEnclosed;
+
/** Flag indicating that the file contains one header line that should be
skipped. */
@HopMetadataProperty(
key = "header",
@@ -317,6 +327,7 @@ public class TextFileInputMeta
this.length = c.length;
this.lineWrapped = c.lineWrapped;
this.noEmptyLines = c.noEmptyLines;
+ this.nullIfNotEnclosed = c.nullIfNotEnclosed;
this.nrFooterLines = c.nrFooterLines;
this.nrHeaderLines = c.nrHeaderLines;
this.nrLinesDocHeader = c.nrLinesDocHeader;
@@ -890,6 +901,11 @@ public class TextFileInputMeta
return content.breakInEnclosureAllowed;
}
+ @Override
+ public boolean isNullIfNotEnclosed() {
+ return content != null && content.nullIfNotEnclosed;
+ }
+
@Override
public FileObject getHeaderFileObject(final IVariables variables) {
final FileInputList fileList = getFileInputList(variables);
diff --git
a/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputUtils.java
b/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputUtils.java
index f450b72ad4..66efea1558 100644
---
a/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputUtils.java
+++
b/plugins/transforms/textfile/src/main/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputUtils.java
@@ -409,6 +409,7 @@ public class TextFileInputUtils {
String[] strings =
convertLineToStrings(log, textFileLine.line, info, delimiter,
enclosure, escapeCharacter);
int shiftFields = (passThruFields == null ? 0 : nrPassThruFields);
+ boolean nullIfNotEnclosed = info.isNullIfNotEnclosed();
for (fieldnr = 0; fieldnr < nrFields; fieldnr++) {
T f = info.getInputFields().get(fieldnr);
int valueIndex = shiftFields + fieldnr;
@@ -424,10 +425,19 @@ public class TextFileInputUtils {
if (strings != null && fieldnr < strings.length) {
String pol = strings[fieldnr];
try {
- if (valueMeta.isNull(pol) || !Utils.isEmpty(nullif) &&
nullif.equals(pol)) {
- pol = null;
+ if (nullIfNotEnclosed && pol == null) {
+ // Nothing was there: a value which is empty and not enclosed,
or a missing trailing
+ // value. That's a null, also when
HOP_EMPTY_STRING_DIFFERS_FROM_NULL is set to Y
+ // (which would otherwise turn it into an empty string).
+ value = null;
+ } else {
+ // An empty enclosed value ("") is an empty string, so don't
null it out.
+ if ((!nullIfNotEnclosed || !pol.isEmpty())
+ && (valueMeta.isNull(pol) || !Utils.isEmpty(nullif) &&
nullif.equals(pol))) {
+ pol = null;
+ }
+ value = valueMeta.convertDataFromString(pol, convertMeta,
nullif, ifNull, trimType);
}
- value = valueMeta.convertDataFromString(pol, convertMeta, nullif,
ifNull, trimType);
} catch (Exception e) {
// OK, give some feedback!
// when getting fields, failOnParseError will be set to false, as
we do not want one
@@ -623,6 +633,7 @@ public class TextFileInputUtils {
int pos = 0;
int length = line.length();
boolean dencl = false;
+ boolean nullIfNotEnclosed = inf.isNullIfNotEnclosed();
int lenEncl = (enclosure == null ? 0 : enclosure.length());
int lenEsc = (escapeCharacters == null ? 0 :
escapeCharacters.length());
@@ -833,8 +844,11 @@ public class TextFileInputUtils {
}
// Now add pol to the strings found!
+ // With "null if not enclosed" an empty value which was not enclosed
(a;;b) is passed on
+ // as null while an empty enclosed value (a;"";b) stays an empty
string. convertLineToRow
+ // turns that null into a null value in the output row.
try {
- strings[fieldnr] = pol;
+ strings[fieldnr] = nullIfNotEnclosed && !enclFound &&
pol.isEmpty() ? null : pol;
} catch (ArrayIndexOutOfBoundsException e) {
// In case we didn't allocate enough variables.
// This happens when you have less header values specified than
there are actual values
@@ -858,7 +872,8 @@ public class TextFileInputUtils {
BaseMessages.getString(PKG,
"TextFileInput.Log.EndOfEmptyLineFound"));
}
if (fieldnr < strings.length) {
- strings[fieldnr] = Const.EMPTY_STRING;
+ // A line ending on a separator (a;b;) leaves an empty,
non-enclosed last value.
+ strings[fieldnr] = nullIfNotEnclosed ? null : Const.EMPTY_STRING;
}
fieldnr++;
}
diff --git
a/plugins/transforms/textfile/src/main/resources/org/apache/hop/pipeline/transforms/fileinput/text/messages/messages_en_US.properties
b/plugins/transforms/textfile/src/main/resources/org/apache/hop/pipeline/transforms/fileinput/text/messages/messages_en_US.properties
index 70a11e930f..0ec923aee0 100644
---
a/plugins/transforms/textfile/src/main/resources/org/apache/hop/pipeline/transforms/fileinput/text/messages/messages_en_US.properties
+++
b/plugins/transforms/textfile/src/main/resources/org/apache/hop/pipeline/transforms/fileinput/text/messages/messages_en_US.properties
@@ -129,6 +129,7 @@ TextFileInput.Injection.NR_FOOTER_LINES=The number of
footer lines
TextFileInput.Injection.NR_HEADER_LINES=The number of header lines
TextFileInput.Injection.NR_LINES_PER_PAGE=The number of lines per page
TextFileInput.Injection.NR_WRAPS=The number of times a line is wrapped
+TextFileInput.Injection.NULL_IF_NOT_ENCLOSED=Return null for empty values
which are not enclosed? (Y/N)
TextFileInput.Injection.PASS_THROUGH_FIELDS=Pass through fields? (Y/N)
TextFileInput.Injection.ROW_LIMIT=The maximum number of lines to read.
TextFileInput.Injection.ROW_NUMBER_BY_FILE=Reset the row number for each file?
(Y/N)
@@ -268,6 +269,8 @@ TextFileInputDialog.NrHeader.Label=Number of header lines
TextFileInputDialog.NrLinesDocHeader.Label=Document header lines
TextFileInputDialog.NrLinesPerPage.Label=Number of lines per page
TextFileInputDialog.NrWraps.Label=Number of times wrapped
+TextFileInputDialog.NullIfNotEnclosed.Label=Return null for empty values which
are not enclosed?
+TextFileInputDialog.NullIfNotEnclosed.Tooltip=Check this to make a difference
between an empty value and a missing value.\nAn empty value which is not
enclosed (a;;b) then returns null, an empty enclosed value (a;"";b) returns an
empty string.\nTo keep that difference in the transforms downstream, also set
the HOP_EMPTY_STRING_DIFFERS_FROM_NULL variable to Y.
TextFileInputDialog.NullIfColumn.Column=Null if
TextFileInputDialog.PassThruFields.Label=Pass through fields from previous
transform
TextFileInputDialog.PassThruFields.Tooltip=Enable this if you have other
fields in the previous transform\nand you want those fields to appear in every
record
diff --git
a/plugins/transforms/textfile/src/test/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputContentParsingTest.java
b/plugins/transforms/textfile/src/test/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputContentParsingTest.java
index 6335105585..cc8ec0bad2 100644
---
a/plugins/transforms/textfile/src/test/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputContentParsingTest.java
+++
b/plugins/transforms/textfile/src/test/java/org/apache/hop/pipeline/transforms/fileinput/text/TextFileInputContentParsingTest.java
@@ -22,6 +22,7 @@ import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertTrue;
import java.util.List;
+import org.apache.hop.core.Const;
import org.apache.hop.core.file.TextFileInputField;
import org.apache.hop.core.variables.Variables;
import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
@@ -140,6 +141,95 @@ class TextFileInputContentParsingTest extends
BaseTextParsingTest {
check(new Object[][] {{"first", "1", "1.1"}, {"second", "2", "2.2"},
{"third", "3", "3.3"}});
}
+ /**
+ * Issue #5725: with "Return null for empty values which are not enclosed" a
value which is empty
+ * and not enclosed (a;;b) is a null, while an empty enclosed value (a;"";b)
is an empty string.
+ */
+ @Test
+ void testNullIfNotEnclosed() throws Exception {
+ meta.getContent().setFileFormat("unix");
+ meta.getContent().setNullIfNotEnclosed(true);
+
+ initByFile("null-if-not-enclosed.csv");
+
+ setFields(
+ new TextFileInputField("id", -1, -1),
+ new TextFileInputField("empty_column", -1, -1),
+ new TextFileInputField("null_column", -1, -1),
+ new TextFileInputField("label", -1, -1));
+
+ process();
+
+ check(
+ new Object[][] {
+ {"1", "", null, "a label"},
+ {"2", null, "", "other"},
+ // A line ending on a separator leaves an empty, non-enclosed last
value.
+ {"3", "x", "y", null},
+ {"4", "x", "y", ""}
+ });
+ }
+
+ /**
+ * The scenario from issue #5725: with HOP_EMPTY_STRING_DIFFERS_FROM_NULL
every empty value used
+ * to end up as an empty string, so a missing value could never be told
apart from an enclosed
+ * empty one. The option has to return the non-enclosed values as null
anyway.
+ */
+ @Test
+ void testNullIfNotEnclosedWithEmptyStringDiffersFromNull() throws Exception {
+ System.setProperty(Const.HOP_EMPTY_STRING_DIFFERS_FROM_NULL, "Y");
+ try {
+ meta.getContent().setFileFormat("unix");
+ meta.getContent().setNullIfNotEnclosed(true);
+
+ initByFile("null-if-not-enclosed.csv");
+
+ setFields(
+ new TextFileInputField("id", -1, -1),
+ new TextFileInputField("empty_column", -1, -1),
+ new TextFileInputField("null_column", -1, -1),
+ new TextFileInputField("label", -1, -1));
+
+ process();
+
+ check(
+ new Object[][] {
+ {"1", "", null, "a label"},
+ {"2", null, "", "other"},
+ {"3", "x", "y", null},
+ {"4", "x", "y", ""}
+ });
+ } finally {
+ System.clearProperty(Const.HOP_EMPTY_STRING_DIFFERS_FROM_NULL);
+ }
+ }
+
+ /**
+ * Without the option both an empty and an empty enclosed value stay null,
as they always were.
+ */
+ @Test
+ void testNullIfNotEnclosedDisabled() throws Exception {
+ meta.getContent().setFileFormat("unix");
+
+ initByFile("null-if-not-enclosed.csv");
+
+ setFields(
+ new TextFileInputField("id", -1, -1),
+ new TextFileInputField("empty_column", -1, -1),
+ new TextFileInputField("null_column", -1, -1),
+ new TextFileInputField("label", -1, -1));
+
+ process();
+
+ check(
+ new Object[][] {
+ {"1", null, null, "a label"},
+ {"2", null, null, "other"},
+ {"3", "x", "y", null},
+ {"4", "x", "y", null}
+ });
+ }
+
@Test
void testSeparator() throws Exception {
diff --git
a/plugins/transforms/textfile/src/test/resources/org/apache/hop/pipeline/transforms/fileinput/text/files/null-if-not-enclosed.csv
b/plugins/transforms/textfile/src/test/resources/org/apache/hop/pipeline/transforms/fileinput/text/files/null-if-not-enclosed.csv
new file mode 100644
index 0000000000..9681286cdc
--- /dev/null
+++
b/plugins/transforms/textfile/src/test/resources/org/apache/hop/pipeline/transforms/fileinput/text/files/null-if-not-enclosed.csv
@@ -0,0 +1,5 @@
+id;empty_column;null_column;label
+1;"";;"a label"
+2;;"";other
+3;x;y;
+4;x;y;""
