Your observation is exactly the issue, the Mac Contacts app strongly prefers vCard and its CSV handling is genuinely limited, so the reliable path either converts to vCard first or uses the CSV import carefully:

The reliable route, convert to vCard first: Mac Contacts imports vCard cleanly, so translating the CSV to vCard before importing avoids the app's weak CSV support entirely. Google Contacts is the free translator, import the CSV at contacts.google.com, then export as vCard and that vCard imports into Mac Contacts via File, Import with proper field mapping, the same translator hop that solves the reverse iCloud case covered elsewhere on this forum.

The direct CSV route, with its limitation named: Mac Contacts can import some CSV files through File, Import, but its field mapping is limited and it handles cleanly structured CSVs better than messy ones, so a simple CSV with clear headers may import directly while a complex one frustrates. Worth one attempt for a clean CSV before reaching for the conversion, but the vCard route is what reliably works when the direct import disappoints.

The preparation that helps either route: clean the CSV first, clear headers, one contact per row, no merged cells or stray formatting and save as UTF-8 to protect accented names, since a well formed CSV both imports more successfully directly and translates more cleanly through Google to vCard.

The verification: after importing, spot check contacts in the Contacts app for names, emails and phones in the right fields and since Mail uses this same address book, the contacts are immediately available in Mail once they are in Contacts, the two sharing the one address book so no separate Mail import exists or is needed.