[R] problem with RODBC

[Mulholland, Tom]

Well you have not given us anything to go on really. Are there more than 94 columns? Does each column have a valid fieldname? RODBC is not guaranteed to work in every possible scenario. If you have a look through the list you will find there are specific limitations which are not immediately apparent.

I have just done a test with 104 columns with no problem. I then tried the whole width and the driver reported

[1] "[RODBC] ERROR: Could not SQLExecDirect"                             
[2] "S1001 -1040 [Microsoft][ODBC Excel Driver] Too many fields defined."

I shortened it to 26 columns and replaced y1 with a numeric rather than character

No problems at all

removed y1 completely

No probs

I can only assume that there is something in the spreadsheet that is causing problems. Try deleting column A and see if you problem shifts 1 to the left. If it does delete the offending column. If that allows the rest of the data, do things like copying and using the paste special to remove all formatting. I have found that at times the only way to get rid of issues in both Word and Excel has been to delete the offending item and type it in again. Pasting simply doesn't remove it. I've had a case where a space was formatted with special features in word that carried through to excel and stopped every other program that I tried from successfully using the data. 

As for the 1 less this is a database lookup you need to have fieldnames. (That appears to be a Microsoft thing, look at the error message above. RODBC is at the mercies of ODBC)  Have you checked the structure of your data (str(object)) to make sure it does have all the rows? I just did a quick test that suggests that RODBC drops the first line if it does not have valid fieldnames. (I haven't looked, but I bet that Brian's documentation covers this in some way or another.) If you want to read a matrix maybe you should save it as a csv file and import it that way.

Tom



> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch
> [mailto:r-help-bounces at stat.math.ethz.ch]On Behalf Of Dave Evens
> Sent: Wednesday, 20 April 2005 10:20 PM
> To: r-help at stat.math.ethz.ch
> Subject: [R] problem with RODBC
> 
> 
> 
> Dear all,
> 
> I'm reading data via the RODBC connection using
> odbcConnectExcel. I use sqlFetch(channel, "sheetx") to
> identify the correct tab. It appears to read the data
> without any problems. However, when I exact a portion
> of data - the row number specified is 1 less than in
> the actual excel file and it can't read any columns
> after the 94th column. 
> 
> Can someone help me? TIA
> 
> Dave
> 
> ______________________________________________
> R-help at 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
>