[R] Importing multiple worksheets from one Excle/ csv file into R

[R. Michael Weylandt]

I don't use xlsReadWrite, but I've found XLConnect rather handy for
things like this: once you're going, you can just loop over all sheets
like so:

do.call("rbind", lapply(1:50, function(n) readWorksheet(wb, sheet = n,
OtherArgumentsGoHere)))

which will gather them all in a list (from lapply) and then "rbind"
them together. That syntax should help if you use xlsReadWrite, but I
can't help with the import problems.

Michael



On Thu, Mar 15, 2012 at 2:00 PM, HJ YAN <yhj204 at googlemail.com> wrote:
> Dear R experts,
>
> I am trying to import some data from some Excle files into R. My Excle file
> contains about 50 sheets.
>
> One solution I can think about is to convert my Excle file into csv file
> first and then load data into R using 'read.csv'.
>
> But it seems to me that 'read.csv' only supports reading one sheet (or 'one
> file') each time, so that seems I have to create 50 csv files and do 'copy
> and paste' work 50 times which is not ideal!
>
> Alternatively I heard about a package 'xlsReadWrite' and created a 3 sheets
> example (e.g. 3 sheets in one Excle file, saved at 'Z:/WORK_2012/Data/' on
> my PC and is called 'test.xls' ) But my code failed to work.
>
> -----------------
> library(xlsReadWrite)
> data1<-read.xls("Z:/WORK_2012/Data/test.xls")
>
>  Error in .Call("ReadXls", file, colNames, sheet, type, from, rowNames,  :
>  Incorrect number of arguments (11), expecting 10 for 'ReadXls'
> ------------------
>
> By reading the error message I thought the error message trys to tell me
> that I need to set some arguments, so I found all the arguments from
>
> http://127.0.0.1:12275/library/xlsReadWrite/html/read.xls.html
>
> and put them in the following code...
>
> -----------------
> data1<-read.xls("Z:/WORK_2012/Data/test.xls",colNames=TRUE,sheet=1,
> type="data.frame",from=1,rowNames=TRUE,checkNames=TRUE,dateTime="isodate",
> naStrings=NA,stringsAsFactors=TRUE)
>
> Error in .Call("ReadXls", file, colNames, sheet, type, from, rowNames,  :
>  Incorrect number of arguments (11), expecting 10 for 'ReadXls'
> ----------------
> It would be great if anyone can let me know where the code went wrong and
> any suggestion on how to load multiple sheets into R please??
>
> If 'read.xls' works, I would think by setting 'sheet=c(1,2,3)' might do the
> job, e.g. reading sheet1, sheet2 and sheet3, assuming sheet1, sheet2 and
> sheet3 having same data structures, e.g. same number of columns and same
> name of each columns. As there is no argument telling 'read.xls' how to
> attach the data together if they are from multiple sheets, e.g. 'by row' or
> by 'column', I still can not see how to read multiple sheets from one Excle
> file or one csv file and put them into one R data.frame.
>
> Or does anyone ever used any packages in part 8 shown in the following link
> that can help to do the job I mentioned here??
>
> http://cran.r-project.org/doc/manuals/R-data.html#Spreadsheet_002dlike-data
>
>
> Many thanks in advance!
>
> HJ
>
>
>
>
>
>
>
>
>
> I know how to import one single worksheet in one file but would like to
> know how to import data from .csv file containning multiple worksheets.
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.