Icon

kn_​example_​r_​read_​single_​xlsx_​file

use R library(readxl) to read XLSX/Excel file into KNIME

use R library(readxl) to read XLSX/Excel file into KNIME

Export the data to SQLite, ARFF and Parquet and demonstrate to read the files back into KNIME





use R library(readxl) to read XLSX/Excel file into KNIMEExport the data to SQLite, ARFF and Parquet and demonstrate to read the files back into KNIME https://forum.knime.com/t/how-to-read-excel-file-in-r-snippet/20219/2?u=mlauber71 # read XLSX file and export to KNIME, SQLite, ARFF and Parquetlibrary(arrow)library(RSQLite)library(foreign)# to read excel fileslibrary(readxl)v_excel_file <- knime.flow.in[["v_path_excel_file"]]df <- as.data.frame(read_excel(v_excel_file,col_names = TRUE))# ----- location of SQLite Database# define some basic variablesvar_loc_sqlite <- paste0(knime.flow.in[["var_path_data"]], "db.sqlite")con = dbConnect(drv=RSQLite::SQLite(), dbname=var_loc_sqlite)dbWriteTable(con, "test_file", df, overwrite = TRUE)# export as ARFF filevar_loc_arff <- paste0(knime.flow.in[["var_path_data"]], "test_file.arff")write.arff(df, var_loc_arff, eol = "\n")# export file as parquetvar_loc_parquet <- paste0(knime.flow.in[["var_path_data"]], "test_file.parquet")write_parquet(x=df, sink=var_loc_parquet, compression = "gzip", compression_level = 5)# bring data back to KNIMEknime.out <- dfdbDisconnect(con) # to read excel fileslibrary(readxl)v_excel_file <- knime.flow.in[["v_path_excel_file"]]knime.out <- as.data.frame(read_excel(v_excel_file,col_names = TRUE)) library(arrow)knime.out <-as.data.frame(read_parquet(knime.flow.in[["v_path_parquet_file"]])) KNIME and R — installation across operating systems — some remarkshttps://medium.com/p/6494a2a498cc locate and create/data/ folderwith absolute pathsv_path_excel_filedummy dataread XLSX file(simple read)knime://knime.workflow/data/db.sqlitedefault.test_fileknime://knime.workflow/data/test_file.arffread XLSX file andexport to KNIME, SQLite, ARFFand Parquetread Parquet file(again) to KNIMEv_path_parquet_filewrite excelfilev_path*/data/test_file.tableknime://knime.workflow/data/test_file.parquet Collect LocalMetadata Java EditVariable (simple) Data Generator R Source (Table) SQLite Connector DB Table Selector DB Reader ARFF Reader R Source (Table) R Source (Table) Java EditVariable (simple) Excel Writer String to Path(Variable) Table Writer Parquet Reader use R library(readxl) to read XLSX/Excel file into KNIMEExport the data to SQLite, ARFF and Parquet and demonstrate to read the files back into KNIME https://forum.knime.com/t/how-to-read-excel-file-in-r-snippet/20219/2?u=mlauber71 # read XLSX file and export to KNIME, SQLite, ARFF and Parquetlibrary(arrow)library(RSQLite)library(foreign)# to read excel fileslibrary(readxl)v_excel_file <- knime.flow.in[["v_path_excel_file"]]df <- as.data.frame(read_excel(v_excel_file,col_names = TRUE))# ----- location of SQLite Database# define some basic variablesvar_loc_sqlite <- paste0(knime.flow.in[["var_path_data"]], "db.sqlite")con = dbConnect(drv=RSQLite::SQLite(), dbname=var_loc_sqlite)dbWriteTable(con, "test_file", df, overwrite = TRUE)# export as ARFF filevar_loc_arff <- paste0(knime.flow.in[["var_path_data"]], "test_file.arff")write.arff(df, var_loc_arff, eol = "\n")# export file as parquetvar_loc_parquet <- paste0(knime.flow.in[["var_path_data"]], "test_file.parquet")write_parquet(x=df, sink=var_loc_parquet, compression = "gzip", compression_level = 5)# bring data back to KNIMEknime.out <- dfdbDisconnect(con) # to read excel fileslibrary(readxl)v_excel_file <- knime.flow.in[["v_path_excel_file"]]knime.out <- as.data.frame(read_excel(v_excel_file,col_names = TRUE)) library(arrow)knime.out <-as.data.frame(read_parquet(knime.flow.in[["v_path_parquet_file"]])) KNIME and R — installation across operating systems — some remarkshttps://medium.com/p/6494a2a498cc locate and create/data/ folderwith absolute pathsv_path_excel_filedummy dataread XLSX file(simple read)knime://knime.workflow/data/db.sqlitedefault.test_fileknime://knime.workflow/data/test_file.arffread XLSX file andexport to KNIME, SQLite, ARFFand Parquetread Parquet file(again) to KNIMEv_path_parquet_filewrite excelfilev_path*/data/test_file.tableknime://knime.workflow/data/test_file.parquetCollect LocalMetadata Java EditVariable (simple) Data Generator R Source (Table) SQLite Connector DB Table Selector DB Reader ARFF Reader R Source (Table) R Source (Table) Java EditVariable (simple) Excel Writer String to Path(Variable) Table Writer Parquet 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: 2019-12-17
On NodePit since: 2019-12-28
Last update: 2024-04-18
Created with KNIME version: v4.7.8
Tags: parquetarrowimportknimereadgziprlibrarycsvreadr

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

Try NodePit Runner!