Merge multiple Mac excel files all with headers that are the same but out of order

csvmacosmicrosoft excel

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?

Best Answer

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 | - |
+---+---+---+

Related Solutions

Excel – How to prevent VLOOKUP from breaking after I add a column to a named range

Personally, I prefer using an INDEX-MATCH combo for lookups instead of VLOOKUP. Here's an example off the Pokedex I was compiling for my niece.

enter image description here

To get Squirtle's Hidden Ability, I'd use this formula:

=INDEX(F2:F11,MATCH("Squirtle", A2:A11,0),1)

This yields the same result as:

=INDEX(A1:G11,MATCH("Squirtle",A1:A11,0),MATCH("Hidden Ability",A1:G1,0))

One good thing about INDEX-MATCH is that in most cases, you don't need to reference the entire data range, so the first formula should work even if you regularly add columns and rows to your data range. It also has another advantage: since you're referencing only 2 one-dimensional ranges, it calculates faster.

Nevertheless, you can still use MATCH with your VLOOKUP formulas to get the column number.

=VLOOKUP(C1,NAMED_RANGE,MATCH("COLUMN_TEXT",$A$1:$H1,0),0)

where:
$A$1:$H$1 is the first row in your data/named range, or the row that contains header text
NAMED_RANGE is the name for your data range
"COLUMN_TEXT" is the header text for the column that contains the data you need
C1 contains your lookup value

Edit: Added Index-Match example as per Doug's request. :D

Bulk fix for Excel 97-2003 error “format does not match extension”

The error "format does not match extension" means that the files are not truly .xls. As Excel can still open them, they are probably of another format that is supported. My guess would be .xlsx.

You may test by using a hex-editor (such as HxD) to check the file signature, which is in the first few bytes:

xlsx: First 4 bytes are 50 4B 03 04
xls: First 8 bytes are D0 CF 11 E0 A1 B1 1A E1

To rename all the files in the current folder at once, you could use the Command Prompt (CMD) with the command:

ren *.xls *.xlsx

If your file-signature is not one of the above, please add it to your post. It will help identify these files.

As the files were HTML, not xsl or even xslx, it's possible to do mass-conversion in Excel using VBA.

The article How to convert multiple xls files to xlsx files in Excel? contains this script that worked for the poster:

Sub ConvertToXlsx()
'Updateby Extendoffice
Dim strPath As String
Dim strFile As String
Dim xWbk As Workbook
Dim xSFD, xRFD As FileDialog
Dim xSPath As String
Dim xRPath As String
Set xSFD = Application.FileDialog(msoFileDialogFolderPicker)
With xSFD
.Title = "Please select the folder contains the xls files:"
.InitialFileName = "C:\"
End With
If xSFD.Show <> -1 Then Exit Sub
xSPath = xSFD.SelectedItems.Item(1)
Set xRFD = Application.FileDialog(msoFileDialogFolderPicker)
With xRFD
.Title = "Please select a folder for outputting the new files:"
.InitialFileName = "C:\"
End With
If xRFD.Show <> -1 Then Exit Sub
xRPath = xRFD.SelectedItems.Item(1) & "\"
strPath = xSPath & "\"
strFile = Dir(strPath & "*.xls")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do While strFile <> ""
If Right(strFile, 3) = "xls" Then
Set xWbk = Workbooks.Open(Filename:=strPath & strFile)
xWbk.SaveAs Filename:=xRPath & strFile & "x", _
FileFormat:=xlOpenXMLWorkbook
xWbk.Close SaveChanges:=False
End If
strFile = Dir
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Best Answer

Related Solutions

Excel – How to prevent VLOOKUP from breaking after I add a column to a named range

Bulk fix for Excel 97-2003 error “format does not match extension”

Related Question