Re: [R] Problem with RODBC
On Fri, 2006-10-27 at 19:04 +0200, Christian Hager wrote: > No all queries are finished. Then, I don't know. Can you write and send a short code example which reproduces the problem on your side so we can try to diagnose? The simpler the code, the more likely you'll get help. Cheers, Jerome -- Jerome Asselin, M.Sc., Agent de recherche, RHCE CHUM -- Centre de recherche 3875 rue St-Urbain, 3e etage // Montreal QC H2W 1V1 Tel.: 514-890-8000 Poste 15914; Fax: 514-412-7106 __ 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.
Re: [R] Problem with RODBC
On Fri, 2006-10-27 at 14:30 +0200, Christian Hager wrote: > Hello, > > I'am currently experiencing some problems with the odbcCloseAll() and > odbcClose() function. I'm trying to connect an R script to a MySQL 5.0 > database using RODBC1.1-7 and the MySQL ODBC Driver v.3.51 on a > Windows XP Machine. At first everything seems fine. The script > connects, reads and writes data but when it comes to odbcCloseAll() it > crashes and hangs forever. Unfortunately I haven't been able to find > out why yet. So I was hoping someone here could point me to what the > problem might be. Perhaps MySQL is still busy with some query when you try closing the connection. Can you check whether MySQL is still running query? Jerome -- Jerome Asselin, M.Sc., Agent de recherche, RHCE CHUM -- Centre de recherche 3875 rue St-Urbain, 3e etage // Montreal QC H2W 1V1 Tel.: 514-890-8000 Poste 15914; Fax: 514-412-7106 __ 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.
RE: [R] problem with RODBC
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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Dave Evens > Sent: Wednesday, 20 April 2005 10:20 PM > To: r-help@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@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 > __ 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
RE: [R] problem with RODBC
> the row number specified is 1 less than in the actual excel file At a guess I'd say this is because the row 1 in excel is taken as the column heads for the data in R __ 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
