How to merge multiple Mac excel files all with headers that are the same but out of order

I am using a Mac. My excel files have headers that match but are out of order. They all start with a header named "query" and then it gets mixed after that.

Here are some header examples:

  • query|email|facebookUrl|twitterUrl|phoneNumber|timestamp|instagramUrl|error|linkedinUrl|youtubeUrl
  • query|facebookUrl|linkedinUrl|phoneNumber|timestamp|youtubeUrl|email|error|instagramUrl|twitterUrl
  • query|email|facebookUrl|instagramUrl|timestamp|linkedinUrl|youtubeUrl|phoneNumber|twitterUrl|error

I can use Terminal to merge all csv files with cat *.csv >combined.csv. But it doesn't work well because all the headers are out of order.

Any ideas what I can do?


You must use a CSV aware tool. A great one is Miller

If you have in example these 3 CSV files

#input_01.csv
a,b,c
0,2,5

#input_02.csv
b,a,c
0,2,5

#input_03.csv
b,a
0,2

you can run mlr --csv unsparsify ./input_0*.csv >./output.csv to have

+---+---+---+
| a | b | c |
+---+---+---+
| 0 | 2 | 5 |
| 2 | 0 | 5 |
| 2 | 0 | - |
+---+---+---+