Thanks Bill , that works great.

It occurred to me upon reading the other responses that rather than try and
work around this issue with excel, another option would be to just always
append to the top line of the data sheet (1 line below the header). That
way I would be assured a proper carriage return, and a reverse
chronological order is fine with me

Thanks again to all


On Sat, Apr 20, 2013 at 7:21 AM, William Dunlap <wdunlap@tibco.com> wrote:

> 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")
>     on.exit(close(conn))
>     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)
>             break
>         }
>         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@r-project.org [mailto:r-help-bounces@r-project.org]
> On Behalf
> > Of Jeff Newmiller
> > Sent: Saturday, April 20, 2013 8:14 AM
> > To: Vinny Moriarty; r-help@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@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@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@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@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.
>

	[[alternative HTML version deleted]]

