Reading Microsoft Access Database

davekalpak · March 23, 2018, 5:39pm

The UCanAccess driver is very unstable in KNIME, in my experience. You can leverage the same lower-level library (jackcess) to implement a custom Access database reader (in a Java Snippet). You will need to add the following (3) external .jar files (that are packaged with UCanAccess) to the Java Snippet node: jackcess*.jar, common*.jar

The Java Snippet takes input table with 1 row and 4 columns; file, table, match_on, match_vals

match_vals is a comma delimited entry
leave match_on and match_vals empty to return the entire table

The output is a list column that can be parsed using native KNIME nodes (i.e. Ungroup)

// system imports
import org.knime.base.node.jsnippet.expression.AbstractJSnippet;
import org.knime.base.node.jsnippet.expression.Abort;
import org.knime.base.node.jsnippet.expression.Cell;
import org.knime.base.node.jsnippet.expression.ColumnException;
import org.knime.base.node.jsnippet.expression.TypeException;
import static org.knime.base.node.jsnippet.expression.Type.*;
import java.util.Date;
import java.util.Calendar;
import org.w3c.dom.Document;


// Your custom imports:
import org.knime.core.node.NodeLogger;
import com.healthmarketscience.jackcess.*;
import com.healthmarketscience.jackcess.impl.*;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Arrays;

// system variables
public class JSnippet extends AbstractJSnippet {
  // Fields for input columns
/** Input column: "file" */
  public String c_file;
/** Input column: "table" */
  public String c_table;
/** Input column: "match_vals" */
  public String c_match_vals;
/** Input column: "match_on" */
  public String c_match_on;

  // Fields for output columns
/** Output column: "records" */
  public String[] out_records;

// Your custom variables:
NodeLogger logger = NodeLogger.getLogger(AbstractJSnippet.class);

// expression start
    public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:

try {

	File db_file = new File(c_file);
	Database db = DatabaseBuilder.open(db_file);	
	Table table = db.getTable(c_table);
	List<String> records = new ArrayList<String>();
	List<String> match_vals = (c_match_vals != null) ? Arrays.asList(c_match_vals.replaceAll(" ","").split(",")) : new ArrayList<String>();
	Integer cnt=0;
	String mv;	
	for(Row row : table){
		mv = null;
		if (c_match_on != null) {

			if (row.get(c_match_on) == null) continue;
			
			if (row.get(c_match_on) instanceof Double) {
				Integer mv_int = (int) Double.parseDouble(row.get(c_match_on).toString());
				mv = mv_int.toString();
			} else {
				mv = row.get(c_match_on).toString();
			}
			if (match_vals.contains(mv)) {			
				records.add(row.toString().split("\\[\\{")[1].replaceAll("\\}\\]",""));
			}
		} else {
			records.add(row.toString().split("\\[\\{")[1].replaceAll("\\}\\]",""));
		}
		cnt = cnt+1;
		//if (cnt>9) break;
	}
	db.close();
	out_records = records.toArray(new String[0]);
	//logger.warn(records);
} catch (IOException e) {
	logger.error("ERROR : "+e.getMessage());
}

// expression end
    }
}