[BioC] RODBC and odbcConnectExcel

[James W. MacDonald]

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_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
> 
> _______________________________________________
> 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.