Microsoft Excel Writer/Input mishandling empty strings and null values when using streaming

This issue occurs both in the PDI Client and on the Pentaho Server.

These Transformations that use the Microsoft Excel Writer and the Microsoft Excel Input steps.

First we write an xlsx file with the Microsoft Excel Writer step using Streaming. This file is readable by Microsoft Excel

Then we read the generated file with an Microsoft Excel Input step using Streaming.

We have observed some issues related to cells that contain an:

• Empty String
• Null

The observations are summarized below in the following two scenarios:

SCENARIO 1

1.1: A file is created containing an:

• Empty String (BOXED in BLUE)
• Null (BOXED in ORANGE)

neither of these cells are the last cell in their row.

1.2: After the file has been read the following can be seen:

• The cell that contains the Empty String has its value changed to the value of the next cell in the row. (BOXED in RED)

• The next cell in the row has its value changed to Null. (BOXED in ORANGE)

• The Microsoft Excel Input step does not continue reading rows after it reaches the row containing the Null value. (BOXED in BLUE)

SCENARIO 2

2.1: A file is created containing an:

• Empty String (BOXED in BLUE)
• Null (BOXED in ORANGE)

the cell with the Empty String is the last cell in its row.

2.2: After the file has been read the following can be seen:

• The cell that contains the Empty String has its value replaced by the value of the first cell of the next row. (BOXED in RED)

• The next row values are replaced by the values of the row coming after. (BOXED in ORANGE)

• The row coming after is repeated (BOXED in BLUE)

• The Microsoft Excel Input step does not continue reading rows after it reaches the row containing the Null value. (BOXED in PURPLE)

NOTE: These issues DO NOT occur if either of the following changes are made:

1: Microsoft Excel Writer step NOT using Streaming. (BOXED in GREEN)

2: Microsoft Excel Input step NOT using Streaming. (BOXED in GREEN)