Author Topic: Request: '|' separator for -csv format  (Read 2247 times)

Tr

  • Jr. Member
  • **
  • Posts: 29
Request: '|' separator for -csv format
« 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
« Last Edit: February 05, 2014, 02:59:47 PM by Tr »

Phil Harvey

  • ExifTool Author
  • Administrator
  • ExifTool Freak
  • *****
  • Posts: 16910
    • ExifTool Home Page
Re: Request: '|' separator for -csv format
« Reply #1 on: February 05, 2014, 07:25:55 PM »
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, use single quotes (') instead of double quotes (") around arguments containing a dollar sign ($).

Tr

  • Jr. Member
  • **
  • Posts: 29
Re: Request: '|' separator for -csv format
« Reply #2 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? 

Phil Harvey

  • ExifTool Author
  • Administrator
  • ExifTool Freak
  • *****
  • Posts: 16910
    • ExifTool Home Page
Re: Request: '|' separator for -csv format
« Reply #3 on: February 07, 2014, 11:44:04 AM »
Sure.

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

Tr

  • Jr. Member
  • **
  • Posts: 29
Re: Request: '|' separator for -csv format
« Reply #4 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 )',
        },
« Last Edit: February 07, 2014, 06:39:33 PM by Tr »

Phil Harvey

  • ExifTool Author
  • Administrator
  • ExifTool Freak
  • *****
  • Posts: 16910
    • ExifTool Home Page
Re: Request: '|' separator for -csv format
« Reply #5 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.
...where DIR is the name of a directory/folder containing the images.  On Mac/Linux, use single quotes (') instead of double quotes (") around arguments containing a dollar sign ($).

Tr

  • Jr. Member
  • **
  • Posts: 29
Re: Request: '|' separator for -csv format
« Reply #6 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