search for: sheet1

Could someone help me with the following code snippet. The results are not what I expect: > Sheet1$Claims[1:10] [1] NA 1 2 NA NA NA NA NA NA NA > Sheet1[1:10,"SubmissionStatus"] [1] Declined Bound Bound Bound Bound Bound Declined Dead Declined [10] Not Taken Levels: Bound Dead Declined Not Taken > Sheet1$Claimsnum <- NA > Sheet1$Claimsnum &...

...using R 2.10.0 on a computer running Windows (XP, I think): library(RODBC) channel <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); DBQ=U:\\test folder\\testA.xlsx; ReadOnly=False") sqlTables(channel) my.data.A <- sqlFetch(channel, "Sheet1") odbcClose(channel) channel <- odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); DBQ=U:\\test folder\\testB.xlsx; ReadOnly=False") sqlTables(channel) my.data.B <- sqlFetch(channel, "Sheet1") odbcClose(channel) channel <-...

...ls: case=nochange DBQ=c:\temp\test.xls DefaultDir=c:\temp Driver={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 = '...

...iones ofrecidas no terminan de funcionar. Estoy intentando leer un fichero .xls en ubuntu con los siguientes paquetes y nada: require(RODBC) conn = odbcConnectExcel("madrid.xls") # open a connection to the Excel file sqlTables(conn)$TABLE_NAME # show all sheets df = sqlFetch(conn, "Sheet1") # read a sheet df = sqlQuery(conn, "select * from [Sheet1 $]") # read a sheet (alternative SQL sintax) close(conn) # close the connection to the file require(gdata) xlsfile <- file.path(path.package('gdata'),'xls','madrid.xls') df = read.xls (xlsfile) df...

I have some questions about the RODBC package. library(RODBC) # required for those who want to repeat these lines 1st, I noticed that the following sequence does not work: channel <- odbcConnextExcel("test.xls") tables <- sqlTables(channel) name1 <- tables[1, "TABLE_NAME"] # this should be the name plan1 <- sqlFetch(channel, name1) # bang!

Hi, all, 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,...

...Hi Rui thanks a lot for your reply for my previous query, was of great help to me. I am facing one last query on the following given issue. > > Issue: > Usage of ETS R codes through RExcel macros in VBA > > Given below is my command code: > > Rinterface.runrcodefromrange Range(?Sheet1!B2:D8?) > > Following are the codes written in the given cell reference: > > #!rput zz 'Sheet1'!$B$2:$B$22 > library(forecast) > zz <- ts(zz,freq=365,start=c(2009,1)) > etsz <- ets(zz,model='AAN') > etszP <- forecast(etsz,h=34) > write.table(etsz...

...hero .xls en ubuntu con los siguientes >> paquetes >> y nada: >> >> require(RODBC) >> conn = odbcConnectExcel("madrid.xls") # open a connection to the Excel >> file >> sqlTables(conn)$TABLE_NAME # show all sheets >> df = sqlFetch(conn, "Sheet1") # read a sheet >> df = sqlQuery(conn, "select * from [Sheet1 $]") # read a sheet >> (alternative >> SQL sintax) >> close(conn) # close the connection to the file >> >> require(gdata) >> xlsfile <- file.path(path.package('gdata'),...

...ot as expected. To show you what I mean I created a tiny Excel sheet with some rows and columns and read it in using read.xlsx. I also used my script to write that sheet to a tab-delim txt file and read that one it with read.table and read.delim. Here is the R output: > (test <- read.table(Sheet1.txt, header=TRUE, sep="\t")) Error in scan(file, what, nmax, sep, dec, quote, skip, nlines, na.strings, : line 1 did not have 5 elements > (test <- read.delim(Sheet1.txt, header=TRUE, sep="\t")) c1 c2 c3 X 123 213 NA NA NA 234 asd NA NA NA > (test <- read...

I'm using the RODBC library to read in an Excel file using the odbcConnectExcel function. I can read the Excel data prefectly, however all my character variables get converted to factors when using my.data <- as.data.frame(sqlFetch(channel, "Sheet1")) I've tried using my.data <- as.data.frame(I(sqlFetch(channel, "Sheet1"))) but this creates just one column of data. Is there an efficient way to read in the character variables from my Excel data as characters rather than converting them to factors. Any...

...t; >>>>>> require(RODBC) >>>>>> conn = odbcConnectExcel("madrid.xls") # open a connection to the >>>>>> Excel file >>>>>> sqlTables(conn)$TABLE_NAME # show all sheets >>>>>> df = sqlFetch(conn, "Sheet1") # read a sheet >>>>>> df = sqlQuery(conn, "select * from [Sheet1 $]") # read a sheet >>>>>> (alternative >>>>>> SQL sintax) >>>>>> close(conn) # close the connection to the file >>>>>> >&gt...

...>>>> y nada: >>>> >>>> require(RODBC) >>>> conn = odbcConnectExcel("madrid.xls") # open a connection to the Excel >>>> file >>>> sqlTables(conn)$TABLE_NAME # show all sheets >>>> df = sqlFetch(conn, "Sheet1") # read a sheet >>>> df = sqlQuery(conn, "select * from [Sheet1 $]") # read a sheet >>>> (alternative >>>> SQL sintax) >>>> close(conn) # close the connection to the file >>>> >>>> require(gdata) >>>&gt...

...eek::Book.new(Rails.root.to_s + "/tmp/users/uploaded_files/inventory/inventory.xlsx") material_list = Creek::Book.new(Rails.root.to_s + "/tmp/users/uploaded_files/material_list/material_list.xlsx") inventory = inventory.sheets[0] scl = Spreadsheet::Workbook.new sheet1 = scl.create_worksheet inventory.rows.each do |row| row.inspect sheet1.row(1).push(row) end sheet1.name = "Site Configuration List" scl.write(Rails.root.to_s + "/tmp/users/generated/siteconfigurationlist.xls") end end -- You received this me...

Dear List, when reading MS Excel files in R using package RODBC I encountered the problem of having the first line of data getting omitted. I read the data as : > library(RODBC) > channel1 <- odbcConnectExcel("myFile.xls") > sheet1 <- sqlQuery(channel1, "SELECT * FROM [Cell measures (1)$]") # I use sqlQuery() instead of sqlFetch() since the sheet I want to extract is called : "Cell measures (1)" The first line of data is missing in the resulting object (sheet1) in the case where the correspondi...

Issue: Usage of ETS R codes through RExcel macros in VBA Given below is my command code: Rinterface.runrcodefromrange Range(?Sheet1!B2:D8?) Following are the codes written in the given cell reference: #!rput zz 'Sheet1'!$B$2:$B$22 library(forecast) zz <- ts(zz,freq=365,start=c(2009,1)) etsz <- ets(zz,model='AAN') etszP <- forecast(etsz,h=34) write.table(etszP) How can I import the output table at th...

*Issue:* Usage of ETS R codes through RExcel macros in VBA Given below is my command code: Rinterface.runrcodefromrange Range(“Sheet1!B2:D8”) Following are the codes written in the given cell reference: #!rput zz 'Sheet1'!$B$2:$B$22 library(forecast) zz <- ts(zz,freq=365,start=c(2009,1)) etsz <- ets(zz,model='AAN') etszP <- forecast(etsz,h=34) *write.table(etszP)* How can I import the outp...

...expecting 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...

...many countries. I have written the program to run through 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 c...

Hello everybody, here is the code I use to read an excel file containing two rows, one of date, the other of prices: library(RODBC) z <- odbcConnectExcel("SPX_HistoricalData.xls") datas <- sqlFetch(z,"Sheet1") close(z) It works pretty well but the only thing is that the datas stop at row 7530 and I don?t know why datas is a data frame that contains 7531 rows with the last two ones = NA... ----- Anna Lippel new in R so be careful I should be asking a loooooooot of questions!:teeth: -- View this...

...Excel file with columns Number, Name, Sex, Age, FEV1 on Sheet 1 and Number, Age, FEV1, Name, Sex on Sheet 2. Now I open the channel 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-he...