How to merge multiple CSV-files into one with Mac OS X Terminal.app

mac terminal icon

Found this csv merge tutorial and it works great to merge csv files on mac into one file.

An explanation and quick piece of code that will help you to simply merge large numbers of .csv files (or for that matter: any kind of text files) into one. I used it to merge multiple .csv files with differing filenames into one. Quick & easy.

  1. Put all of your .csv files into one folder and open the folder in terminal on the Mac.
  2. Run this command in terminal on the folder:
cat *.csv >merged.csv

 



To specify which files you want to merge use:

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

Note that each file must have the same number of columns.

More things you can do with Terminal on the Mac

10 Comments

  1. Peter on April 19, 2017 at 7:23 am

    This is really useful and thanks for sharing. I did some files in this way, finding the merged always has a repeated headline. Do you have any way to remove that?

    • Mr. Dif on April 19, 2017 at 9:49 am

      Thanks Peter. To test this I took a .csv with a header row and duplicated it 4 times in a folder and then ran the command via terminal in that folder. I see only one header for the file but if I change the header even slightly in one of the files then I get the duplicate headers in the merged file. So long story short, I wonder if not all of your csv files have the same header row. In a text editor like Panic’s Coda or Sublime you could do a find and replace with some wildcards to remove duplicated headers from your merged.csv. Hope that helps you.

  2. Charlotte on April 27, 2017 at 8:33 am

    Hello, I want to combine 185 files of 220 bytes and when I use the command “cat *.csv > outputfile.csv”, it creates a file of more than 130 Gb! Impossible to open and to process.. Any idea why?? Thanks, Charlotte

    • Mr. Dif on April 27, 2017 at 9:52 pm

      Wow, that is some command line power right there. My first thought is wondering if there’s something else in the directory. Maybe try on a smaller scale first to see if that helps matters. There’s also more ways to combine csv files that might be helpful. This is a good resource: https://bconnelly.net/working-with-csvs-on-the-command-line/#some-useful-resources Good luck! Sorry I couldn’t be more helpful to you.

    • Manuel on July 12, 2017 at 3:38 pm

      Just ran into the same problem. The destination file is not allowed to exist (in this folder). If it does (which seems to have happened to you and me), it keeps to be rewritten in itself until you stop the command or your disk runs full. So just use a file outside the folder or use a filename which not exists yet.

  3. Arta on October 4, 2017 at 1:53 pm

    That was a killer!
    That was damn simple. Had to lookup on google for an hour!

    This trick merged 1970 csv files in a matter of a second!

  4. Kirsten on January 18, 2018 at 8:27 am

    this was super helpful. Any chance it is possible to annotate the data that is being merged with the filename from where it came? Ideally I’d love to add an entry at the start of ever line that includes the csv filename, so in the end I have 1 csv and I know from which original file ever line came from. Possible?

    • Mr. Dif on January 18, 2018 at 9:33 am

      Glad it helped you get your files merged. The work around answer to your question would be to add a row to each of your files with the name of the file. Then when merged, you’d have the entry for each.

      • Keenan on May 8, 2018 at 8:31 am

        While this would work, it becomes impractical when trying to merge hundreds of csv files. Is there any other way to include the title of the original file in the merged one?

Leave a Comment