Author Topic: Convert Excel for Code Replacement  (Read 9174 times)

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Convert Excel for Code Replacement
« on: October 02, 2010, 02:50:36 AM »
Any suggestions on how to convert excel sheet with multiple columns to UTF-8 for the code replacement function, whereas the first column will serve as the code and remaining columns as the replacement.
Sofar have been unable to do so. OSX 10.6.4 PM latest beta. Many thanks ! Joop

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25024
    • View Profile
    • Camera Bits, Inc.
Re: Convert Excel for Code Replacement
« Reply #1 on: October 02, 2010, 08:53:37 AM »
Any suggestions on how to convert excel sheet with multiple columns to UTF-8 for the code replacement function, whereas the first column will serve as the code and remaining columns as the replacement.
Sofar have been unable to do so. OSX 10.6.4 PM latest beta. Many thanks ! Joop

Just save out as tab separated values from Excel.  Open the file with a Unicode capable text editor (the built-in TextEdit will suffice but you have to open it as UTF-8) and fix any characters that don't look right.  Bring it into PM.

-Kirk

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: Convert Excel for Code Replacement
« Reply #2 on: October 02, 2010, 02:55:46 PM »
Many thanks Kirk for your prompt answer !  I must be doing something wrong...... I followed your instructions (at least I attempted to do so) - but I do not get the result I hoped for. Once I type the code, I only get the data from the first replacement column, somehow it ignores the data in the other remaining replacement columns. Would you mind taking a look at my excel sheet?
Once again for your great service ! Brgds Joop

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25024
    • View Profile
    • Camera Bits, Inc.
Re: Convert Excel for Code Replacement
« Reply #3 on: October 02, 2010, 04:05:46 PM »
Joop,

Many thanks Kirk for your prompt answer !  I must be doing something wrong...... I followed your instructions (at least I attempted to do so) - but I do not get the result I hoped for. Once I type the code, I only get the data from the first replacement column, somehow it ignores the data in the other remaining replacement columns. Would you mind taking a look at my excel sheet?

If you want all of the data in the remaining columns to appear when you enter your code, then you need to remove the tab characters between each additional column and replace them with spaces.  Otherwise you need to use the extended Code Replacement syntax and specify with the code which column you want to use.  That syntax is like so: code#column

For instance if you want the data in the third column you'd use:

code#3

HTH,

-Kirk

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: Convert Excel for Code Replacement
« Reply #4 on: October 04, 2010, 10:59:08 AM »
KIRK, many thanks !!  This is a great solution, on basis of your feedback, I have come to the ideal set-up to automatically fill several IPTC fields at the same time from one excel sheet with multiple columns by putting the code (in my case) in the Headline field and use the extended code replacement syntax in combination with the variable function to copy all relevant columns to their corresponding IPTC fields, this saves me days and days of manual work ! . Once again many thanks for the great customer support. Brgds Joop

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: Convert Excel for Code Replacement
« Reply #5 on: October 05, 2010, 10:59:36 AM »
Hi Kirk, the advanced code replacement works ! managed to add IPTC data from an excel sheet with 17 columns/400 rows  to over 14000 images in the shortest time possible.   I have one follow-on question. In case I use advanced code replacement I may have files were a particular column in my excel sheet does not contain any data (empty field), in this case it will show the code replacement data i.e. \{headline}#16\  will show as\B-HUL#16\    . Is there a way to prevent advanced code replacement from placing the code in case the source field is empty?
Many thanks, Joop.

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25024
    • View Profile
    • Camera Bits, Inc.
Re: Convert Excel for Code Replacement
« Reply #6 on: October 05, 2010, 11:52:20 AM »
Joop,

Hi Kirk, the advanced code replacement works ! managed to add IPTC data from an excel sheet with 17 columns/400 rows  to over 14000 images in the shortest time possible.   I have one follow-on question. In case I use advanced code replacement I may have files were a particular column in my excel sheet does not contain any data (empty field), in this case it will show the code replacement data i.e. \{headline}#16\  will show as\B-HUL#16\    . Is there a way to prevent advanced code replacement from placing the code in case the source field is empty?

No, there isn't any way to prevent that from occurring.  Any suggestions as to what it should do?

-Kirk

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: Convert Excel for Code Replacement
« Reply #7 on: October 05, 2010, 01:10:44 PM »
Kirk, I would prefer that in case advance code replacement is being used on an empty column that it will ignore the code replacement for that field.
I hope it makes sense ?
Thanks Joop

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: Convert Excel for Code Replacement
« Reply #8 on: October 07, 2010, 12:06:49 PM »
Kirk, is there a limit to the size of the code / advanced code replacement file ?
Many thanks
Joop

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25024
    • View Profile
    • Camera Bits, Inc.
Re: Convert Excel for Code Replacement
« Reply #9 on: October 07, 2010, 12:19:27 PM »
Joop,

Kirk, I would prefer that in case advance code replacement is being used on an empty column that it will ignore the code replacement for that field.
I hope it makes sense ?

Do you mean that you want it to just become an empty replacement? (removing the code and its delimiters in the process)

Quote from: JoopPlaisier
Kirk, is there a limit to the size of the code / advanced code replacement file ?

There is no hard limit to the number of codes or replacements.

-Kirk

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: Convert Excel for Code Replacement
« Reply #10 on: October 07, 2010, 12:53:10 PM »
Kirk,

A. Yes, just an empty replacement.

B. Many thanks for the feedback, I will experiment with a larger number of codes and replacements. Sofar the advanced code replacement has taken an important place in my workflow and is saving me months of work !

Many thanks for the great support !

Joop

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: Convert Excel for Code Replacement
« Reply #11 on: October 09, 2010, 03:46:36 AM »
Kirk, I am extremely pleased to report that my testing with a large xls file converted to utf8 for advanced code replacement went ok and the test results are that with a file of over 54000 rows and 16 columns the code replacement is blazing fast..... !! The "hidden" powers of PhotoMechanic are beyond imagination !  Many thanks. Joop

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 25024
    • View Profile
    • Camera Bits, Inc.
Re: Convert Excel for Code Replacement
« Reply #12 on: October 11, 2010, 10:27:34 AM »
Joop,

A. Yes, just an empty replacement.

You can choose a default replacement for the case where there does not exist a replacement for the entered code.  You cannot leave it blank and have your non-existent code replaced with nothing however.  But you could set it to an obvious string of characters that you could later do a bulk Find/Replace on to clear them out.  For instance if you were to set the default replacement to "!missing!" (without the quotes), you could later do a Find and Replace, finding "!missing!" and replacing with "".  (Don't enter the quotes.)

That's a solution to your issue that won't require me to make any changes that might break some of the more advanced functionality of Code Replacement.

-Kirk

Offline JoopPlaisier

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: Convert Excel for Code Replacement
« Reply #13 on: October 12, 2010, 12:16:46 PM »
Thanks Kirk, this works well ! Many thanks again. Joop