News:

2023-03-15 Major improvements to the new Geolocation feature

Main Menu

escaping csv special characters (newlines, delimiter, etc.)

Started by delsey, January 12, 2023, 05:21:29 AM

Previous topic - Next topic

delsey

Hi,

in FAQ #12 it says

But note that any values containing commas, quotes or some other special characters may mess up the CSV formatting. If this is a possibility, the -api filter option may be added to the command to quote values if necessary. For example:
# in Windows
exiftool -api filter="$_ = qq{""$_""} if s/""/""""/g or /(^\s+|\s+$)/ or /[,\n\r]/" ...

I was happy, because this was exactly what I was looking for. However, it doesn't work at all. I think this may be, because the double quotes are not escaped properly, but I don't understand what it does well enough to fix it myself.

I'm using it in the following command:

exiftool.exe "<folder path>" -r -csv -api filter="$_ = qq{""$_""} if s/""/""""/g or /(^\s+|\s+$)/ or /[,\n\r]/" > test.csv

And I'm getting the error

Error: File not found - if
Error: File not found - s//"/g or /(^/s+|/s+$)/ or /[,/n/r]/

I have tried cmd and ps, win10 machine. Would appreciate some assistance.

Phil Harvey

I'm hoping that someone with a Windows machine can help here.  Was the error you posted from with cmd or powershell?

- Phil
...where DIR is the name of a directory/folder containing the images.  On Mac/Linux/PowerShell, use single quotes (') instead of double quotes (") around arguments containing a dollar sign ($).

delsey

Hi, thanks for the reply. The one I posted was from CMD, but since they differ, here are the outputs of both (using the same command from the original post):

Powershell
Error: File not found - if
Error: File not found - s//"/g
Error: File not found - or
Error: File not found - /(^/s+|/s+$)/
Error: File not found - or
Error: File not found - /[,/n/r]/
    1 directories scanned
[...]

CMD
Error: File not found - if
Error: File not found - s//"/g or /(^/s+|/s+$)/ or /[,/n/r]/
    1 directories scanned
[...]

Phil Harvey

I'm pretty sure you need to use single quotes in PowerShell.

- Phil
...where DIR is the name of a directory/folder containing the images.  On Mac/Linux/PowerShell, use single quotes (') instead of double quotes (") around arguments containing a dollar sign ($).

StarGeek

Quote from: Phil Harvey on January 13, 2023, 09:56:58 AMI'm pretty sure you need to use single quotes in PowerShell.

This is correct.

Pay attention to the highlighting in PowerShell.  It tells you how it is grouping parts of the command.  Notice how when you're using double quotes, the $_ is highlighted differently?  That's because with the double quotes, $_ is treated as a shell variable and isn't passed to exiftool.

You may have to fiddle with the command a bit, as I'm not sure how PS is going to interpret multiple double quotes.  In CMD, it would change how the command is interpreted.

* Did you read FAQ #3 and use the command listed there?
* Please use the Code button for exiftool code/output.
 
* Please include your OS, Exiftool version, and type of file you're processing (MP4, JPG, etc).