Inserting data with UCanAccess from big text files is very slow - Processing Nulls

Since am not allowed to comment on the original discussion (Inserting data with UCanAccess from big text files is very slow) because I don't have enough points, am having to start a new one.

I have implemented the code discussed above (thank you Gord Thompson), but it is not handing nulls. When I either use the word 'null' (without quotes) or simply leave no value in my CSV field, it will not import into a predefined table (in this case a nullable double field). This even includes when I implement my own com.healthmarketscience.jackcess.util.ImportFilter.

Looking at the Jackcess code, I see the problem in com.healthmarketscience.jackcess.impl.ColumnImpl (in Jackcess 4.0.0 JAR). The relevant method is the ColumnImpl::toNumber method, where, in the event of encountering a null, returns a BigDecimal.ZERO, which is obviously not null

Having said all that, my code is failing in ColumnImpl::toNumber on the return statement at the end...

return Double.valueOf(value.toString());

...meaning I have not successfully passed in a null at this point, just an empty string. I can override this within the ImportFilter implementation, but as indicated above, it would still return the non-null BigDecimal.ZERO value.

Not sure how to get around this other than return special values that I can convert to null with successive update queries. Also, predefined table / field level macros in MS Access do not seem to get triggered when importing data (at least not that I've seen so far). Either solution is not ideal though, it would be nice if nulls were handled properly.

Any ideas?

Thanks,

Matthew

P. S. trying to do a call out to Gord Thompson, but the '@' thing is not working for me (not enough points, perhaps?).

The following logic for ImportFilter::filterRow works (at least for my scenario in which I don't use empty strings)...

    public Object[] filterRow(Object[] row) throws SQLException, IOException {

    Object[] lclReturn = new Object[row.length];

    for (int i = 0; i < row.length; i++) {

        Object lclObject = row[i];

        if (lclObject != null) {

        if (!"".equals(lclObject.toString().trim())) {
            lclReturn[i] = lclObject;
        } else {
            lclReturn[i] = null;
        }

        } else {
        lclReturn[i] = null;
        }

    }

    return lclReturn;
    }