RODBC and odbcConnectExcel
1
0
Entering edit mode
@wolfgang-raffelsberger-1805
Last seen 10.3 years ago
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
• 2.7k views
ADD COMMENT
0
Entering edit mode
@james-w-macdonald-5106
Last seen 3 hours ago
United States
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.
ADD COMMENT

Login before adding your answer.

Traffic: 474 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6