Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a large series of subfolders on my Mac with a random amount of csv files in each one. What I'd like to do is merge these files into a single file for each directory.

So far I know I can merge these files with cat * > mergedfile.csv, but I'm having issues iterating through all the folders. I somehow managed to merge all sorts of things so far, but I can't seem to make this do what I want exactly.

Any idea on the best way to do this?

for DIR in ./subfolder/*
    cat $dir/* > merged.csv 
share|improve this question
$DIR and $dir are not the same thing. – choroba Dec 1 '11 at 13:07

With find, you can recursively list all files that match a certain criterion, e.g. the file name.

for file in $(find . -type f -name "*.csv"); do cat "$file" >> /path/to/output.csv; done

Breaking it up, find . -name "*.csv" will find all CSV files from the current folder you're in (.), and the loop will just iterate over that list, appending everything to the output.csv file.

But: File names with spaces, globbing characters, and newlines can be tricky here. A safer solution would be to just use exec for the find command.

find . -name "*.txt" -exec cat '{}' >> /path/to/output.csv ';'

Here, '{}' will be replaced by find with the filename. For a long Q&A about why this is and how to circumvent the problem can be found here.

Now, if you want to create one CSV file for each directory – sorry, didn't see that before –, I'd probably do something like this:

for dir in $(find . -type d); do find $dir -maxdepth 1 -name "*.csv" -exec cat {} >> "$dir/out" ';'; mv "$dir/out" "$dir/merged.csv"; done

Although Franck's solution below is probably more efficient.

Of course, pay attention to the difference between > and >>. The former will always truncate the file to zero-length before writing to it, whereas the latter will just append to the file.

The reason why cat *.csv > merged.csv worked—and why in your loop, it won't work—is that the shell will expand the wildcard before, so basically it sees:

cat file1.csv file2.csv file3.csv > merged.csv

… which will of course not overwrite anything.

share|improve this answer
+1 for the -exec option. – MaQleod Dec 1 '11 at 16:50

Into the parent folder :

for dir in $(find . -type d); do
  cd $dir
  [[ $(ls *.csv|wc -l) -eq 0 ]] 2> /dev/null || { print "$dir.csv created";
                                                  cat *.csv > $dir.csv; }
  cd - > /dev/null
share|improve this answer

Assuming bash 4+ (check with bash --version), you can activate globstar with shopt -s globstar and loop through all directories (and only directories - the trailing / rules out files) recursively with **/

for f in **/; do cat "$f"/*.csv > "$f"/merged.csv; done

If you genuinely want to use all files in a directory, rather than just those ending in .csv, then

for f in **/; do cat "$f"/* > "$f"/merged.csv; done

If you only want to go down a single level, rather than being fully recursive, then use */ rather than **/.

The key mistake in the OP script (aside from forgetting that bash is case-sensitive) is that it attempts to write the contents of all the files into a single .csv file, and does it in such a way as each iteration of the loop would over-write the last.

If you wanted to concatenate all the .csv files recursively into a single file, you could again use globstar

for f in **/*.csv; do cat "$f" > merged_all.csv
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .