Request: '|' separator for -csv format

Started by Tr, February 05, 2014, 02:45:43 PM

Previous topic - Next topic

Tr

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

Phil Harvey

You can use the -p option to use any arbitrary format that you want.

- 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 ($).

Tr

Genius!  Did you not think of everything ? 

So, I should be able to create SQL INSERT statements as well, right? 

Phil Harvey

...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 ($).

Tr

#4
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 )',
        },

Phil Harvey

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:

%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.
...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 ($).

Tr

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