[BioC] RODBC and odbcConnectExcel

[Wolfgang Raffelsberger]

Hi,

I’m using the package RODBC to read data from MS Excel.

In the (special) case of Excel sheet–names containing a space character 
(eg “Feuil 1”), I’m having trouble to access the data in such sheets.


Similarly, defining a "print area" in Excel (from a sheet without " " in 
it's name) will give an additional line in sqlTables that is of 
table_type "TABLE", but I can't read it neither. A closer look revealed 
that odbcConnectExcel() reads such sheets with " " in the sheet-name as 
“TABLE” (as it does for "print areas") while sheets without (eg 
“Sheet1”) are read as “SYSTEM_TABLE”. Is this the reason why sqlQuery() 
and sqlFetch() give error messages of the type "table not found on 
channel" ?


Of course one solution would be to rename the Excel-sheets. But I’m 
looking for an automated solution to treat data from an instrument that 
generates data in this format (ie MS Excel with sheet names containing “ “).

Does anybody have an idea how I could read/retrieve the information from 
such sheets who’s names contain “ “ ?

Thank’s in advance,

Wolfgang


Here the details :

 > channel1 <- odbcConnectExcel("Classeur1.xls")

 > sqlTables(channel1)

TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS

1 D:\\otherProjects\\TCA\\Sept06\\Classeur1 <NA> Feuil1$ SYSTEM TABLE <NA>

2 D:\\otherProjects\\TCA\\Sept06\\Classeur1 <NA> noSpaceButVeryLong$ 
SYSTEM TABLE <NA>

3 D:\\otherProjects\\TCA\\Sept06\\Classeur1 <NA> 'Feuil 2$' TABLE <NA>

4 D:\\otherProjects\\TCA\\Sept06\\Classeur1 <NA> 'Feuil 3 (2)$' TABLE <NA>

5 D:\\otherProjects\\TCA\\Sept06\\Classeur1 <NA> 
noSpaceButVeryLong$Print_Area TABLE <NA>
>

>  sh1.fetch <- sqlFetch(channel1, "Feuil1") # works OK

>  sh2.fetch <- sqlFetch(channel1, "Feuil 2") # won't work !!
Erreur dans odbcTableExists(channel, sqtable) :
'Feuil 2': table not found on channel
>  sh2.query <- sqlQuery(channel1, "Feuil 2") # won't work either ..

>  sh2.query
[1] "[RODBC] ERROR: Could not SQLExecDirect"
[2] "37000 -3500 [Microsoft][Pilote ODBC Excel] Instruction SQL non 
valide; 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' ou 'UPDATE' attendus."

>
>  sh.prAr <- sqlQuery(channel1, "noSpaceButVeryLong$Print_Area")
>  sh.prAr
[1] "[RODBC] ERROR: Could not SQLExecDirect"
[2] "37000 -3500 [Microsoft][Pilote ODBC Excel] Instruction SQL non 
valide; 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' ou 'UPDATE' attendus."
>

>  odbcCloseAll()

>
>  sessionInfo()
R version 2.4.0 (2006-10-03)
i386-pc-mingw32

locale:
LC_COLLATE=French_France.1252;LC_CTYPE=French_France.1252;LC_MONETARY=French_France.1252;LC_NUMERIC=C;LC_TIME=French_France.1252

attached base packages:
[1] "methods" "stats" "graphics" "grDevices" "utils" "datasets" "tcltk" 
"base"

other attached packages:
RSQLite DBI RODBC svIO R2HTML svMisc svSocket svIDE
"0.4-15" "0.1-12" "1.1-7" "0.9-5" "1.58" "0.9-5" "0.9-5" "0.9-5"





. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
. . . . .

Wolfgang Raffelsberger, PhD
Laboratoire de BioInformatique et Génomique Intégratives
IGBMC
1 rue Laurent Fries, 67404 Illkirch Strasbourg, France
Tel (+33) 388 65 3314 Fax (+33) 388 65 3276
wolfgang.raffelsberger at igbmc.u-strasbg.fr