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

[R. Michael Weylandt]

The line I gave you will read them in, store them in an object called
a list (which is just a generic holding structure, like a struct in C
or a list in Python) and, once it's got them all in one list, rbind
the whole list together to make one "super"-data.frame. If you want to
keep them separately, you can ditch the rbind bits and just operate on
each "sheet" (now data.frame) independently.

Here's some demo code that will help you get a sense of the syntax:

lapply(list(1:3, 4:6, 7:9), max) # When you give lapply a list, it
will take each list entry individually and do the function to it.

lapply(1:5, cos) # When you give it a vector, it turns each element
into a list -- this is an easy way to iterate over objects and get the
results in a list

do.call("rbind", list(1,2,3)) # do.call lets you pass arguments to a
function in a list rather than the inline/regular way -- useful for
programmatic stuff.

So all together, we use the 2nd sort of lapply to read all the sheets
into a list, then we rbind() that whole list at once (faster than
rbinding after each read)

Hope this helps,

Michael

On Thu, Mar 15, 2012 at 2:52 PM, HJ YAN <yhj204 at googlemail.com> wrote:
> Hi Michael,
>
> I'd just like to say thank you so much again for your help!
>
> So did you mean after I have read all the sheets in R, I can try to use your
> syntax to wrap them into one dataframe? ... still think it might be much
> simpler just using 'rbind' 'cbind' to manipulate data after the data have
> been imported into R .
>
> I might be wrong here and will give it a try anyway...
>
> The issue I mentioned here should be very common for any data analyst so
> expected some easy-to-use R packages have been develped to solve it..
>
> Thanks,
> HJ
>
>
>
>
>
> On Thu, Mar 15, 2012 at 6:29 PM, R. Michael Weylandt
> <michael.weylandt at gmail.com> wrote:
>>
>> 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.
>
>