How to choose another separator for csv export?

[geobernadotte]

Hi,
i really tried to read all posts and test around with different configurations, but find no way:

I want to export all image-data into an csv file which i then load into excel for easier data handling.
After half a year i found out, that a comma in my self-defined string-tag will break the import of the csv, cause the person adding the text is a real novel writer and used a lot of special characters.

I tried to change the separator for the csv file to | with the

Code Select Expand

-csv -sep "|" but the resulting textfile is still comma-separated, so i seem to have a basic problem in understanding what its about.

Furthermore i realized, that exiftool adds "" around my tags when some special characters are contained within the string, which i have to delete manually afterwards. Is there a ways to tell exiftool not to do so?

In short, my command looks like this so far (lots more tags handled in full version

Code Select Expand

exiftool.exe -L -T -r -c  -XMP-ivl:Text-de -XMP-ivl:Text -ext jpg -csv -sep "|" DIR > P:\EXIF.txt

Greatful for all hints

[Phil Harvey]

The CSV file format specifies that values should be quoted if they contain certain special characters.  If you choose File/Open in Excel to open a CSV file like this then it should interpret the quotes properly and all your quoting and special character problems will be solved.  It may help to give the file a .csv extension.

I also just tried using Import to read a CSV file into Office 2011 Excel, and it works too if I specify a CSV file type and use a comma delimiter -- it then removes the quotes an preserves commas and other special characters in the values.

- Phil

[geobernadotte]

Hi Phil,

I built kind of a vba monster, importing hole directories of images into excel, people enter additional data, push a button and files are renamed and tags are added with exiftool batch.
Another button sends the images to irfanview commandline for image resize and then generates a word document with all images and their respective image descriptions underneath.
So manually changing things is not really a solution. I'm sure i will be able to delete the quotes with some code for the export, but the bigger problem at the moment is, that my csv file is still comma separated, so reading in text-tags with commas will break the import.

So my question was mainly about what i am doing wrong to change the delimiter.

Thanx a lot
Bernd

[Phil Harvey]

Hi Bernd,

OK.  Since properly importing a CSV doesn't seem to be an option, there are other solutions.

You can use the -p option to format the output any way that you want, with any separators you want to use.  See FAQ number 12 for some examples.

- Phil

[samulij]

A related question: I have a huge pile of assets and I need to split the Caption into two separate fields if a specific marker is found.
If I do this
exiftool -csv -r -Caption-Abstract * > out.csv
I get a list that I can process, and use it to re-import new metadata (the automatic SourceFile field is essential).

My problem is that on some "lines" Caption value is wrapped in quotes (e.g. when the caption is multi-line), and on some lines it is not.

This is how my out.cvs file looks like
folder/13347.jpg,one line caption has no quotes
folder/13442.jpg,"two line
caption forces quotes"

Since I need to get rid of the extra line feeds before processing and importing the csv data... is there a way to force quote wrappers at csv export?

[Phil Harvey]

The only way to force quotes would be with the -p option.

- Phil

[samulij]

Thanks Phil,

I tried -p, and it worked... but I could not include the SourceFile (file path) field I need. SourceFile is essential when I have to process directory trees full of files, and import the processed cvs data.

Is there a way to include the SourceFile field with -p option.

.: Samuli :.

[Phil Harvey]

Hi Samuli,

This can be done with "$directory/$filename".

- Phil

[samulij]

Wunderbar, Thank You Phil!

This works like a charm, now I am able to process the resulting csv file and remove line feeds from caption field.

BTW, would there be a way to filter them out at export? I tried -E, and -b options, but I have the unwanted (mid-record) line feeds in my csv file.

[Phil Harvey]

The problem with -p is that special characters (ie. quotes and linefeeds) can cause formatting problems in the output.  The only way around this with ExifTool is painful, and involves creating user-defined tags to filter the offending characters.

- Phil