HI everyone, I am trying to read an excel with around 100 Columns and 400K Rows (file size around 160MB), however, the excel reader dont allow me to proceed, and say “Requested array size exceeds VM limit”. In my KNIME.ini, i have make change of below. -Xmx12g (revised to 12g, while my computer has 16g) -Dknime.database.fetchsize=1000 (Newly added) If there is no direct way to deal with that, do we have some play around trick please? All i want is to read this file (5 more files at similar sizes), filter by a string at certain column, extract those and append to form a new file. Thanks in advance

Hi @Fovas , you can try to read the file in batches. The Excel Reader allows you to read between a range of rows: [image] So, you could read let’s say 40k rows at a time, and do your process and write to your new file, and then repeat the process and append to the file. You can use a Loop to do this. You can check this thread that I replied to earlier that can help you with how to read a file partially implement the loop: [image] The file is too big to be read by knime, is there any solution? Or measures KNIME Analytics Platform One thing I’ve noticed though is that there does not seem a way to append to an Excel sheet. You can write to the same Excel file, but in a new sheet. You can overwrite a sheet, but you cannot append to it. I put something together quickly, but I’m writing to different sheets at the moment. Workflow looks like this: [image] Since we don’t know how many times we need to loop, I start with a Generic Loop Start, but I know I should stop once we have reached the end of the file, and we know tha… EDIT: Just to add, so you would dynamically calculate the range for the rows within the loop, and assign the range as flow variables in the Excel Reader to these 2: [image]

Thanks, i am trying your suggested method, and now i am wondering there is another problem. When I lowered the row count to 5000, it still return the same error msg and dont let me to execute.(and i tried with 500 same issue, that isnt normal…) I don’t have the VM limit problem with other excel that is at smaller size tho (i.e around 30MB, 50 columns and 10k Rows) [VM size prob]

Hi @Fovas , that was a good test. There’s definitely an issue… Not sure what is causing this… Can you try to read just 1 column to see what’s the behaviour? May be also reduce the number of rows to scanned: [image]

HI, reduced the row to only scan 5000 rows at the advanced setting tab, plus limit the Col and Row limit per below… still fail… lol i am guessing we missed something here…probably not the VM issue anymore [VM size prob 2]

@Fovas you could try and use R ( 1 | 2 ) to import the file Excel file and see how that does work out [image] use R library(readxl) to read XLSX/Excel file into KNIME – mlauber71 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

Definitely something weird going on there @Fovas

Thanks Mlauber71 again, i am trying out with R, i guess i get it installed correctly, but then how do make the R source table node to “read” the excel pls?

@Fovas you could check out the example and adapt it to your needs [image]

Sorry for my stupid question here, i get to the point that with below in the R source (Table) node. to read excel files library(readxl) v_excel_file ← knime.flow.in[[“PSR_Location”]] v_excel_sheet ← knime.flow.in[[“PSR_SheetName”]] knime.out ← as.data.frame(read_excel(v_excel_file,sheet = v_excel_sheet, col_types = “text”, col_names = TRUE, .name_repair = ~make.unique(.x, sep = “_”))) However, when i try to execute it, it return the error msg below. And i cant connect a regular Excel Reader node with the R Source (table) node… ERROR R Source (Table) 4:84 Execute failed: Error in R code: Error: there is no package called ‘readxl’ Error: could not find function “read_excel”

[image] Fovas: there is no package called ‘readxl’ You would have to install the package . Best thing would be to install RStudio as well. Then you can easily manage your R installation and you will have the packages for KNIME. Typically this command should do: install.packages(“readxl”)

KNIME Forum Archive

[Excel reader node] Requested array size exceeds VM limit

KNIME Analytics Platform

mlauber71 October 23, 2021, 3:32am 6

@Fovas you could try and use R (1|2) to import the file Excel file and see how that does work out

"invalid code lengths set" on Excel Reader

How to fix "ZIP entry size is too large or invalid" in Excel Reader