Icon

kn_​example_​r_​sqlite_​multiple_​tables_​40

Create a SQLite table with R (and manipulate data)

Create a SQLite table with R (and manipulate data)

You can do all the stuff you want in R and then put the results into SQLite by either creating new data sets or appending the stuff to existing ones. Gives you a lot of flexibility.

Create a SQLite table with R (and manipulate data)https://forum.knime.com/t/how-to-create-a-table/11251/6?u=mlauber71 # knime.out <- knime.invar_loc_sqlite <- knime.flow.in[["db_sqlite_path_full"]]library("RSQLite")# connect to SQLite tablecon = dbConnect(drv=RSQLite::SQLite(), dbname=c(var_loc_sqlite))# tables <- dbListTables(con)# print(tables)# lDataFrames <- vector("list", length=length(tables))data_out_1 <- knime.in[knime.in$ID <= 12,]data_out_2 <- knime.in[knime.in$ID >= 20,]data_out_3 <- knime.in[knime.in$Value2 == 'A',]# manipulate the 3rd export filedata_out_3$Value3 <- data_out_3$Value1 * 2# keep only IDs that are not NULLdata_out_3 <- subset(data_out_3, !is.na(ID))# export the first tabedbWriteTable(con, "data_export_1", data_out_1 , overwrite = TRUE)# append the second table with same structuredbWriteTable(con, "data_export_1", data_out_2 , row.names=FALSE, overwrite = FALSE,append = TRUE)# export the 3rd tabe with a different structuredbWriteTable(con, "data_export_3", data_out_3 , overwrite = TRUE)dbDisconnect(con)knime.out <- data.frame(var_loc_sqlite, stringsAsFactors=FALSE) locate and create/data/ folderwith absolute pathsdb_sqlite_path_fullcreate db.sqliteread data_export_1read data_export_3data.xlsx Collect LocalMetadata R Snippet Java EditVariable (simple) SQLite Connector DB Query Reader DB Query Reader Excel Reader Create a SQLite table with R (and manipulate data)https://forum.knime.com/t/how-to-create-a-table/11251/6?u=mlauber71 # knime.out <- knime.invar_loc_sqlite <- knime.flow.in[["db_sqlite_path_full"]]library("RSQLite")# connect to SQLite tablecon = dbConnect(drv=RSQLite::SQLite(), dbname=c(var_loc_sqlite))# tables <- dbListTables(con)# print(tables)# lDataFrames <- vector("list", length=length(tables))data_out_1 <- knime.in[knime.in$ID <= 12,]data_out_2 <- knime.in[knime.in$ID >= 20,]data_out_3 <- knime.in[knime.in$Value2 == 'A',]# manipulate the 3rd export filedata_out_3$Value3 <- data_out_3$Value1 * 2# keep only IDs that are not NULLdata_out_3 <- subset(data_out_3, !is.na(ID))# export the first tabedbWriteTable(con, "data_export_1", data_out_1 , overwrite = TRUE)# append the second table with same structuredbWriteTable(con, "data_export_1", data_out_2 , row.names=FALSE, overwrite = FALSE,append = TRUE)# export the 3rd tabe with a different structuredbWriteTable(con, "data_export_3", data_out_3 , overwrite = TRUE)dbDisconnect(con)knime.out <- data.frame(var_loc_sqlite, stringsAsFactors=FALSE) locate and create/data/ folderwith absolute pathsdb_sqlite_path_fullcreate db.sqliteread data_export_1read data_export_3data.xlsx Collect LocalMetadata R Snippet Java EditVariable (simple) SQLite Connector DB Query Reader DB Query Reader Excel Reader

Nodes

Extensions

Links