How to merge multiple excel files

Question

I have multiple Excel files, each with two columns. One being the same and the other being different for different files.

How can I merge all the files by their common column and save them as a new file?

Note:

I don't wish to combine them one under the other (using rbind). Instead, I want to merge them based on the commun column.

For R:

I have filenames in the following format.

percent- 15  knt 03_01__00_51.csv
percent- 20  knt 03_01__00_54.csv
percent- 25  knt 03_01__00_57.csv

Due to the file names format I can't write a script to read each file individually. I don't know how to write a loop that iterates over just 15 ,20 .... and leaves the end part somehow.

"don't want to read as data frames" seems like a silly restriction — hrbrmstr, Jan 02 '15 at 19:48
That could be the last option. I am first looking if I can do it through excel. — Abhishek Bhatia, Jan 02 '15 at 19:50
Well, you could read them as a `list` of data.frames, merge them, and write them back to your folder. (If you don't want to use R, you should probably remove the R tag from your question .. ) — talat, Jan 02 '15 at 19:51
After your edit, is it safe to say you could just read all the `.csv` files in a folder? — JasonAizkalns, Jan 02 '15 at 20:06
I am unaware of how one can accomplish that. How do to name the dataframes then. Or do they get stored in a list? — Abhishek Bhatia, Jan 02 '15 at 20:10
You can do `filelist <- list.files(pattern = "^percent.*\\.csv$"); files <- lapply(filelist, read.csv, header=TRUE)` to read them all into a list — talat, Jan 02 '15 at 20:11
Similar to @docendodiscimus comment, check out this [post](http://stackoverflow.com/questions/15134327/using-r-functions-lapply-and-read-sql-csv) — JasonAizkalns, Jan 02 '15 at 20:12
@akrun, they would not need it if those were the _only_ files in that directory. — talat, Jan 02 '15 at 20:14
All those files could be in that folder (which is how I understood your comment) but that doesn't mean that there are no other files in the folder. — talat, Jan 02 '15 at 20:16
@AbhishekBhatia If the pattern is as shown in the edit, something like `list.files(pattern='percent- +\\d+ +knt \\d{2}_\\d{2}__\\d{2}_\\d{2}.csv')` — akrun, Jan 02 '15 at 20:25

score 2 · Accepted Answer · edited May 23 '17 at 12:05

2

You can probably do something like:

# if those are the only files in the folder, you don't even need a pattern
filelist <- list.files(pattern = "^percent.*\\.csv$")    # read all file names according to pattern
files <- lapply(filelist, read.csv, header=TRUE)         # read all files in filelist
files <- lapply(files, function(x) x[-1])                # remove first column of each file
DF = Reduce(function(...) merge(..., by = "CommonColumn", all=T), files)   # merge all files
x <- sub("^(percent- )(\\d+)(\\s.*)$", "\\2", filelist)  # get the file name numbers 
names(DF[-1]) <- paste(names(DF[-1]), x, sep = "-")      # add file name numbers to column names in DF
write.csv(DF, "myfile.csv")                              # write data to new file

The Reduce() part is taken from here.

edited May 23 '17 at 12:05

Community

1
1

answered Jan 02 '15 at 20:37

talat

68,970
21
126
157

Works great. There is one problem though. Each file has three columns- Serial No. , Common Column , Different Colunm. Using the above code the Serial No. also gets copied from each file. – Abhishek Bhatia Jan 02 '15 at 20:57
Is there anyway. If I can select two common columns or select which different column to taken from each file. Or any other way you suggest to resolve the issue. – Abhishek Bhatia Jan 02 '15 at 20:58
Thanks! Yeah I wish to include serial no column and it is the first column. Can you please can make a edit in the original answer. It would better to understand that way. – Abhishek Bhatia Jan 02 '15 at 21:14
Are you saying that you want to use serial no column as a second common column? Your descriptions/comments are not very clear – talat Jan 02 '15 at 21:17
Sorry. I don't want use serial no as a column. So the output would- ..... . Here is the second column and is the third one in each file and the first. – Abhishek Bhatia Jan 02 '15 at 21:19
Thanks! Great. Could it possible to name the different columns by filenames as well? For example, consider the different column name is avgTime, so the output file should contain ... – Abhishek Bhatia Jan 02 '15 at 21:33

How to merge multiple excel files

1 Answers1

Linked