How to save a data frame in a txt or excel file separated by columns

Question

I have a question about saving data frames in txt or excel format. I have a data frame in R but when I save it:

1. First using write.table() function when I open the saved data frame for example in Excel, all columns are grouped in one column and you have to separe the columns in Excel with a risk to lose data. The same happens with write.csv() function.

Is it posibble to save in R a data frame with all columns separated. Thanks.

Answer 1

My favorite way to achieve this (since I frequently need to share data frames processed in R with non-R users and it is convenient for them to just double click and have it open nicely in Excel) is:

write.table(x,"filename.txt",sep="\t",row.names=FALSE)

As mentioned above the sep="\t" argument tab delimits your file, but I think it is also useful to remove the row names unless needed.

Answer 2

The default delimiter for write.table is a single space. While .txt is often used to mean a spreadsheet, all it really indicates is that it's a text file. If what you want is a .tsv, or a file with tab seperated values, then you can try write.table(x,sep="\t"). This will tab delimit your file, and excel should pick up on that when you try to open the file. I'd really recommend givning ?write.table (and ?read.table while we're at it) a good read. Because really, even if your code is perfect, if you read or write your data improperly, what's the point?

Answer 3

It is possible that the file that you are saving is fine, you are just loading it into Excel incorrectly (hard to know for sure without example). When you import into Excel there are ways to specify what the separator is and other options. You could also simplify the process by setting some options in write.table. Also check your regional settings, the default separator may be different depending on if your computer thinks it is in Europe (or certain countries) or the USA (or certain other countries), it could be that your version of R and your Version of excel don't agree on which region (and therefore which separator to use) you are in.

You may also want to look at the XLConnect package which will create excel files directly without needing to go through a text file. (there are a couple of other packages that will also write to excel files, but XLConnect is the one that I have had the best experience with).

Another option for quickly transferring a data frame or matrix to excel (at least on windows, not sure if this works on other OS's) is to use write table with file="clipboard" and sep="\t", then in the Excel window click on a cell, then do "Paste" and the data will be in excel via the clipboard rather than a file.