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

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2018-06-10
On NodePit since: 2019-12-21
Last update: 2024-11-21
Created with KNIME version: v4.7.8
Tags: sqliterdatabase

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!