Multiline fields in CSV causing issues on Excel import

[KiwiEater]

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.

[StarGeek]

It might be related to this StackOverflow question, though that was with excel 2007. Previous exiftool thread.

FAQ #12 gives an option to create a create a CSV using the -p (-printFormat) option.

A round about way would be to load the csv into Libre/Open Office/Google Docs and export from there as xls.

[KiwiEater]

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.

[Phil Harvey]

You can replace newlines with spaces in tag values using the API filter option:

exiftool -api filter="tr/\n\r/  /" ...

- Phil