Skip to content

Writing a data frame in an excel file

12 messages · Charlie Sharpsteen, Stefan Petersson, Peter Ehlers +4 more

anna
# 
Hello, I am having trouble by using the write.table function to write a data
frame of 4 columns and 7530 rows. I don?t  know if I should just use a
sep="\n" and change the .xls file into a .csv file. Thanks in advance

-----
Anna Lippel
new in R so be careful I should be asking a loooooooot of questions!:teeth:
Charlie Sharpsteen
#
anna_l wrote:
Base R cannot write .xls files by it's self.  You should output CSV using
write.csv():

  write.csv( dataFrame, file = 'results.csv' )

If you are using R on windows, then the RODBC package provides a mechanism
for dumping data frames directly to Excel files, possibly with multiple
sheets:

  require( RODBC )

  xlsFile <- odbcConnectExcel( 'results.xls', readOnly = F )

  sqlSave( xlsFile, dataFrame, tablename = 'R Results', rownames = F )

  odbcCloseAll()


The tablename argument to sqlSave allows you to assign a name to the excel
sheet that will contain the data.frame.


-Charlie

-----
Charlie Sharpsteen
Undergraduate
Environmental Resources Engineering
Humboldt State University
Stefan Petersson
# 
hello,

sep="\n" will seperate each column by \n which is not what you want.

I think a csv would be the best solution.

write.table(yourdataframe,sep=",")

or use write.csv directly.

regards,
 stefan
On Mon, Nov 16, 2009 at 11:49:28AM -0800, anna_l wrote:
Peter Ehlers
#
anna_l wrote:
Excel can read CSV files, so why not just use either
write.csv() or write.table() with sep=","?

  -Peter Ehlers
David Winsemius
#
On Nov 16, 2009, at 3:06 PM, smu wrote:

            

      
      
      
    

        
Excel will also read (and even prefers in some sense) tab delimited  
files, so:

write.table(yourdataframe, file="dataout.xls", sep="\t")

            

      
      
      
    

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      David Winsemius
    

    

      
      #
    

  


  

      
On Nov 16, 2009, at 3:13 PM, David Winsemius wrote:

        

            

      
      
      
    

        
Neither one of those methods will deal with the problem that no  
separator is put in the file on the first row before the colnames. To  
keep the names registered with the columns you would need to set  
row.names=F

write.table(yourdataframe, file="dataout.xls", sep="\t",  
row.names=FALSE)

  
    

      
      
    

  


  


      

    

    
1 day later

    

      
      

        


  

        

      anna
    

    

      
      #
    

  


  

      
Hi Charlie, I?ve been trying to use the sqlSave the way you showed me but it
would give me this error message which I couldn?t solve:
Erro em sqlSave(xlsFile, strategy, tablename = "Result", rownames = FALSE) : 
  table ?Result? already exists

I would like to save the data frame in a specified worksheet but I couldn?t
find in the help on sqlSave how to do it.

        
cls59 wrote:

            

      
      
      
    

        
-----
Anna Lippel
new in R so be careful I should be asking a loooooooot of questions!:teeth:

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      anna
    

    

      
      #
    

  


  

      
Sorry Charlie, I didn?t understand that tablename=R Results was creating a
worksheet. But the thing now is that it works very well when I write for the
first time on the excel file but when I want to rewrite on it it gives the
error i wrote before saying that Results already exists, is there a way to
avoid that?


-----
Anna Lippel
new in R so be careful I should be asking a loooooooot of questions!:teeth:

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Karl Ove Hufthammer
    

    

      
      #
    

  


  

      
On Wed, 18 Nov 2009 08:02:47 -0800 (PST) anna_l <lippelanna21

        
@hotmail.com> wrote:

            

      
      
      
    

        
See the help page for 'sqlSave':
?sqlSave

More specifically, take a look at the 'append' and 'safer' arguments.

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      anna
    

    

      
      #
    

  


  

      
Thanks Karl, well I am getting an error now after the following sqlSave
command:
sqlSave( xlsFile, datas, tablename = 'Datas_and_coefficients', rownames =
FALSE )

-->  [RODBC] Failed exec in Update
22018 39 [Microsoft][Driver ODBC for Excel]invalid character value for the
diffusion specification (null) (null)


More specifically, take a look at the 'append' and 'safer' arguments.

-- 
Karl Ove Hufthammer

______________________________________________
R-help at r-project.org mailing list

PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.




-----
Anna Lippel
new in R so be careful I should be asking a loooooooot of questions!:teeth:

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Orvalho Augusto
    

    

      
      #
    

  


  

      
Why don't try the fabulous WRITEXLS package?

Caveman

        
On Wed, Nov 18, 2009 at 7:45 PM, anna_l <lippelanna21 at hotmail.com> wrote:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      anna
    

    

      
      #
    

  


  

      
Ok I?ve been trying to understand what is happening: the data.frame I am
sending on the xls file has been constructed by the following way: I used
the RODBC package to read dates and prices columns into a dataframe so the
first column in excel is of type "date". In the data.frame it is not numeric
but still double. So when I change the xls files I read by turning these
dates datas into numbers, everything works well. So I don?t know if
something has to be changed within the data.frame containing the dates or if
I have to do everything in numbers and use RDCOM after to change the date
column format of the excel file back into date type.

        
anna_l wrote:

            

      
      
      
    

        

      
      
    

        
-----
Anna Lippel
new in R so be careful I should be asking a loooooooot of questions!:teeth: