ExifTool Forum

ExifTool => Newbies => Topic started by: Tr on February 05, 2014, 02:45:43 PM

Title: Request: '|' separator for -csv format
Post by: Tr on February 05, 2014, 02:45:43 PM
I have seen other posts requesting to use a pipe '|' as the csv separator.  I just wanted to second that request. 

The csv output is oh so close to direct ingest into SqlServer.  The BULKINSERT command however will fail on embedded commas ','.  So using a pipe separator would do the trick and avoid a silly transformation of the .csv step after export from exiftool.

Alternatively, any recommendation for a good .csv command line tool for mac ?  This would do the trick for many simple transformations, including the separator.

Thanks
Title: Re: Request: '|' separator for -csv format
Post by: Phil Harvey on February 05, 2014, 07:25:55 PM
You can use the -p option to use any arbitrary format that you want.

- Phil
Title: Re: Request: '|' separator for -csv format
Post by: Tr on February 07, 2014, 10:30:11 AM
Genius!  Did you not think of everything ? 

So, I should be able to create SQL INSERT statements as well, right? 
Title: Re: Request: '|' separator for -csv format
Post by: Phil Harvey on February 07, 2014, 11:44:04 AM
Sure.

- Phil
Title: Re: Request: '|' separator for -csv format
Post by: Tr on February 07, 2014, 05:42:55 PM
Okay, clearly I don't know 'The Perl'

Here is an attempt at escaping single quote strings (') to double single qoutes ('') so that SqlServer is happy:

It does not seem to work, consistently when using -p option.  (*sigh*) 

Feedback welcome:

String Tag Escaped:

Title_Escaped => {
            Require => 'Title',
            PrintConv => '$val =~ s/\'/\'\'/g; sprintf("%s", $val );',
        },

Array of Strings Escaped:

Keywords_Escaped => {
            Require => 'Keywords',
            PrintConv => 'if (ref $val eq "ARRAY") { $val = join(", ", @$val) } $val =~ s/\'/\'\'/g; sprintf("[ %s ]", $val )',
        },
Title: Re: Request: '|' separator for -csv format
Post by: Phil Harvey on February 07, 2014, 08:32:25 PM
It seems that you are trying to create user-defined tags in a config file.  What you are doing is fine, but I'm wondering if the rest of the definition was correct:

Code: [Select]
%Image::ExifTool::UserDefined = (
    'Image::ExifTool::Composite' => {
        Keywords_Escaped => {
            Require => 'Keywords',
            PrintConv => 'if (ref $val eq "ARRAY") { $val = join(", ", @$val) } $val =~ s/\'/\'\'/g; sprintf("[ %s ]", $val )',
        },
        Title_Escaped => {
            Require => 'Title',
            PrintConv => '$val =~ s/\'/\'\'/g; sprintf("%s", $val );',
        },
    },
);

- Phil

P.S.  You can use the advanced formatting feature of the -p option to avoid creating user-defined tags like this.
Title: Re: Request: '|' separator for -csv format
Post by: Tr on February 09, 2014, 11:21:51 PM
Very nice. Went with advanced formatting option here.  So a single line stored procedure will perform the 'UPSERT' for each image record as shown here :

spUpsertRecord '$ID','${DistanceNotes;s/\'/\'\'/g}',......

I think this was the last piece of the mystery puzzle.

Thanks again for the assistance along the way.
Tr