[R] Editing data sheet issue with write.table append

William Dunlap wdunlap at tibco.com
Sat Apr 20 19:21:28 CEST 2013

R is nice because it can deal with other less flexible software and many people
must use Excel at their jobs.

You can write a function that detects if your file ends with a newline and use it
to add a newline exactly when needed.  E.g., the following compares the last
few bytes in the file to the newline pattern that you specify and returns TRUE if
the file ends with a newline.  It reads in chunks so it doesn't waste space with very
large files.  In your situation you can do
   if (!fileEndsWithNewline(outputFile)) cat("\n", append=TRUE, file=outputFile)
before each call to write.table(append=TRUE).

I suppose the check should be modified to check that the file is not empty, as you don't
want a newline before any of your data in that case.

fileEndsWithNewline <- function(file, chunksize=4096, newline=if (.Platform$OS.type=="windows") "\r\n" else "\n")
    rawNewline <- charToRaw(newline)
    stopifnot(chunksize >= length(rawNewline))
    conn <- file(file, "rb")
    prevChunk <- raw()
    repeat {
        thisChunk <- readBin(conn, what="raw", n=chunksize)
        if (length(thisChunk) < chunksize) {
            # concatenate in case newline is spread over two chunks
            lastChunk <- c(prevChunk, thisChunk)
        prevChunk <- thisChunk
    length(lastChunk) >= length(rawNewline) && all(rawNewline == tail(lastChunk, 2))

Bill Dunlap
Spotfire, TIBCO Software
wdunlap tibco.com

> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf
> Of Jeff Newmiller
> Sent: Saturday, April 20, 2013 8:14 AM
> To: Vinny Moriarty; r-help at r-project.org
> Subject: Re: [R] Editing data sheet issue with write.table append
> a) Your terminology "data sheet" is foreign to R (and this mailing list). It implies a
> computing model that R does not use.
> b) You appear to be complaining about the behavior of software other than R on the R-
> help mailing list, which is pointless.
> c) I think the chances of adding code to base R to fix the broken behavior of another
> software package is highly unlikely, particularly when from your description you might or
> might not end up with the missing newline depending on whether you choose to modify
> any particular file outside R. Putting in a newline every time would make R just as broken
> as Excel.
> 4) You can put in newlines before or after your write.table call using the ?cat function.
> Read the data input and output manual for details... but beware that write.table is a
> high-level function that intentionally tries to generate a coherent representation of the
> data table as a single block of text. If you need to tweak within that text then you may
> need to make your own special output function.
> ---------------------------------------------------------------------------
> Jeff Newmiller                        The     .....       .....  Go Live...
> DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
>                                       Live:   OO#.. Dead: OO#..  Playing
> Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
> /Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
> ---------------------------------------------------------------------------
> Sent from my phone. Please excuse my brevity.
> Vinny Moriarty <vwmoriarty at gmail.com> wrote:
> >I'm running R 2.15.2 on Max OS X 10.6.8
> >
> >
> >If I write a simple file
> >
> >Data1<-1
> >DF<-data.frame(Data1)
> >colnames(DF)<-"Col1"
> >
> >and write to a csv file
> >
> >write.table(DF,file="Data.csv",sep=",",row.names=FALSE,col.names=TRUE)
> >
> >I can then append to it no problem
> >
> >Data2<-2
> >Data3<-4
> >
> >
> >DF2<-data.frame(Data2)
> >DF3<-data.frame(Data3)
> >
> >write.table(DF2,file="Data.csv",sep=",",row.names=FALSE,col.names=FALSE,append=T
> RUE)
> >write.table(DF3,file="Data.csv",sep=",",row.names=FALSE,col.names=FALSE,append=T
> RUE)
> >
> >
> >All good so far.
> >
> >But the problem arises if I then need to edit the data sheet and
> >continue
> >to append using the above code. If I open up the data sheet in excel
> >and
> >delete a line, the next time I try and append the data as above the
> >first
> >new line of data starts at the end of the last row of data (though all
> >subsequent new lines are appended properly).
> >
> >I'm sure when I manually delete the line in excel I am removing the
> >invisible carriage return character, causing the first line of new data
> >to
> >be added onto where the carriage return used to be.
> >
> >Is there a way to specify in append=TRUE to always start with a new
> >line of
> >data in the spreadsheet? Failing that, is there a work around to avoid
> >this
> >problem?
> >
> >	[[alternative HTML version deleted]]
> >
> >______________________________________________
> >R-help at r-project.org 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.
> ______________________________________________
> R-help at r-project.org 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.

More information about the R-help mailing list