White, Charles E WRAIR-Wash DC
2005-Aug-31 17:46 UTC
[R] Reading numeric data in Excel as character data in R using RODBC
The Excel 'database' I am pulling from mixes multiple character indicators for different kinds of non-numeric results in the same column as the numeric values. A simple sqlFetch turns all of the character indicators into NAs. I would like to pull in all of the Excel data as character so that I can split the column into multiple types of vectors and control the type conversion within R. Can anyone provide clues as to how I go about getting RODBC to bring in data containing some numbers without running a numeric conversion? The simple code I use to pull data directly from Excel is generally something like the following: prj<-'C:/data' library(RODBC) channel <- odbcConnectExcel(paste(prj,"/intermediate/datafile.xls",sep='')) r <- sqlTables(channel) ifn.display <- sqlFetch(channel, "SHEET1") il2.display <- sqlFetch(channel, "SHEET2") pl.display <- sqlFetch(channel, "SHEET3") odbcClose(channel) Background: The current problem is small enough to solve with read.csv but I expect to get larger problems in the future. My clients generally give me data in Excel format. Sometimes I get multiple character indicators for different kinds of non-numeric results. Sometimes I get 40+ sheets in a single file where all of the sheets are formatted alike. I'd like to be ready to run a reasonably automated data conversion whenever a client decides to give me both at once. Thanks for your time. Chuck