R help - Mar 2012 - Importing multiple worksheets from one Excle/ csv file into R

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]]

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.

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.
>
>