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.
- Put all of your .csv files into one folder and open the folder in terminal on the Mac.
- 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.
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?
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.
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
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.
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.
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!
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?
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.
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?
You could create a file for each file name and then combine those files or create a file with the file names and extract and combine select rows. This link may be helpful in combining or extracting select rows also: https://bconnelly.net/working-with-csvs-on-the-command-line/#combining-rows-from-two-or-more-csvs. Not sure exactly how it would work.