Excel input Step- Spread sheet type engine

Hi All,

I have 3 excel files with same format. I am doing some transformations on it. I execute it in loop.
I have selected Spread sheet Type engine as "Excel 2007 Apache POI streaming" in excel input step. It works fine for 2 excel files. But for 1 excel file it does not return any rows.
For that 1 excel file it works with "Excel 2007 Apache POI" Spread sheet Type engine. why so?
What is the diffrence between "Excel 2007 Apache POI streaming" and "Excel 2007 Apache POI" Spread sheet Type engines?
I noticed "Excel 2007 Apache POI streaming" works faster.
So How can I make Spread sheet Type engine as "Excel 2007 Apache POI streaming" work for all 3 excels?
I use penhato 7.1. Windows 7. JRE 1.8

Old "XLS" format files used a binary storage format. New "XLSX" format files are a series of XML files, compressed into a ZIP file format.

Because the newer formats are XML based, the Apache POI library has two options for reading these spreadsheets.

1. Load the XML document into memory and then parse and extract the data for PDI (similar to how web browsers render HTML)
2. Read the XML document in "streaming" mode (it doesn't store the full document in memory, just the minimum needed to read the next part)

When dealing with large files, the first option may lead to the application to freeze/crash, as the in-memory representation of XML is much larger than the file size that is used to save it on your hard drive. Because of this, most people will end up using the streaming option for large files, as it will use much less memory to process an Excel file.

In your case, since two of the three are working in streaming mode, it likely comes down to some specific differences with the 3rd file why it won't read the data in it. I'd recommend reviewing the configuration settings in the step against what is in the Excel file (sheet names, row/column start points, hidden columns in the file etc.). If that doesn't solve it, you may want to reach out to Pentaho Support if you have an Enterprise license, so they can provide more help.