Filehelpers ExcelStorage.ExtractRecords fails when first cell is empty

Question

When the first cell of an excel sheet to import using ExcelStorage.ExtractRecords is empty, the process fail. Ie. If the data starts at col 1, row 2, if the cell (2,1) has an empty value, the method fails.

Does anybody know how to work-around this? I've tried adding a FieldNullValue attribute to the mapping class with no luck.

Here is a sample project that show the code with problems

Hope somebody can help me or point in some direction.

Thank you!

Answer 1

It looks like you have stumbled upon an issue in FileHelpers.

What is happening is that the ExcelStorage.ExtractRecords method uses an empty cell check to see if it has reached the end of the sheet. This can be seen in the ExcelStorage.cs source code:

while (CellAsString(cRow, mStartColumn) != String.Empty)
{
    try
    {
        recordNumber++;
        Notify(mNotifyHandler, mProgressMode, recordNumber, -1);

        colValues = RowValues(cRow, mStartColumn, RecordFieldCount);

        object record = ValuesToRecord(colValues);
        res.Add(record);

    }
    catch (Exception ex)
    {
        // Code removed for this example
    }
}

So if the start column of any row is empty then it assumes that the file is done.

Some options to get around this:

1. Don't put any empty cells in the first column position.
2. Don't use excel as your file format -- convert to CSV first.
3. See if you can get a patch from the developer or patch the source yourself.

The first two are workarounds (and not really good ones). The third option might be the best but what is the end of file condition? Probably an entire row that is empty would be a good enough check (but even that might not work in all cases all of the time).