Author Topic: Import of data from MS ACCESS databases into ExifTool  (Read 4705 times)

Aemetz

  • Newbie
  • *
  • Posts: 7
Import of data from MS ACCESS databases into ExifTool
« on: January 23, 2014, 05:02:02 PM »
Hi ExifTool experts!

Whilst discovering ExifTool (and ExifToolGUI ) being an excellent program to modify date & time, I had a closer look to the modifying functions for Metadata.
Searching the forum for keywords such as "input from MS access" or "Excel", I couldn't find any matches thay may help me with my problem.
My "lifetime" project is digitizing a huge number of colour slides. As they do not carry any metadata by nature  ;D I collected all relevant camera data such as aperture, shutter speed, creation date as well as general data like location, subject, title, keywords etc. in a taylor-made software which isn't supported any longer.  As a result, I have exported the data into several MS Access databases now containing all this information.

The last days I was successful in making my first steps to input the data using the ExifToolGUI. However, as I have to input a huge number of data into the metadata fields of the corresponding files (scanned colour slides) I am looking for an automated operation or batch process.

Is there anybody who might be able to give my some hints or links or, in best case, who did this already?
I do not intend to manually input thousands of data by the copy and paste method.

Your help and ideas are highly appreciated!

Kind regards
Aemetz

Phil Harvey

  • ExifTool Author
  • Administrator
  • ExifTool Freak
  • *****
  • Posts: 16883
    • ExifTool Home Page
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #1 on: January 24, 2014, 07:13:35 AM »
Hi Aemetz,

The -csv option (in the command-line application) is the way to import from a spreadsheet.  I should probably make this one of the FAQ's, since import from a database is a fairly common question.  Search for "csv=" in this forum to find threads on this topic (ie. this one)

- 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 ($).

Aemetz

  • Newbie
  • *
  • Posts: 7
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #2 on: January 24, 2014, 08:14:53 AM »
Hi Phil,

Thanks for your quick reply.
I will perform a search with the recommended keyword "csv=".
Seems to be a good idea to include this one into the FAQ's.

As I am not yet familiar with all the details of EXIF, IPTC and XMP, you may be able to give me a hint regarding creation of new fields:
My former software had some special fields like "n° of magazine", "n° of slide inside magazine" and "box" that may not correspond to any existing field or tag in IPTC or XMP.
In order to include the information in these fields into the metadata, there are two different approaches to solve my from my point of view:
1. Link an existing field not used with the particular field of my former software.
2. Create a new field in either IPTC or XMP - but is this possible?

Your short expertise would be appreciated!

Kind regards
Aemetz

Phil Harvey

  • ExifTool Author
  • Administrator
  • ExifTool Freak
  • *****
  • Posts: 16883
    • ExifTool Home Page
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #3 on: January 24, 2014, 08:29:28 AM »
Aemetz,

My former software had some special fields like "n° of magazine", "n° of slide inside magazine" and "box" that may not correspond to any existing field or tag in IPTC or XMP.
In order to include the information in these fields into the metadata, there are two different approaches to solve my from my point of view:
1. Link an existing field not used with the particular field of my former software.
2. Create a new field in either IPTC or XMP - but is this possible?

2 is possible, and requires a config file to create the appropriate user-defined tags.  XMP is recommended.

Whether you choose 1 or 2 depends on the software you want to use to read this.  In general, 2 is preferred, but you may be forced into option 1 if your software won't read/display the new tags.

- 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 ($).

Aemetz

  • Newbie
  • *
  • Posts: 7
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #4 on: January 24, 2014, 09:18:23 AM »
Whether you choose 1 or 2 depends on the software you want to use to read this.  In general, 2 is preferred, but you may be forced into option 1 if your software won't read/display the new tags.

- Phil
Do you have a recommendation regarding the software for reading and/or displaying XMP files? I understood that XMP is a genuine Adobe development and I haven't found any freeware which is able to read / modify XMP files. I'm familiar with .txt, .csv and .dbf files only.
Maybe I need to explain my planned workflow but this would go beyond the scope of this post  ;)
Okay, I'll cut it short:
  • Export Access Database file into .xls format->
  • Create .csv format out of .xls file
  • Import .csv file into either CR2 or TIFF or JPEG files
As far as I understood your last comment I need to
  • create appropriate user-defined tags
  • export to .csv file
  • have to match the format of my "out-of-database" .csv file to the exported .csv file
prior to reimport the .csv file.

Am I correct?

Kind regards
Aemetz

Phil Harvey

  • ExifTool Author
  • Administrator
  • ExifTool Freak
  • *****
  • Posts: 16883
    • ExifTool Home Page
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #5 on: January 24, 2014, 09:54:23 AM »
Hi Aemetz,

I wasn't suggesting using XMP file format.  CSV is what I would recommend for the database.

I suggested XMP for the type of metadata that you write to your images. XMP is designed to be extensible, so is good if you are creating user-defined tags.

Your steps are correct.  I have added FAQ number 26 to help explain this.  Please let me know if you have any suggestions for additions/changes to this FAQ.

- 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 ($).

Aemetz

  • Newbie
  • *
  • Posts: 7
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #6 on: January 24, 2014, 12:39:46 PM »
Hi Phil,

I misunderstood!

What I now understand, following your latest post, is:
  • the right place to implement user-defined tags is the XMP section.
  • I don't need access to XMP file as I can output the metadata linked to a specific photo in .csv format. However, when reading about XMP, I had the idea that this format can be accessed and edited. Maybe I had to read more about the structure of metadata  ::)
  • For input of new or modified data .csv files are the right "tool".
  • In order to create user-defined tags I have to use a config file. I will follow your link and read the comments in the config file.

I have read your FAQ #26 carefully.
I will perform some tests the next few days to become familiar with the export and import functions and "behaviour" of the program.

Addendum:
... two hours later, I made the config file working. I had to create a path in the system environmental settings. Placing the config file in the \Windows directory didn't worked - in opposite to Bogdans post I found in the archive.
First, I have to find out how to create my own config file by modifying your example. (It's a long time ago I programmed Fortran and dBase  :P)
Then, I will use any .jpg file to output data in .csv following FAQ12 prior to input data. Let me start to my next level in wisdom  ;)

Kind regards
Aemetz


Thanks so far
Aemetz
« Last Edit: January 24, 2014, 02:51:40 PM by Aemetz »

Aemetz

  • Newbie
  • *
  • Posts: 7
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #7 on: January 25, 2014, 01:02:34 PM »
Hi Phil, Hi all

Following Phil's recommendation to use a config file to create user-defined tags I started with the sample config file from Phil.
My first steps end in testing the "-v2" and "-csv" options. Doing so, I discovered the following:

The command exiftool -v2 -NewXMPxmpTag=test FILE results in the exspected output (first line only) Writing XMP-xmp:NewXMPxmpTag.
However, trying to output another of the 16 tags listed in the sample config file, i.e.  XMP-xxx:NewXMPxxxTag1, I got a fault message that this tag doesn't exist.

Any idea what could have gone wrong?

Kind regards
Aemetz

Phil Harvey

  • ExifTool Author
  • Administrator
  • ExifTool Freak
  • *****
  • Posts: 16883
    • ExifTool Home Page
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #8 on: January 25, 2014, 01:46:30 PM »
Hi Aemetz,

It sounds like you didn't get the tag name right.  If you show me your command I could help.  You're using the sample config file without any changes?  If you changed something, include it in your post too so I can see.

- 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 ($).

Aemetz

  • Newbie
  • *
  • Posts: 7
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #9 on: January 25, 2014, 03:38:30 PM »
Hi Phil,

Looks like a false alarm  :-[
I produced the fault on a second PC (notebook).
Now I have changed to my desktop PC and created everything anew: specific folder for exiftool.exe, the sample config file as well as a subdirectory with some sample images.
I repeated the same commands on the desktop PC and it worked! Obviously, I produced a typing error when testing some commands with my notebook.
No further support from your side is required regarding the sample config file.   :D

However, do you have a link or a description for understanding the syntax of the commands in the config file?
Although I am a programmer I am struggling with the syntax in your sample config file.
As I wrote earlier, I need to define relations between my database and existing and user-defined fields in the metadata. I have to create some kind of a comparison table to find out for which data I need to define my own tags.

Example: My old colour slides archive software  includes five keywords (such as KEYWORD1, KEYWORD2 and so on), whereas EXIF  and XMP may contain less than those five.
I know that the EXIF definition includes dozens of tag names.
Any suggestions where I can find a comprehensive introduction or a collection of mostly used tags?

Thanks in advance
Aemetz

Phil Harvey

  • ExifTool Author
  • Administrator
  • ExifTool Freak
  • *****
  • Posts: 16883
    • ExifTool Home Page
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #10 on: January 25, 2014, 09:02:19 PM »
Hi Aemetz,

The config file is in the Perl language, so knowing a bit of Perl might help.

Complete details about the meanings of all the variables is found in lib/Image/ExifTool/README of the full distribution, but this contains a lot more too.  However it may be useful as a reference if you want to know the details about something specific.

What may be more useful is to look at more examples.  See the Composite tags in lib/Image/ExifTool/Exif.pm for more examples.

- 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 ($).

Aemetz

  • Newbie
  • *
  • Posts: 7
Re: Import of data from MS ACCESS databases into ExifTool
« Reply #11 on: January 26, 2014, 05:31:02 AM »
Hi Phil,
The config file is in the Perl language, so knowing a bit of Perl might help.

Complete details about the meanings of all the variables is found in lib/Image/ExifTool/README of the full distribution, but this contains a lot more too.  However it may be useful as a reference if you want to know the details about something specific.
I'm not familiar with Perl, but thanks for the recommendation.
I may spend more time on this in the future if I'm not satisfied with the examples.

What may be more useful is to look at more examples.  See the Composite tags in lib/Image/ExifTool/Exif.pm for more examples.
That might be the easiest and quickest approach to me.

Maybe somebody else with the same interest (digitizing and archiving colour slides) may read this and has already developed a workflow.

Kind regards
Aemetz