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

Wolfgang Raffelsberger wrote: > 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" ? RODBC isn't a BioC package, so this isn't the correct place for this question. I think you will get much better traction on R-Help. For now, you should note two things. From ?sqlFetch Note: If the table name desired is not a valid SQL name (alphanumeric plus '_'), use 'sqlQuery' with whatever quoting mechanism your DBMS vendor provides (e.g. '[ ]' on Microsoft products and backticks on recent versions of MySQL). From ?sqlQuery Usage: sqlQuery(channel, query, errors = TRUE, ..., rows_at_time = 1) Arguments: channel: connection handle as returned by 'odbcConnect'. query: any valid SQL statement Your call to sqlQuery below (sqlQuery(channel1, "Feuil 2")) cannot possibly work (unless 'Feuil 2' is some sort of new-fangled SQL query ;-D). I think you need to come up with a reasonable query here. Something like myrawdata <- sqlQuery(channel1, "SELECT * FROM [Feuil 2$]") You will then have to do some of the cleanup that sqlFetch() does in order to get a reasonable result. Or you could just extract what you want instead of the whole sheet. But BDR on R-Help is really the one to ask. Best, Jim > > > 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_MONETAR Y=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 > > _______________________________________________ > Bioconductor mailing list > Bioconductor at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/bioconductor > Search the archives: http://news.gmane.org/gmane.science.biology.informatics.conductor -- James W. MacDonald, M.S. Biostatistician Affymetrix and cDNA Microarray Core University of Michigan Cancer Center 1500 E. Medical Center Drive 7410 CCGC Ann Arbor MI 48109 734-647-5623 ********************************************************** Electronic Mail is not secure, may not be read every day, and should not be used for urgent or sensitive issues.