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