Author Topic: import data excel  (Read 10653 times)

Offline franslemmens

  • Newcomer
  • *
  • Posts: 41
    • View Profile
import data excel
« on: November 27, 2008, 06:49:04 AM »
Is there a possibility to import data (captions and keywords) from an excel file (or saved as a text file) in the IPTC in PhotoMechanic?

Thanks and Best regards,

Marjolijn van Steeden (Mac user)

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 24970
    • View Profile
    • Camera Bits, Inc.
Re: import data excel
« Reply #1 on: November 27, 2008, 11:18:28 AM »
Marjolijn,

Is there a possibility to import data (captions and keywords) from an excel file (or saved as a text file) in the IPTC in PhotoMechanic?

Yes, use the Text exporter module in the Export dialog.  You can use the {tab} variable to separate your desired export fields.  Use {caption} and {keywords} like so:

{caption}{tab}{keywords}

as your export string and then export to a single file.

That should do it.

-Kirk

Offline franslemmens

  • Newcomer
  • *
  • Posts: 41
    • View Profile
Re: import data excel
« Reply #2 on: November 27, 2008, 12:28:52 PM »
Dear Kirk,

Thanks for the fast reply. I will try tomorrow, but what I understand from your explanation is
the other way around.

I mean to import data (caption and keyword columns) from an excel sheet
into the IPTC fields in PhotoMechanic.
(A company in India did the captioning and keywording in an excelsheet for about 2000 images)

I use iMac (OSX10.4.11) and PhotoMechanic 4.5.4

Thanks again,
Marjolijn


Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 24970
    • View Profile
    • Camera Bits, Inc.
Re: import data excel
« Reply #3 on: November 27, 2008, 05:22:58 PM »
Marjolijn,

Thanks for the fast reply. I will try tomorrow, but what I understand from your explanation is
the other way around.

I mean to import data (caption and keyword columns) from an excel sheet
into the IPTC fields in PhotoMechanic.
(A company in India did the captioning and keywording in an excelsheet for about 2000 images)

I use iMac (OSX10.4.11) and PhotoMechanic 4.5.4

I misunderstood.  There is no such feature in Photo Mechanic.

-Kirk

Offline devenh

  • Sr. Member
  • ****
  • Posts: 435
    • View Profile
Re: import data excel
« Reply #4 on: November 28, 2008, 03:45:40 PM »
Marjolijn,

Kirk is correct that there is no direct way to import Excel data, but it can be done with a little effort.  Given you have 2000 images, I think it will be well worth your time.

You'll need to become familiar with Code Replacements.  See the section in the PM pdf manual that discusses this.

Next, you'll have to output your Excel file to a text file in Code Replacement format.  Suppose your Excel file looks something like this:

img_500 mycaption500 mykeywords500
img_501 mycaption501 mykeywords501

What I do is create another sheet in the workbook that looks like:

img_500a *tab* mycaption500
img_500b *tab* mykewords500
img_501a *tab* mycaption501
img_501b *tab* mykewords501

where *tab* is the tab character.  Note I need to add an 'a' and a 'b' to the filenames to distinguish which field they will go in.

Once the CR file is in place and ready to use by PM, the in then IPTC Stationary Pad:

Caption field ==> /{filenamebase}a/
Keywords field ==> /{filenamebase}b/

Apply this Stationery to the 2000 images and you're good to go!

I've probably skipped a detail or two, but hopefully this gets you started.

This process would be made *much* easier if some of the suggestions in this thread found their way into PM:

http://forums.camerabits.com/index.php?topic=2958.0

Deven



Offline franslemmens

  • Newcomer
  • *
  • Posts: 41
    • View Profile
Re: import data excel
« Reply #5 on: November 29, 2008, 05:16:30 AM »
Dear Deven,

Thanks very, very much for your help.

The only thing: It doesn’t work with

/{filenamebase}/ but it works with 
\{filename}\ 
(I use the jpg extension in the filename)

To avoid making 2 filenames A and B I copy the text file and make one text file with Filename and Caption and one text file with Filename and Keywords.

As far as I can see it works perfect.

Thanks again

Have a nice weekend,
Marjolijn

Offline devenh

  • Sr. Member
  • ****
  • Posts: 435
    • View Profile
Re: import data excel
« Reply #6 on: November 29, 2008, 07:20:52 AM »
To avoid making 2 filenames A and B I copy the text file and make one text file with Filename and Caption and one text file with Filename and Keywords.

Yes, this approach greatly simplifies the Excel code required to convert the spreadsheet to a CR compatible text file.  It does get a bit cumbersome if you are dealing with 3, 4, or 5 fields, and thus the suggested PM feature request I referred to earlier which would make this significantly easier.

Deven

Offline Kirk Baker

  • Senior Software Engineer
  • Camera Bits Staff
  • Superhero Member
  • *****
  • Posts: 24970
    • View Profile
    • Camera Bits, Inc.
Re: import data excel
« Reply #7 on: November 29, 2008, 07:25:03 AM »
Deven,

To avoid making 2 filenames A and B I copy the text file and make one text file with Filename and Caption and one text file with Filename and Keywords.

Yes, this approach greatly simplifies the Excel code required to convert the spreadsheet to a CR compatible text file.  It does get a bit cumbersome if you are dealing with 3, 4, or 5 fields, and thus the suggested PM feature request I referred to earlier which would make this significantly easier.

I'll see if I can get such a feature into version 4.6 (maybe not the initial release).

-Kirk

Offline franslemmens

  • Newcomer
  • *
  • Posts: 41
    • View Profile
Re: import data excel
« Reply #8 on: November 29, 2008, 07:30:10 AM »
Thanks Kirk and thanks Deven.

Deven, I don't know if I understand you well, but the excell sheets with filename, caption and keywords of 2000 images are allready finished.

So if I do it like your first proposal I have to insert rows and copy the filename 2000 times.

Anyway thanks again,
Marjolijn



Offline devenh

  • Sr. Member
  • ****
  • Posts: 435
    • View Profile
Re: import data excel
« Reply #9 on: November 29, 2008, 07:38:56 AM »
So if I do it like your first proposal I have to insert rows and copy the filename 2000 times.

It might seem that way at first, but what you really have to do is write some complicated cell formulas (based on row and column positions) that copy the data into a format compatible with the CR feature.

Again, your approach is fine if it is a one time conversion.  My approach is much more complicated, but is well suited to situations where the fields are being frequently updated or where the conversion takes place after each shoot.

Deven

Offline franslemmens

  • Newcomer
  • *
  • Posts: 41
    • View Profile
Re: import data excel
« Reply #10 on: November 29, 2008, 07:53:58 AM »
Hi Deven,

It's only ONE 'batch import' which will occur maybe every month, so I am very happy with this solution. The first option is too difficult for me (maybe later).

But maybe like Kirk wrote there will be an option in PM 4.6

Like in FileMakerPro it's very easy to import data from an excel file
(just match the fields).

Have a nice weekend,
Marjolijn

Offline John OHara

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: import data excel
« Reply #11 on: December 06, 2008, 10:58:38 AM »
I do this for all my events. There are a number of steps, take them one at a time.
You MUST have a text editor, Textwrangler, is a free download. I have been using it for years.
1. Select a column, copy it,  paste it into a new textwrangler document. Give it an identifiable name. Do this with each column of information you need.
note: this data can be imported into PM. You have to add menuitem tags ETC. If this is what you want to do, reply to this email and we will go on. Once you have learned how to do it and the steps it is difficult. There is more to this.
If you want to learn it, I'll help. I just don't want to give to you so much information that you don't have use for, or know what to do with it. John

Offline franslemmens

  • Newcomer
  • *
  • Posts: 41
    • View Profile
Re: import data excel
« Reply #12 on: December 09, 2008, 02:29:26 AM »
Dear John,

Thanks for your idea. I am not sure if I understand what you write. I don't understand why I should use a text editor. What I do: I copy the filename and keywords colums  to an empty sheet of excel and save it as a .txt file (text divided by tabs.txt) in order to import to PhotoMechanin and that works perfect.
Or do I miss your point?
Anyway thanks,
Marjolijn

Offline John OHara

  • Newcomer
  • *
  • Posts: 21
    • View Profile
Re: import data excel
« Reply #13 on: December 10, 2008, 07:26:42 PM »
I photograph Equestrian events. ( I used to work at the San Francisco Chronicle) I have as many or more than 400 entry's at a single event. Each exhibitor, has an Entry Number (category field), Horse Name (title field), Rider Name ( credit field) and Owner Name ( source field). I import into PM each column of information into the specific field I have described.
Now, When I , Identify an exhibitor, I select the entry#, Horse, Rider and owner and create an .IPT file in PM. Now each time I see that horse, I "load" that .IPT file. It inputs all the data for that exhibitor on each photo I have made of that horse.
Data, is so important during and after the event for proofing, and locating a horse/ or exhibitor or Owner.Is this something that will work for you ? If you look at my web site and select an eventn, then do a search, you will see how the exhibitor ( customer, the person who writes you the check) finds their photos. The link is, www.johnoharaphoto.com
...John O'Hara

Offline franslemmens

  • Newcomer
  • *
  • Posts: 41
    • View Profile
Re: import data excel
« Reply #14 on: December 16, 2008, 01:55:18 AM »
Hi John,

It's very interesting to see the possibilities, and it looks very professional on your site.
But your solution is 'one bridge too far' for my usage.

Anyway thanks one more time and all the best,
Marjolijn