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