Convert Excel for Code Replacement

[JoopPlaisier]

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

[Kirk Baker]

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

[JoopPlaisier]

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

[Kirk Baker]

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

[JoopPlaisier]

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

[JoopPlaisier]

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.

[Kirk Baker]

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

[JoopPlaisier]

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

[JoopPlaisier]

Kirk, is there a limit to the size of the code / advanced code replacement file ?
Many thanks
Joop

[Kirk Baker]

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)

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

[JoopPlaisier]

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

[JoopPlaisier]

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

[Kirk Baker]

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

[JoopPlaisier]

Thanks Kirk, this works well ! Many thanks again. Joop