Reading Microsoft Access Database

There is also the R package Hmisc, which may be of interest...

http://svitsrv25.epfl.ch/R-doc/library/Hmisc/html/mdb.get.html

Excellent find, thanks Aaron!

 

I've taken the search a little further from here and have come up with a tool of potentially even greater interest - the "Jackcess" Java library. According to their FAQ it "[...] supports Access database versions 2000-2010 read/write and Access 97 read-only".

 

Unfortunately (for me) it uses Java syntax instead of SQL, but hey - can't have it all. Someone with actual coding skills beyond basic snippet creation interested in setting something up? :-)

 

Cheers

E

JDBC for MS Access - it does exist!

 

http://ucanaccess.sourceforge.net/site.html

 

Was slightly hidden on the MDB Tools Wikipedia page...

 

Cheers

E

 

P.S.: Dependencies - could you envisage bundling these with future KNIME distros? Would make it less fiddly...

UCanAccess requires (at least) the following dependencies in your classpath:

  • jackcess-1.2.9.jar
    • commons-lang-2.4.jar
    • commons-logging-1.0.4.jar
  • hsqldb.jar(2.2.x)

 

P.P.S.: In fact I'd love to see KNIME extensions consolidating open source (and even otherwise distributable) JDBCs - thoughts? I can provide lists... :)

 

P.P.P.S.: *cough* http://rapid-i.com/wiki/index.php?title=JDBC_Drivers *cough*

Hi all,

 

I´m trying write a data in a MS ACCESS table, but when I execute the node I receive this message:

ERROR Database Writer      0:677      Execute failed: UCAExc:::3.0.5 unexpected token: Storecode R

Field Knime Double = ACCESS

Does any one knows how to fix it?

Thank you!

 

 

When I export text data everything works very well, but when I try export value data, example total of sales with the format double precision ,the Knime generated this error : ERROR Database Writer      0:681      Execute failed: UCAExc:::3.0.5 unexpected token: Total of sales

Do you know how can I fix it?

Thank you once again!

William Paiva

 

Hi Everyone,

here https://tech.knime.org/forum/knime-general/no-connection-to-ms-access-database

is a very nice description of the connection.

Best, Iris 

Nice Iris, many thanks!

E

Hi im new in knime, and im having a lot of problems reading an ms access table in knim 3.2 i wanna know if theres a simple route with the database reader node or some other node becouse i dont understand how the database driver works, i was runing in 3.1.7 ucanload and i does work (sorry not english native)

 

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

1 Like