Dear Alberto,
It is better to assign a name to an area of data and not to use
the name of the sheet, because this can have graphs and other data.
If you this interested can see:
http://tarwi.lamolina.edu.pe/~fmendiburu/Rsolutions.htm
I hope that this also helps,
Felipe.
-----Original Message-----
From: Wolfgang Raffelsberger [mailto:wraff at titus.u-strasbg.fr]
Sent: Wednesday, March 07, 2007 4:50 AM
To: Mendiburu, Felipe (CIP)
Cc: Alberto Monteiro; r-help at stat.math.ethz.ch
Subject: Re: [R] Package RODBC
Dear Alberto,
please note that special characters (eg a space character) in the Excel
sheet names mess up the simple way of querying provided by sqlFetch.
If you have a regular case of all sheets like "Sheet1":
plan1 <- sqlFetch(channel,"Sheet1") # should work
But if you have "Sheet 1" (& similar..) you have to use the
command
sqlQuery(), which means that you have to write a proper SQL query as 2nd
argument that follows proper SQL syntax (starting with "SELECT",
etc...). If I wanted to combine this with sheet-names already read in
variables/vectors I concatenate this into a single stringsimilar to your
2nd code variant ... Of course you could also use grep() to search the
position of a given sheet-name (the order of the sheets may be different
that within Excel).
What you get with
plan1[,1]
depends on what you're reading. In case that the 1st column is read as
string, this is read by default as factor with n levels. You can simply
convert it using as.character() ...
Hope this helps,
Wolfgang
Mendiburu, Felipe (CIP) a ?crit :> Dear Alberto,
>
> channel <- odbcConnectExcel("test.xls")
> name1 <- tables[1, "TABLE_NAME"] # the name1 is Sheet1$
> it must be:
> name1 <- "Sheet1"
> plan1 <- sqlFetch(channel, name1) is ok
> or
> plan1 <- sqlFetch(channel, "Sheet1")
>
> Regards,
>
> Felipe
>
> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch
> [mailto:r-help-bounces at stat.math.ethz.ch]On Behalf Of Alberto Monteiro
> Sent: Tuesday, March 06, 2007 9:37 AM
> To: r-help at stat.math.ethz.ch
> Subject: [R] Package RODBC
>
>
> I have some questions about the RODBC package.
>
> library(RODBC) # required for those who want to repeat these lines
>
> 1st, I noticed that the following sequence does not work:
>
> channel <- odbcConnextExcel("test.xls")
> tables <- sqlTables(channel)
> name1 <- tables[1, "TABLE_NAME"] # this should be the name
> plan1 <- sqlFetch(channel, name1) # bang!
> odbcClose(channel)
>
> However, I can circumvent this with:
>
> channel <- odbcConnextExcel("test.xls")
> tables <- sqlTables(channel)
> name1 <- tables[1, "TABLE_NAME"] # this should be the name
> plan1 <- sqlQuery(channel, sprintf("select * from [%s]",
name1)) # ok
> odbcClose(channel)
>
> 2nd, it seems that only "pure" strings (which are not links to
> strings) and numerical values are correctly fetched or selected.
> Is this a bug?
>
> 3rd, when do something like plan1[,1] a weird message about Levels
> appear. What is that?
>
> Alberto Monteiro
>
>
>
--
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . .
Wolfgang Raffelsberger, PhD
Laboratoire de BioInformatique et G?nomique Int?grative
IGBMC
1 rue Laurent Fries, 67404 Illkirch Strasbourg, France
Tel (+33) 388 65 3300 Fax (+33) 388 65 3276
wolfgang.raffelsberger at igbmc.u-strasbg.fr