I am using the RODBC package to read data from an Excel file. An excerpt of the file looks like this: 00103V206 AES Corporation 6.00% 42.87 00808N202 AES Trust III 6.75% 34.98 03748R861 Apartment Investment & Management 9.00% #ERROR (I) 039380209 Arch Coal, Inc. 5.00% 61.51 My problem appears to be related to specification of data types by column. For instance, the CUSIPS in column 1 are read in correctly when they contain a letter, but as NA when they are purely numbers. Similarly, in the fourth column the rows with "#ERROR (I)" are read in as such, but all other values become NA. I have experimented with "as.is" but have not been able to arrive at a solution. I also changed the nullstrings return value and each of the NAs generated above appear to be genrated by the query "seeing" null strings. As always, your assistance is most appreciated. _________________________________________________________________ Don’t just search. Find. Check out the new MSN Search!
I had a similar problem when I posted this recently: RODBC and Excel: Wrong Data Type Assumed on Import http://tolstoy.newcastle.edu.au/~rking/R/help/05/11/14938.html My conclusion was: "Being lucky" shouldn't be part of processing Excel files, which is the case when RODBC is used. This reply gave some suggestions: http://tolstoy.newcastle.edu.au/~rking/R/help/05/11/14990.html I found this suggestion the most useful: "You could try using the COM interface rather than the ODBC interface" http://tolstoy.newcastle.edu.au/~rking/R/help/05/11/15030.html This approach has problems if you have "holes" in your data, but with some work I found RDCOMClient the way to go: http://tolstoy.newcastle.edu.au/~rking/R/help/05/11/15090.html IMHO, RODBC should only be used if you have an Excel file without holes, and with very regular numeric data. I don't understand why the online documentation is not updated to give a usage note that RODBC will often fail reading Excel files. Specifically, this help: library(RODBC) ?odbcConnectExce should be modified to have a warning "RODBC considered harmful with Excel files" efg "Keith Sabol" <sabolk at hotmail.com> wrote in message news:BAY114-F12CBDEDD885260F9BE1AA7D75C0 at phx.gbl...> I am using the RODBC package to read data from an Excel file. > ... > My problem appears to be related to specification of data types by column.