0

I am new to R and want to import an Excel file into SQL Server. Each time,read 1 row and insert 1 row using sqlQuery(as I worry about some big excel sheet in the future). Only after 7 rows inserted and the error pops up and keeps coming during later re-runs(increased the Jave memory to 2G but no difference).

Is there somebody know this and please kindly point out the problem with my coding style or synatax? Much appreciate for your reply:) 

 > library(xlsx)
 Loading required package: xlsxjars
 Loading required package: rJava
 > .jinit(parameters="-Xmx2g")
 [1] 0
 > for(i in 1:100 ){
 + Res <- read.xlsx(file="D:/mingdong/R/SECM.xlsx",sheetIndex=1,startRow=i, endRow=i+1,colIndex =1:4,stringsAsFactors=FALSE,encoding="UTF-8")
 + print(paste(Res)) }
 [1] "000000" "1"      "A360"   "1000"  
 [1] "000001" "1"      "A360"   "1000"  
 [1] "000002" "1"      "A360"   "1000"  
 [1] "000003" "1"      "A360"   "1000"  
 [1] "000004" "1"      "A360"   "1000"  
 [1] "000005" "1"      "A360"   "1000"  
 [1] "000006" "1"      "A360"   "1000"  
 [1] "000007" "1"      "A360"   "1000"  
 Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
 java.lang.OutOfMemoryError: Java heap space
 >
trincot
  • 317,000
  • 35
  • 244
  • 286
Bylon
  • 21
  • 1
  • 6
  • Why are you reading only two rows at a time using file access. Why not read the whole spreadsheet at once? – IRTFM Nov 27 '13 at 15:12
  • Sometimes the excel file maybe very large, reading all rows into memory may cause the memory overflow directly, which we must consider it. – Bylon Nov 28 '13 at 01:03
  • Edited it and simplified it as their is perhaps a little long codes – Bylon Nov 28 '13 at 02:23
  • Then why not read in 1000 rows at a time and process? – IRTFM Nov 28 '13 at 07:30
  • yeah, I am considering reading 1000 rows a time, but I am afraid after reading 7*1000 rows it will fail as well – Bylon Nov 28 '13 at 07:42
  • I am considering turning to RODBC.But I dont know how to import 1 or 1000 rows a time in sqlQuery().Could anybody please tell me how to load 1 or 1000 rows a time in RODBC? – Bylon Nov 29 '13 at 06:00
  • It's because of this issue that I gave up on the `xlsx` package and have moved to `readxl`, which is [faster](http://stackoverflow.com/questions/6099243/read-an-excel-file-directly-from-a-r-script/31734198#31734198) – MichaelChirico Feb 25 '16 at 02:20

0 Answers0