Icon

kn_​example_​r_​openxlsx_​read_​excel_​file

Import Excel/XLSX file with R's openxlsx package

Import Excel/XLSX file with R's openxlsx package

https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_import_meta_workflow~BxiMlBJ2jYCPlZnS


Import Excel/XLSX file with R's openxlsx packagehttps://forum.knime.com/t/excel-reader-node-requested-array-size-exceeds-vm-limit/36908/18?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_import_meta_workflow~BxiMlBJ2jYCPlZnS library(openxlsx)v_data_path <- knime.flow.in[["v_data_path"]]# name and location of excel fileexcelFile <- knime.flow.in[["v_import_file"]]# sheet to importv_sheet_to_load <- "cars"df <- read.xlsx(xlsxFile = excelFile, sheet = v_sheet_to_load, skipEmptyRows = FALSE, detectDates = FALSE)head(df)df$dateCrawled <- convertToDate(df$dateCrawled)df$dateCreated <- convertToDate(df$dateCreated)df$lastSeen <- convertToDate(df$lastSeen)knime.out <- dflibrary(arrow)library(RSQLite)library(foreign)# ----- location of SQLite Database# define some basic variablesvar_loc_sqlite <- paste0(knime.flow.in[["v_data_path"]], "db.sqlite")con = dbConnect(drv=RSQLite::SQLite(), dbname=var_loc_sqlite)dbWriteTable(con, "cars", df, overwrite = TRUE)# export as ARFF filevar_loc_arff <- paste0(knime.flow.in[["v_data_path"]], "cars.arff")write.arff(df, var_loc_arff, eol = "\n")# export file as parquetvar_loc_parquet <- paste0(knime.flow.in[["v_data_path"]], "cars.parquet")write_parquet(df, var_loc_parquet, compression = "gzip")dbDisconnect(con) library(openxlsx)v_data_path <- knime.flow.in[["v_data_path"]]# name and location of excel fileexcelFile <- knime.flow.in[["v_import_file"]]wb = loadWorkbook(xlsxFile = excelFile)# load existing sheet namessheet_names <- names(wb)df_list_sheets <- as.data.frame(sheet_names)knime.out <- df_list_sheets KNIME and R — installation across operating systems — some remarkshttps://medium.com/p/6494a2a498cc 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 XLSX with ARFF and Parquet format saving date formatsv_data_pathdata/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.tablecars.parquetpreserves the date and time ciolumns Collect LocalMetadata R Snippet Java EditVariable (simple) R Source (Table) R Snippet Java EditVariable (simple) 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 Parquet Reader String to Path(Variable) Import Excel/XLSX file with R's openxlsx packagehttps://forum.knime.com/t/excel-reader-node-requested-array-size-exceeds-vm-limit/36908/18?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_import_meta_workflow~BxiMlBJ2jYCPlZnS library(openxlsx)v_data_path <- knime.flow.in[["v_data_path"]]# name and location of excel fileexcelFile <- knime.flow.in[["v_import_file"]]# sheet to importv_sheet_to_load <- "cars"df <- read.xlsx(xlsxFile = excelFile, sheet = v_sheet_to_load, skipEmptyRows = FALSE, detectDates = FALSE)head(df)df$dateCrawled <- convertToDate(df$dateCrawled)df$dateCreated <- convertToDate(df$dateCreated)df$lastSeen <- convertToDate(df$lastSeen)knime.out <- dflibrary(arrow)library(RSQLite)library(foreign)# ----- location of SQLite Database# define some basic variablesvar_loc_sqlite <- paste0(knime.flow.in[["v_data_path"]], "db.sqlite")con = dbConnect(drv=RSQLite::SQLite(), dbname=var_loc_sqlite)dbWriteTable(con, "cars", df, overwrite = TRUE)# export as ARFF filevar_loc_arff <- paste0(knime.flow.in[["v_data_path"]], "cars.arff")write.arff(df, var_loc_arff, eol = "\n")# export file as parquetvar_loc_parquet <- paste0(knime.flow.in[["v_data_path"]], "cars.parquet")write_parquet(df, var_loc_parquet, compression = "gzip")dbDisconnect(con) library(openxlsx)v_data_path <- knime.flow.in[["v_data_path"]]# name and location of excel fileexcelFile <- knime.flow.in[["v_import_file"]]wb = loadWorkbook(xlsxFile = excelFile)# load existing sheet namessheet_names <- names(wb)df_list_sheets <- as.data.frame(sheet_names)knime.out <- df_list_sheets KNIME and R — installation across operating systems — some remarkshttps://medium.com/p/6494a2a498cc 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 XLSX with ARFF and Parquet format saving date formatsv_data_pathdata/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.tablecars.parquetpreserves the date and time ciolumns Collect LocalMetadata R Snippet Java EditVariable (simple) R Source (Table) R Snippet Java EditVariable (simple) 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 Parquet Reader String to Path(Variable)

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: 2021-11-24
Last update: 2024-04-27
Created with KNIME version: v4.7.8
Tags: KNIMEExcelImportrarffopenxlsx

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

Try NodePit Runner!