Merge txt files in R into single Excel

Question

[ab123]

HI guys,

I have been searching for an answer for this online, but cannot seem to find one that suits my needs. That is:

I'm trying to merge around 600 txt files from a microarray experiment into a single Excel (ideally) file using R. Rather than tediously copy and pasting the data of each file into Excel, there must be a faster way through R?

Most of the answers I have found suggest loading in each single file and then merging. At 600 files that's still too complicated.

Is there a way to batch merge these in R Studio?

Many thanks!

[ivivek_ngs]

what are the contents of this 600 text files? do they have probe one column and the intensity on the other? are the probe ids exactly the same number and in the same order in each file? these information's are required before doing any kind of merge.

[ab123]

Hi there, yes they are all the same. Merging them as per the below suggestion didn't work for me, as it doesn't open the files...but then again I'm an R beginner so the code alone below is rather puzzling to me :-( Any suggestions?

[ab123]

Great, will give that a go. Many thanks!

Answer 1

[RossCampbell]

If you put all 600 files (and nothing else) in their own directory, you can use list.files() to pull in all 600 file names into a variable, then read each one individually and append it to a new data frame with either rbind() or cbind(), depending on whether you want to append each new file as rows or columns. For example:

setwd("Your/favorite/directory")
Allfiles = list.files()
for (file in Allfiles){
    #create the new file in the first iteration of the loop by checking if it exists
    if(!exists(df)){df = read.table(file)} #make sure you check if your file has a header
    #now, since we've already created the new file, we can just append to it each time
    if(exists(df)){
         df_temp = read.table(file)
         df = rbind(df, df_temp)
    }
}
#then write your final file
write.table(df,"600Files.txt",sep="\t")

If you need it in Excel format, open the text file in Excel and save as a .XLSX file.

[Charles Plessy]

If you need it in Excel format, open the text file in Excel and save as a .XLSX file.

There is also a package to save a table in XLSX format.

Answer 2

[ivivek_ngs]

This should be able to work. I reckon they are microarray files so each file should have the first column as probe id and second column as the intensity. So you will have to merge all the files by the column of probe id. Then this should do the work

library(reshape)
file_list <- list.files()
list_of_files <- lapply(file_list, read.csv) # Read in each file if its a csv file or `read.table` or `read.delim`
mm<- merge_all(list_of_files, by = "ID") # ID refers to the column with probe ID so you are merging all your files based on the probe id

list.files() is a function which you point out to the folder/directory where all your files are kept, it should be better to put them all in one. Once you are done you can save the mm dataframe in tab-delimited form and open in excel or use a package to save in excel format.