search for: sheet2

...I'm trying to use RODBC to read data from Excel. However, I'm having trouble converting missing values to NA and rather perplexed by the output. Below illustrates my problem: ## DATA - copy to Excel and save as "tmp.xls" ## tmp.xls!Sheet1 x 0.11 0.11 na na na 0.11 ## tmp.xls!Sheet2 x 0.11 0.11 na na na na 0.11 ## R Code read.xls <- function(file, sheet = "Sheet1", ...) { require(RODBC) channel <- odbcConnectExcel(file) sheet <- sprintf("select * from `%s$`", sheet) x <- sqlQuery(channel, sheet, ...) odbcClose(channel) x } re...

...tion, http://tolstoy.newcastle.edu.au/R/help/06/05/28088.html defining the typeInfo argument... typeInfo <- getSqlTypeInfo("EXCEL") typeInfo$character <- "varchar(3000)" z <- odbcConnectExcel2007("test_rodbc.xlsx",readOnly=FALSE) sqlSave(z,d,tablename="Sheet2",rownames=FALSE,typeInfo=typeInfo) odbcClose(z) Error in sqlSave(z, d, tablename = "Sheet2", rownames = FALSE, typeInfo = typeInfo) : 42000 -1506 [Microsoft][ODBC Excel Driver] Size of field 'v2' is too long. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [Sheet2...

Hi Adrian, dat=data.frame(matrix(0,3,3)) write.xlsx(dat,"z:/dat.xlsx",sheetName="sheet1",append=F) write.xlsx(dat,"z:/dat.xlsx",sheetName="sheet2",append=F) The above code works and creates new worksheets. But if I want to append to an existing worksheet I seem to get an error. write.xlsx(dat,"z:/dat.xlsx",sheetName="sheet2",append=T) - This gives an error saying "The workbook already contains a sheet of...

...ng 10 for 'ReadXls' ---------------- It would be great if anyone can let me know where the code went wrong and any suggestion on how to load multiple sheets into R please?? If 'read.xls' works, I would think by setting 'sheet=c(1,2,3)' might do the job, e.g. reading sheet1, sheet2 and sheet3, assuming sheet1, sheet2 and sheet3 having same data structures, e.g. same number of columns and same name of each columns. As there is no argument telling 'read.xls' how to attach the data together if they are from multiple sheets, e.g. 'by row' or by 'column', I...

...gh each country to generate one output for each country. I want to put the output like this: one sheet has output for one country. How do I achieve it by r. I have tried this: library(xlsx) write.xlsx(nnn, "vn.xlsx", sheetName="Sheet1") [1] but when I change sheetName="Sheet2" to add up another country into one sheet. it autimatically deleted which I have down on [1]. index<-unique(dataset$country) for (i in 1:length(index)){ data<-dataset[dataset$country==index[i],] (...) output<-dd #then how do I create each country's output into one sheet of one e...

...l to the file > chan1 <- odbcConnectExcel("c:/testOdbc.xls") > tables(chan1) and the list appears with the 2 tables > aa -> sqlFetch(chan1,"Sheet1") and aa has the Number, Name and Sex columns correct but Age and FEV1 are all NAs > bb -> sqlfetch(chan1,"Sheet2") and bb is correct! So all numeric columns after a column of characters become NAs Is this an Excel problem or an sql problem.? I did not find anything in the r-help archives relative to this problem. Thanks for any help

...{Microsoft Excel Driver (*.xls)} DriverId=790 MaxBufferSize=2048 PageTimeout=5 > tbls <- sqlTables(con) > tbls TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS 1 c:\\temp\\test <NA> Sheet1$ SYSTEM TABLE <NA> 2 c:\\temp\\test <NA> Sheet2$ SYSTEM TABLE <NA> 3 c:\\temp\\test <NA> Sheet3$ SYSTEM TABLE <NA> Everything seems to be fine. Then I did > qry <- paste("SELECT * FROM", tbls$TABLE_NAME[1], sep = ' ') > qry [1] "SELECT * FROM Sheet1$" > sqlQuery(con...

...message here: # Error in odbcConnectExcel(outFile, readOnly = FALSE) : # odbcConnectExcel is only usable with 32-bit Windows # When this works, the following 3 lines of code # suffice to create the outFile: sqlSave(xlsFile, df1, tablename='sheet1') sqlSave(xlsFile, df2, tablename='sheet2') odbcClose(xlsFile) sessionInfo() #R version 2.15.0 (2012-03-30) #Platform: x86_64-pc-mingw32/x64 (64-bit) #locale: #[1] LC_COLLATE=English_United States.1252 #[2] LC_CTYPE=English_United States.1252 #[3] LC_MONETARY=English_United States.1252 #[4] LC_NUMERIC=C #[5] LC_TIME=English_United St...

..., I believe, uses the Microsoft Excel Driver DSN). Then I try and save a new table to the database(spreadsheet) using SqlSave, but obtain an error message. Below is some test code which reproduces the error (assuming that c:\test.xls is a standard "blank" Excel spreadsheet; i.e. Sheet1, Sheet2, Sheet3) > con = odbcConnectExcel("c:\\test.xls") > df = data.frame(x = rnorm(10), y = rnorm(10), z = letters[1:10]) > sqlSave(con, df) Error in sqlSave(con, df) : [RODBC] ERROR: Could not SQLExecute Where am I going wrong? Thanks, Dave

....names = FALSE): arguments has differents counts of rows: 0, 18" for the following code. Can you please help? Thank you Devarayalu library(ggplot2) setwd("D:\\General Check list") library(RODBC) conn <- odbcConnectExcel ("Book1.xls") Orange1 <- sqlFetch (conn, "Sheet2") odbcClose(conn) rm(conn) unique(Orange1$BSCHSTAT) -> change pdf("xxx.pdf") for (j in 1:length(change)){ ((Orange1$BASCHGAUT == "8-item scale")&(Orange1$BSCHSTAT == change[j])) -> b FD <- Orange1[b, ] unique(FD$REFID) -> refid for (i in refid) { Orange2 &l...

...e, :ignore) if (file_format.upcase == "XLS") parsed_file = Excelx.new(filename,false, :ignore) if (file_format.upcase == "XLSX") raise t "#{filename} is not an Excel file!" if (!parsed_file) parsed_file.default_sheet = parsed_file.sheets[0]#''Sheet2''#oo.sheets[1] first_row = parsed_file.first_row last_row = parsed_file.last_row first_column = parsed_file.first_column last_column = parsed_file.last_column #logger.info "#### Total Rows:#{last_row}, first_row:#{first_row}, last_row:#{last_row}, first_c...

...t 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]]

...cel("D:\\R\\cdeg_SSFA_R.xls") cdeg <- sqlFetch(con, "cdeg") close(con) It works great, but is there a way without giving the sheet name (here "cdeg")? The thing is that I almost always have a single sheet in my xls files, which is not really named (usually sheet1, sheet2, etc), so it would be great if I could just import the 1st sheet into the dataframe. - to export I do this (from Tal Galili's solution earlier today): xlsFile <- odbcConnectExcel("file", readOnly=F) save2excel <- function(x, t.name) sqlSave(xlsFile, x, tablename=t.name, rowna...

...rally 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 indicator...

...import data from access, and make an object (c) store the data, when I want to use it in function mnp, it gives the below error: Error in dimnames(x) <- dn : length of 'dimnames' [2] not equal to array extent > x<-odbcConnectAccess("db2.mdb") > c<-sqlFetch(x,"Sheet2") > odbcClose(x) > c choice clio punto polo 1 3 10.08163 10.05406 10.07323 2 1 10.08163 10.05406 10.07323 3 2 10.08163 10.05406 10.07323 4 1 10.08163 10.05406 10.07323 5 2 10.08163 10.05406 10.07323 .........and 607 more > res1 <-...

...clear or drop sheets from the XLSX files, as per the following example: > library(RODBC) > xlsx<-odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);DBQ=c:\\documents and settings\\desktop\\testxlsx.xlsx; ReadOnly=False") > sqlClear(xlsx,"newsheet2",errors=TRUE) [1] "[RODBC] ERROR: Could not SQLExecDirect" [2] "HY000?Þêÿÿ\003 -5410 [Microsoft][ODBC Excel Driver] Deleting data in a linked table is not supported by this ISAM." > sqlClear(xlsx,"newsheet2",errors=TRUE) [1] "[RODBC] ERROR: Could not SQLE...

R version 2.13.1 OS X (or Windows) Colleagues, I received a number of files with a .xls extension. These files open in XL and, by all appearances, are XL files. However, it appears to me that the files are actually XML: > readLines(dir()[16])[1:10] [1] "<?xml version=\"1.0\"?>" [2] "<Workbook

...': - cells: pick single cell values and give them back as a vector or as a data.frame (the latter is new, type will be determined for each cell individually) - range: read ranges either by name or by a numeric 4-elem-vector (R1,C1,R2,C2) (A1 style, i.e. 'A1:C3', 'Sheet2!B42' could eventually be added here). o xls.sheet, NAMEORINDEX argument renamed - 'nameOrIndex' becomes 'sheet' and the default is the first/active sheet (depends if file is a physical file or an xls-obj) - 'copyAndInsert' action copies from the active sheet o...

Hello! I have no problem reading Excel files (each worksheet in the file is a "table" which can be read - at least in my case). What I would like to do is to read such a table, change it (just the contents, not the format) and write it back, and this I can not do. I am getting the following error messages (3 slightly different attempts): > sqlSave(con, x, tablename =

Dear R users, I've just posted a similar question about Illustrator. This time I would like to export the results of my statistic tables and my dataframes into Excel files. Up to now I've used write.csv(), but I have to resave every file in .xls in Excel. I would like to know if there is a function or package to export directly into *.xls. I have found xlsReadWrite which would be