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
You can use the -p option to use any arbitrary format that you want.
- Phil
Genius! Did you not think of everything ?
So, I should be able to create SQL INSERT statements as well, right?
Sure.
- Phil
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 )',
},
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.
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