Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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/*
do
    cat $dir/* > merged.csv 
done
share|improve this question
1  
$DIR and $dir are not the same thing. –  choroba Dec 1 '11 at 13:07
add comment

3 Answers

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
add comment

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
done
share|improve this answer
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.