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?).

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?).

• java
• ms-access
• null
• ucanaccess
• jackcess

Share Improve this question Follow asked Mar 26 at 19:20 lastfreedomlastfreedom 4166 bronze badges 2

• 1 can you provide the stack trace of the failure are seeing? a null value should not ever make it into the toNumber() logic. if you implemented your filter to pass in a proper null value for blank values, then that should work. – jtahlborn Commented Mar 26 at 19:59
• 1 Hello, thanks for the reply. It looks like there may have been a bug in my implementation of the ImportFilter::filterRow logic. I was returning the original object array passed in, instead of the one that I had created. The logic I've posted below works – lastfreedom Commented Mar 26 at 21:58

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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;
    }