search for: odbcconnectexcel2007

Hi all, Apologies if I violate any posting etiquette - this is my first submission to the R mailing list. I regularly use 'odbcConnectExcel2007' (from package 'RODBC') to read data from named ranges in excel workbooks into R. I recently received the following error message when attempting to set up a connection channel (i.e. ch1 <- odbcConnectExcel2007('path to excel 2007 file.xlsx')): "External table is not in...

Hi sorry if this is really basic but I am just starting on R. Can anyone point me at how to write R objects into Excel 2007 files. I have seen how to set up a connection to a file through odbcConnectExcel2007(xls.file, readOnly = FALSE, ...) but it doesn't say anything on how to write data Thanks _____________________________________________________________________ The information contained in or attached to this email is intended only for the use of the individual or entity to which it is...

I tried creating a .xlsx file using odbcConnectExcel2007 and adding a worksheet with sqlSave. This seems to work, I am even able to query the worksheet, but when I try opening the file in Excel I get the following message: "Excel cannot open the file 'test.xlx' because the file format or file extension is not valid. Verify that the file...

I have probably an old question. I have R.3.0.1 installed in 64 bit windows 7. The odbcConnectExcel in RODBC library does not work. Tried odbcConnectExcel2007 still does not work. Any ideas. Thanks Melissa<-sqlFetch(odbcConnectExcel2007("F:\\Cotton2012\\validation.xlsx"),sqtable = "Sheet3", + na.strings = "NA", as.is = TRUE) Error in sqlFetch(odbcConnectExcel2007("F:\\Cotton2012\\validation.xl...

...s a little slow compared to RODBC. Since I will be processing hundreds to thousands of files, I would like a faster option. RODBC seems to be it, but I am having trouble telling it not to take the first row of "data" as a header/column name. Here is my present command rawspectrum <- odbcConnectExcel2007("directory path/filename.xlsx", readOnly = TRUE) editspectrum <- sqlFetch(rawspectrum,"Sheet1", as.is=TRUE, colnames = FALSE, rownames = FALSE, nullstring="") odbcClose(rawspectrum) If I ran this on the above fake data I would get something like: Subject ,...

...t would be better to set up a SQL database, but this is not an option at this point.). Typically we are not working on the same sheets. However, if possible I like to read the most current excel file into R. RODBC works great as long as the other user has not open the file. I tried: channel <- odbcConnectExcel2007("Biosample.xlsx") channel <- odbcConnectExcel2007("Biosample.xlsx",readOnly = TRUE) but I do get a windows error message that the file is locked by another user. Is there a way to open an excel file with RODBC in "read only" mode? I am using R2.15 (32bit) on a Mi...

...ot;) Instalo la librería > library(RODBC) Todo bien hasta aqui... Se supone, que en esa librería hay unas funciones que me permiten conectar con Excel, esas funciones son: o -odncConnectExcel2007 o -odncConnectExcel DE tal manera que ahora podría abrir una canal > canalexcel <- odbcConnectExcel2007("lbw.xlsx") Pues esas funciones no están. R me da el error Error: no se pudo encontrar la función "odbcConnectExcel2007" lo mismo con la otra función > canalexcel <- odbcConnectExcel("lbw.xlsx") Y lo curioso es que también me pasa algo similar con la otra po...

...st one field with character elements that exceed 255 bytes, which appears to be the cell width limit in Excel. Below is example code and the warning message received: library(RODBC) d <- data.frame(v1=c(1,2),v2=c(paste(rep("test",100),collapse=""),"test")) z <- odbcConnectExcel2007("test_rodbc.xlsx",readOnly=FALSE) sqlSave(z,d,tablename="Sheet1",rownames=FALSE) odbcClose(z) Warning message: In odbcUpdate(channel, query, mydata, coldata[m, ], test = test, : character data 'testtesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttestt...

...many numbers. But on line 3000 and some other lines are strings like "FG 1". "RODBS" seems to omit this lines. "gdata" works, but is much slower. Is this a bug of RODBC or do I apply it wrong? Example with the same "file.xlsx" library(RODBC); excel <- odbcConnectExcel2007("file.xlsx") tab <- sqlQuery(excel, 'select * from "Table 1$"'); str(tab) col1: num 1 2 3 4 5 6 7 8 9 10 ... library(gdata); tab <- read.xls("file.xlsx", sheet=1); str(tab) col1: Factor w/ 51 levels "1","10","11",..: 1 12...

I am reading dates in Excel2007 into R. Here are the functions I used: library(RODBC) channel<-odbcConnectExcel2007("myfile.xlsx") tmp<-sqlFetch(channel,"1",as.is=T) The dates in myfile.xlsx are all in this format: mm/dd/yyyy. But when I read it to R, some columns look like "yyyy-mm-dd 00:00:00", some columns look like "yyyy-mm-dd", and some columns are numbers. I do...

...just to know if the issue is only a problem of mine or if it is a general issue due to the new MS Office pack. I'm using R 2.11.1 32 bits in a Windows 7 x64 with the MS office 2010 x64 installed. I can import .xls files normally (the same way I did with my Excel 2007 32 bits). But the function odbcConnectExcel2007 isn't able to import .xlsx files now that I have the new version of the Office package. It gives me the following warning message, which make impossible the importing process through sqlFetch: Warning messages: 1: In odbcDriverConnect(con, tabQuote = c("[", "]"), ...) : [...

...e where site = x and write it into an object named 'sitex_data'. Somehow I'm really missing something as I'm not able to create these sitex_data objects with the database values, neither using list nor assign... Here some code snipplets: >library (RODBC) >channel <- odbcConnectExcel2007 ("biomass_data.xlsx") >site_data <- sqlQuery(channel, "select site_no from [biomass_data $] group by site_no") #Here the values I want to loop through >str(site_data) 'data.frame': 44 obs. of 1 variable: $ site_no: num 4 7 9 10 15 16 17 18 19 20 ......

Hi all, I have imported xlsx file (Excel 2007) into R using the following scripts. *library(RODBC) * *setwd(".......") * *query <- odbcConnectExcel2007(xls.file = "GI 2010.xlsx", readOnly = TRUE) dat <- sqlQuery(query, "select * from [sheet1$]", as.is = TRUE, na.strings = "exp")* *dat* contain one column consisting of intergers and characters (unique value which is "exp"). However, R recognises the cl...

Dear all, I have been looking for possibilities to read and write Excel 2007-files in and from R. The 'reading' part is ok through odbcConnectExcel2007 and sqlFetch(RODBC). For 'writing' I thought of using sqlSave in the same package, but it does not work (I think because this function starts from an existing file). Is there a workaround for this, or are there other plausible options for writing .xlsx files? With kind regards, Katrie...

...already read the R Import/Export guide, and I know how to use gdata to import xls files and read.table to import .csv. My problem is that all data that I receive is in the xlsx format, and I have to convert all the files to xls. Well, when I was using Windows Vista OS, RODBC did the trick with the odbcConnectExcel2007 function (which I know is not present in the Linux RODBC package, probably due to drivers issue). Isn't there a way to import this xlsx files directly to R without any previous conversion (.csv or .xls)? Thank you for the attention, it's probable that some one already asked it. I even reme...

...i've tried to run a loop using sqlTables but it seemed to break the connection. unless i've missed something, i cant see a solution to this on the help list. grateful for any help or pointers simeon # long way library(RODBC) filepath <- "C:/Data/workbook.xlsx" connect <- odbcConnectExcel2007(filepath) tbls <- sqlTables(connect) sheet1 <-sqlFetch(channel=connect,sqtable='sheet1') sheet2 <-sqlFetch(channel=connect,sqtable='sheet2') sheet3 <-sqlFetch(channel=connect,sqtable='sheet3') .. etc close(connect) [[alternative HTML version deleted]]

...2.14.0, under Windows 7 and I tried following: library(RODBC) > channel <- odbcConnectExcel("results.xlsx") Error in odbcConnectExcel("results.xlsx") : odbcConnectExcel is only usable with 32-bit Windows # ok this is clear why it doesn't work > channel <- odbcConnectExcel2007("results.xlsx") # this was one of proposals from old R help posts, but it doesn't work for me Warning messages: 1: In odbcDriverConnect(con, tabQuote = c("[", "]"), ...) : [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Da...

Hello, I am trying to read an Excel file using RODBC connect (its attached) using the following code: Data<-odbcConnectExcel2007('acciones_col.xlsx',readOnly=T,) datos<-sqlFetch(Data,'Bloomberg') odbcClose(Data) The file contains stock data downloaded from bloomberg, and it contains five variables per stock (so every five columns there is an empty column) . As the bloomberg output comes, in the first...

Hi Every one, I have a problem with Reading Excel file into R 2.9.0 version. In older versions it is working with "xlsReadWrite" package. But in 2.9.0 version there is no package like that. so help me out in this aspect. Thanks in Advance. -- View this message in context: http://www.nabble.com/How-to-Import-Excel-file-into-R-2.9.0-version-tp24914638p24914638.html Sent from the R help

Hi All, I'm using R 2.11.1 on 64 bit windows XP. The little function I wrote below I use often to import the first 1001 lines in an excel sheet to R. This works fine on the 32 bit version of R but fails on the 64 bit [both on the same machine, using the same function, importing the same .xls file]. The message from 64 bit R is: Error in sqlTables(channel1) : first argument is not an