[R-sig-DB] RODBC: Problem with reading 1st line of data

[Prof Brian Ripley]

This is an issue with the ODBC driver, not RODBC.

On Mon, 8 Jan 2007, Wolfgang Raffelsberger wrote:

>
> Dear List,
>
> when reading MS Excel files in R using package RODBC I encountered the
> problem of not being able to read the first line of data from the
> original Excel sheets :
> This problem arises when the data start directly, i.e. when the original
> sheets don?t contain header-names and the 1^st line starts immediately
> with the (numeric) values I want to read.
> Any text/string component of original first line appears as
> column-name(s) (in the case of strings) while numeric components are
> replaced by F2, F3 etc.
> Then the 2nd line from my input appears then as 1st line of data in the
> resulting R-object and, no surprise, the total number of lines is 1 too
> few ...
>
> I read the data as :
>>  library(RODBC)
>>  channel1 <- odbcConnectExcel("myFile.xls")
>>  sheet1 <- sqlQuery(channel1, "SELECT * FROM [Cell measures (1)$]") # I
> use sqlQuery() instead of sqlFetch() because of the name of the sheet I
> want to extract ("Cell measures (1)")
>
>>  sheet1[1:3,1:5]
> D - 5(fld 10) F2 F3 F4 F5
> 1 D - 5(fld 11) 162 182.110 0.042 184.695
> 2 D - 5(fld 12) 163 198.154 0.086 201.932
> 3 D - 5(fld 13) 164 182.403 0.034 182.816
>
> However, the 1st line in the original reads as :
> D - 5(fld 10) 161 182.929 0.045 188.819
>
> Do you have an idea how to formulate the query that I actually can read
> the 1st line of data ?
> Is there some argument equivalent to the "col.names=FALSE" in
> read.table() ?
> Or is there a way to circumvent the problem by adding an additional line
> using the SQL command "INSERT" (so that the real data would start in
> line 2) ?
>
> Since the original idea consist in generating an automatic protocol for
> processing data available as several Excel sheets from a custom
> application and since these sheets are packed until the last possible
> line (#65536) manually inserting an additional line isn?t a suitable
> option either.
>
>>  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:
> RODBC svIO R2HTML svMisc svSocket svIDE
> "1.1-7" "0.9-5" "1.58" "0.9-5" "0.9-5" "0.9-5"
>
>
> Thank's in advance,
> Wolfgang
>
>
>
>
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
> . . . . .
>
> 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 using igbmc.u-strasbg.fr
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595