Icon

kn_​example_​r_​ods_​calc_​read

Import ODS (Libre Office) calc Sheets

Import ODS (Libre Office) calc Sheets

use R "readODS" to import a ODS (libre Office) clac sheet. Use other R packages to export the ODS data into ARFF and Parquet files to preserve the date and time formats

Import ODS (Libre Office) calc Sheetshttps://forum.knime.com/t/node-to-read-libreoffice-calc-and-db-files/18791 https://github.com/chainsawriot/readODS/read_ods(path = NULL, sheet = 1, col_names = TRUE,col_types = NULL, na = "", skip = 0, formula_as_formula = FALSE, range = NULL) # Load necessary librarieslibrary(readODS)library(arrow)library(RSQLite)library(foreign)# Read the ODS filefile_location <- knime.flow.in[["v_import_file"]]df <- read_ods(path = file_location, sheet = "cars", col_names = TRUE, col_types = NULL, na = "", skip = 0, formula_as_formula = FALSE, range = NULL)# SQLite Database Connection# Define the location of the SQLite databasevar_loc_sqlite <- paste0(knime.flow.in[["context.workflow.data-path"]], "db.sqlite")con <- dbConnect(drv=RSQLite::SQLite(), dbname=var_loc_sqlite)# Write the dataframe to the SQLite databasedbWriteTable(con, "cars", df, overwrite = TRUE)# Export the dataframe as a Parquet filevar_loc_parquet <- paste0(knime.flow.in[["context.workflow.data-path"]], "cars.parquet")write_parquet(df, var_loc_parquet, compression = "gzip")# Function to add quotes to string elements in a dataframequote_strings <- function(x) { if(is.character(x)) { return(sapply(x, function(y) paste0('"', y, '"'))) } else { return(x) }}# Apply the function to quote string elements in each column of the dataframedf_arff <- data.frame(lapply(df, quote_strings))# Export the modified dataframe as an ARFF filevar_loc_arff <- paste0(knime.flow.in[["context.workflow.data-path"]], "cars.arff")write.arff(df_arff, var_loc_arff, eol = "\n")# Prepare the output for KNIMEknime.out <- as.data.frame(df)# Disconnect from the SQLite databasedbDisconnect(con) locate and create/data/ folderwith absolute pathsget sheet namesif you later want to loopor automate the importof more than one sheetv_import_filejust initiate Rimport ODSARFF and Parquet format saving date formatsdata/cars.tableknime://knime.workflow/data/db.sqlitedefault.carsknime://knime.workflow/data/cars.arff^(dateCrawled|lastSeen)$dateCreateddata/cars_from_sqlite.tabledata/cars_from_arff.tabledata/cars_from_parquet.tablesubstr($$CURRENTCOLUMN$$, 1, length($$CURRENTCOLUMN$$) - 2)cars.parquetpreserves the date and time ciolumnsstring columnsNode 134create RegEx Patternjoin("^(",replace(substr($AggregatedValues$, 1, length($AggregatedValues$) - 2),",","|"), ")$") Collect LocalMetadata R Snippet Java EditVariable (simple) R Source (Table) R Snippet Table Writer SQLite Connector DB Table Selector DB Reader ARFF Reader String to Date&Time String to Date&Time Table Writer Table Writer Table Writer String Manipulation(Multi Column) Parquet Reader Column Filter ExtractColumn Header Create CollectionColumn Table Rowto Variable Collectionto String String Manipulation Import ODS (Libre Office) calc Sheetshttps://forum.knime.com/t/node-to-read-libreoffice-calc-and-db-files/18791 https://github.com/chainsawriot/readODS/read_ods(path = NULL, sheet = 1, col_names = TRUE,col_types = NULL, na = "", skip = 0, formula_as_formula = FALSE, range = NULL) # Load necessary librarieslibrary(readODS)library(arrow)library(RSQLite)library(foreign)# Read the ODS filefile_location <- knime.flow.in[["v_import_file"]]df <- read_ods(path = file_location, sheet = "cars", col_names = TRUE, col_types = NULL, na = "", skip = 0, formula_as_formula = FALSE, range = NULL)# SQLite Database Connection# Define the location of the SQLite databasevar_loc_sqlite <- paste0(knime.flow.in[["context.workflow.data-path"]], "db.sqlite")con <- dbConnect(drv=RSQLite::SQLite(), dbname=var_loc_sqlite)# Write the dataframe to the SQLite databasedbWriteTable(con, "cars", df, overwrite = TRUE)# Export the dataframe as a Parquet filevar_loc_parquet <- paste0(knime.flow.in[["context.workflow.data-path"]], "cars.parquet")write_parquet(df, var_loc_parquet, compression = "gzip")# Function to add quotes to string elements in a dataframequote_strings <- function(x) { if(is.character(x)) { return(sapply(x, function(y) paste0('"', y, '"'))) } else { return(x) }}# Apply the function to quote string elements in each column of the dataframedf_arff <- data.frame(lapply(df, quote_strings))# Export the modified dataframe as an ARFF filevar_loc_arff <- paste0(knime.flow.in[["context.workflow.data-path"]], "cars.arff")write.arff(df_arff, var_loc_arff, eol = "\n")# Prepare the output for KNIMEknime.out <- as.data.frame(df)# Disconnect from the SQLite databasedbDisconnect(con) locate and create/data/ folderwith absolute pathsget sheet namesif you later want to loopor automate the importof more than one sheetv_import_filejust initiate Rimport ODSARFF and Parquet format saving date formatsdata/cars.tableknime://knime.workflow/data/db.sqlitedefault.carsknime://knime.workflow/data/cars.arff^(dateCrawled|lastSeen)$dateCreateddata/cars_from_sqlite.tabledata/cars_from_arff.tabledata/cars_from_parquet.tablesubstr($$CURRENTCOLUMN$$, 1, length($$CURRENTCOLUMN$$) - 2)cars.parquetpreserves the date and time ciolumnsstring columnsNode 134create RegEx Patternjoin("^(",replace(substr($AggregatedValues$, 1, length($AggregatedValues$) - 2),",","|"), ")$") Collect LocalMetadata R Snippet Java EditVariable (simple) R Source (Table) R Snippet Table Writer SQLite Connector DB Table Selector DB Reader ARFF Reader String to Date&Time String to Date&Time Table Writer Table Writer Table Writer String Manipulation(Multi Column) Parquet Reader Column Filter ExtractColumn Header Create CollectionColumn Table Rowto Variable Collectionto String String Manipulation

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-11
On NodePit since: 2019-10-15
Last update: 2024-04-26
Created with KNIME version: v4.7.7
Tags: KNIMEODSOpen DocumentExcelImportcalclibreofficeregexlistcolumnsfiltermanipulateARFF

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

Try NodePit Runner!