[R] Package RODBC

[Wolfgang Raffelsberger]

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