[R] RODBC : first line of data from query omitted

From: Wolfgang Raffelsberger <wraff_at_titus.u-strasbg.fr>
Date: Wed 03 Jan 2007 - 14:20:35 GMT


Dear List,

when reading MS Excel files in R using package RODBC I encountered the problem of having the first line of data getting omitted.

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() since the sheet I want to extract is called : "Cell measures (1)"

The first line of data is missing in the resulting object (sheet1) in the case where the corresponding sheet contains a list of data without a line serving as header. And the original first line appears as column-name (in the case of strings while any numeric content is transformed to incrementing column-names). 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.

> 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 can read the 1st line of data ?
Is there some argument like the "col.names=FALSE" in read.table() ? Or is there a way to add an additional line unsing the SQL coomand "INSERT" (so that the real data would start in line 2) ?

> 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@igbmc.u-strasbg.fr



R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Thu Jan 04 01:31:58 2007

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Wed 03 Jan 2007 - 15:30:28 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.