I have been using a fairly generic command to export all my metadata for analysis in Excel:
exiftool -G -s -r -all -n -GPSLatitude -GPSLongitude -csv -ext jpg -ext mp4 -ext png -ext gif / >Metadata_2022-01-19.csv
Across 100K of files I end up with about 10K of errors on CSV import. It appears that the majority of them seem to be caused by multi-line fields in the CSV file. If I look for example at values in EXIF:ImageDescription, I see that any values that are within a single line (ie don't contain any carriage returns or line feeds) and don't contain comma's are written as a just the text string (ie ,this is a description,). If however the value has carriage returns, or line feeds or commas then the output has quotes around it (ie ,"this is a description",). I have seen this behaviour in multiple different exported CSV fields.
I am finding when importing into Excel 2019 these fields are being properly tagged as text fields, but don't seem to wrap the multi-line items within one cell. The first cell I have in this column that contains quotes is about row 300, so I am not sure if there is something in Excel I have to do to make it handle these text fields as both quoted and unquoted, or alternatively is there a way to force exiftool to add quotes to all text fields or something? Currently I am using exiftool 12.13 if this helps at all.
It might be related to this StackOverflow question (https://stackoverflow.com/questions/2668678/importing-csv-with-line-breaks-in-excel-2007), though that was with excel 2007. Previous exiftool thread (https://exiftool.org/forum/index.php?topic=6863.0).
FAQ #12 (https://exiftool.org/faq.html#Q12) gives an option to create a create a CSV using the -p (-printFormat) option (https://exiftool.org/exiftool_pod.html#p-FMTFILE-or-STR--printFormat).
A round about way would be to load the csv into Libre/Open Office/Google Docs and export from there as xls.
Google Sheets did not work as the CSV file is close to 300MB. I also could not figure out the format file needed with the -p option given that I am generating all fields and these fields can change by adding just one new photo.
You can replace newlines with spaces in tag values using the API filter option:
exiftool -api filter="tr/\n\r/ /" ...
- Phil