• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Can you import metadata from an excel database to images (JPG, PSD, TIF) in bulk?

New Here ,
Apr 23, 2012 Apr 23, 2012

Copy link to clipboard

Copied

I am very new to working with metadata. I have a microsoft excel file with the IPTC Core fields I need for each image file.

(Creator, Headline, Description, Keywords, Title, Job Identifier, Credit Line, Source, Rights Usage Terms, Copyright Status and Copyright Notice)

Is there a way to get the metadata into the files without having to copy from a cell into each metadata field individually?

I am hoping some for sort of script, possibly...

Also, if only ONE of these fields needed updating in all of the files (i.e. Rights Usage Terms) can that be done?

I have Bridge 5.1 (and earlier versions CS3/CS4)

TOPICS
Batch , Import and export , Scripting

Views

33.7K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 2 Correct answers

Valorous Hero , Apr 23, 2012 Apr 23, 2012

This might be of use...

DIY Metadata.jsx

 

EDIT: the link is there now: DIY Metadata.jsx

Votes

Translate

Translate
Community Expert , Mar 14, 2023 Mar 14, 2023

Perhaps try tab delimited format instead of comma separated value format.

 

Edit – Paul Riggott originally wrote the following instructions:

 

1616665_DIY.png

 

N.B. The first field in the input file MUST be the filename only IE:- CRW_0001.jpg

This script will allow you to choose the fields and order they are in within either your CSV or TEXT file so that you can input your metadata.

Fields available are:-

Keywords 1 - These are Keywords that are in ONE field seperated by semicolons

Keywords 2 - These are Key

...

Votes

Translate

Translate
replies 127 Replies 127
Community Expert ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

This appears to have worked, check the metadata:

DJI_0002_EDITED.jpg

I’m still downloading the sample images, however your CSV has fiilename.JPG while your sample file had filename.jpg – so I’m guessing that this is case sensitive!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

I fixed the file names in the CSV file to .jpg.  But it still locks up as soon as I select the CSV file and click on OK.  So there is something else wrong. 

What did you name your script?  I named mine DJI_script.jsx.  Should it be something else.  Is it ok to have the _ ?

I am leaving and will be back in 1 hour.  Will try it again and see if you need me to try something different.  I think mayby a file name or setting is wrong.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

Works fine on a Mac running Bridge CC 2018...

I tried in Windows on Bridge CC 2019, however I received the following error message:

error.png

So perhaps too many images selected in one go!

After hitting OK, Bridge still appears to be trying to process the original request, the files are processing in the status bar:

status.png

After waiting a while and coming back, it appears that the metadata has been written (confirmed using File Info Raw Data and with ExifTool).

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

Stephen,

I tried it on my super computer and same thing.  It crashed (locked) up immediately.  I have to believe there is something else wrong. 

1. Can you tell me what you named the script?  I named mine DJI_script.jsx  . 

Not sure if that can make a difference but I will name my script the same thing you have.  There has to be something with my script that is causing it to lock up.  I have my script uploaded to dropbox.  So you may have been able to download it when you downloaded the images.  I checked and the contents of the script are identical to what SuperMerlin posted above.  The only thing that may be different is the file/script name.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

In Bridge CC2018 on the Mac, I named the script:

Drone Metadata from CSV.jsx

The text was copied from the forum post, pasted into ExtendScript Toolkit and saved.

While in Bridge CC2019 on Windows, I kept the name and script exactly the same as from your download.

P.S. I have stopped working on the ExifTool side of things as Bridge was looking good, however ExifTool is always a fallback...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

Thanks Stephen. No idea why it is freezing on both PC's.  I put the script in the folder for startup scripts and then restarted bridge.  I don't know what else to try.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

Have you tried just selecting a single image?

Or removing say half the rows from the CSV?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

I just did 5 images (with only 5 rows in CSV file).  It processed fine.  Soooo, I will need to test and keep adding more and more until it stops to see if there is a limit.

Also, I had renamed the extensions to .jpg when in fact they are .JPG (all caps).  I need to try it with lower case (.jpg) to see if that has anything to do with it.

Are you familiar with ExifTool?  If not, do you know anyone that knows it like you do the Adobe Bridge?  It would be really nice if I was able to use the ExifTool CLI to do this   But I certainly could not figure that out.  I would not mind paying someone if they could format the code I needed to use ExifTool CLI.  You have been very helpful and I appreciate it.  I will keep testing more and more images to see how it goes doing this in Bridge.

If Bridge is the only way I can do this, I need to figure out how to edit the script to so that it updates the Lat, Lon, and Altitude data.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 06, 2019 Feb 06, 2019

Copy link to clipboard

Copied

Yes I am familiar with ExifTool and as a newb to scripting I am more comortable with ExifTool… However this will require a custom config file to write the metadata, which is not something that I do every day in ExifTool so I need to put in some research.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 07, 2019 Feb 07, 2019

Copy link to clipboard

Copied

(1) OK, the ExifTool custom config file was not as obvious as I would have thought, however with the help of the ExifTool forum I have an answer.

%Image::ExifTool::UserDefined = (

  'Image::ExifTool::DJI::XMP' => {

        RtkFlag => { Writable => 'rational',  },

        RtkStdLon => { Writable => 'rational',  },

        RtkStdLat => { Writable => 'rational',  },

        RtkStdHgt => { Writable => 'rational',  },

    },

);

I saved this plain text file with a .config extension on the desktop with a name of: drone-dji.config (ensure that there is no double extension with .txt). This is the easy option that does not require installation, the .config file path is simply referenced in the command line code below.

(2) I have a folder full of images to process on the Desktop called “Input Folder"

(3) I also have a comma separated value text file on my desktop called “exiftool_import.csv” with the following structure (header row with shortcut tag names, full system path to each file and required metadata values under each tag):

csv.png

SourceFile,RtkFlag,RtkStdHgt,RtkStdLat,RtkStdLon

/Users/username/Desktop/DJI_0002.JPG,16,3.37184,1.25373,1.85592

(Note: in addition to the “shortcut” tags listed above, the full tag can also be used:  XMP-drone-dji:RtkFlag  etc.)

(4) Finally, the following ExifTool command has to first list the full path to the .config file as well as the CSV file and the input folder to process:

exiftool -config '/Users/username/Desktop/drone-dji.config' -sep ',' -csv='/Users/username/Desktop/exiftool_import.csv' -r '/Users/username/Desktop/Input Folder'

IMPORTANT NOTE: ExifTool command line code on the Mac uses ' straight single quotes, while on MS Windows you'll need to use " straight double quotes. The code above uses the recursive  -r  command to process all sub-directories under the main top level folder. Further arguments can be added to exclude specific sub-directories or to only process specific file extensions.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

Stephen,

Sweet setup on the Exiftool.  I have a question.  I modified your script and it runs perfectly (as I have 7 fields to update).  I made a backup copy of the Image folder before running ExifTool

The one thing I can not figure out is how to output the "updated" images to a subfolder so that it will leave the originals.  I tried using the -o . option and I could not get it to work. 

-------------------------

exiftool -config C:\Exiftool\drone-dji.config -sep ',' -csv="M:\Midsouth360\Missions\PPK Test Missions\2018-12-22 Snowden PPK\P4P\Snowden-12-22-Exif.csv" -r "M:\Midsouth360\Missions\PPK Test Missions\2018-12-22 Snowden PPK\P4P" -overwrite_original

-------------------------

I used -overwrite_original so it would not create backups in the same folder as originals.  That won't work for my work flow.

For the command line above, I would like to output the edited images to a subfolder [Pix4D]

M:\Midsouth360\Missions\PPK Test Missions\2018-12-22 Snowden PPK\P4P\Pix4D\

I have installed AutoHotkey scripting program and it seems pretty sweet.  My next thing to tackle will be figuring out how to use AutoHotkey to allow me to:

1. Input Config file Location

2. Input CSV file location

3. Input Output Folder (once I get it figure out).

Then click RUN  button to put those "inputs" together and format and run the command like I have above.

You and SuperMerlin are great!  Thank ya'll.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

The one thing I can not figure out is how to output the "updated" images to a subfolder so that it will leave the originals.

Firstly, I would like to say that I am not very happy with the following solution (it is inefficient, slow and a hack), however it is the best that I can come up with on the spot! It does work… However I am hoping that a better approach may come with more research and fiddling around.

I have simplified the example below for testing by writing a single tag without using a CSV etc. If you are happy with it, you should be able to adapt the code to work with the CSV import code.

____________________

Step 1: write the metadata creating a backup file and create a new directory called “EDITED” and duplicate the edited file to the directory:

exiftool -config '/Users/username/Desktop/drone-dji.config' -RtkFlag=16 -hardlink='%dEDITED/%f.%e' -r '/Users/username/Desktop/Input Folder'

Step 2: Wait for the first step’s command to complete, then run the following command to tidy up, deleting the edited file and restoring the original file (which leaves the edited file in the new EDITED directory):

exiftool -restore_original -r '/Users/username/Desktop/Input Folder'

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

Wouldn't it be easier to simply make a backup copy of the originals first and then replace the ones you are changing? I assume that exiftool can send shell commands directly to the system?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

Hey Lumigraphics, it would be very easy to just copy and paste the folder in Windows Explorer (which is what I am doing now as I test this stuff).  But if ExifTool can just dump the edited photos into a new sub-folder, then accidentally forgetting to make a backup is never an issue.  I need all the help I can get in that area

I was hoping that ExifTool had an option code that would force the new edited images to put in a specified folder.  But that may not be the case.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

Hi Lumigraphics, ExifTool automatically creates backup copies unless instructed not to… So a copy is made and edited i.e. myfilename.jpg and the original unaltered file would be renamed to myfilename_original

The challenge as I see it is directing the command line to recursively process the required images in specific directories. We can ignore specifically named subdirectories, but we can’t ignore the top level input directory.

I am only looking at this from an ExifTool perspective, you are indeed correct that either a command line or batch file should be able to pipe and or mix together different commands.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

You should be able to run a shell command that calls exiftool, after you make backups. If there is a problem with directories, try walking the directories in your shell script and calling exiftool multiple times.

Shell script meaning .bat, powershell, bash, applescript, whatever you are comfortable with.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

That would certainly solve the problem.  Simple and it would work.  Now I just have to figure out why the GPS data is not getting written to the XMP block and I will be good to go   I feel 99.9% sure it is because the GPS data already exist in the EXIF block.  My setup is writing to the EXIF block and it needs to write to the XMP block.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

Perhaps you are making this more complicated with the GPS tags... Try removing the custom GPS lines from the config file, I would have thought that they were standard?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

>Edit: your csv is using tag shortcuts, try using the explicit full group:tag instead.

Can you explain that a little further.  I am not exactly what change I need to make here.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

Can you explain that a little further.  I am not exactly what change I need to make here.

Rather than just using the shorthand form of GpsLatitude which may conflict with another tag in another group, use the longhand form XMP-drone-dji:GpsLatitude instead to explicitly target where the data should be written. I made a veiled reference to this in post #81.

EDIT: Leave the .config file alone, that appears to be correct

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

I missed that reference.  sorry about that.  I did make the change to the CSV headers and exiftool did not like that all.  It just sits there.  I got to go fly a mission and I will be back   It is about 30° so I won't be just hanging around outside!

ExifTool CSV setup example2.jpg

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

You may need to isolate which if any are the problem tags in the CSV or config file.

I think that it is getting to the point where you may need to take this to a more appropriate forum, then come back and update this thread once things are working 100%.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 08, 2019 Feb 08, 2019

Copy link to clipboard

Copied

Stephen, I will certainly do that and update back here once I get it solved.  The Bridge method works perfectly so if the ExifTool method can't be worked out, I have a way to do this.  Have a great weekend!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 09, 2019 Feb 09, 2019

Copy link to clipboard

Copied

Stephen, the ExifTool is working nicely.  I wanted to post back the final solution.

Config file placed in the c:\Exiftool\ directory

%Image::ExifTool::UserDefined = (

  'Image::ExifTool::DJI::XMP' => {

        RtkFlag => { Writable => 'rational',  },

        RtkStdLon => { Writable => 'rational',  },

        RtkStdLat => { Writable => 'rational',  },

        RtkStdHgt => { Writable => 'rational',  },

        GpsLongitude => { Writable => 'rational',  },

        GpsLatitude => { Writable => 'rational',  },

        AbsoluteAltitude => { Writable => 'rational',  },

GPSLongitude => { Writable => 'rational',  },

        GPSLatitude => { Writable => 'rational',  },

    },

  'Image::ExifTool::EXIF' => {

GPSLongitude => { Writable => 'rational',  },

        GPSLatitude => { Writable => 'rational',  },

GPSAltitude => { Writable => 'rational',  },

    },

);

CSV Header Format:

SourceFile RtkFlag RtkStdLon RtkStdLat RtkStdHgt XMP-drone-dji:GpsLongitude XMP-drone-dji:GpsLatitude AbsoluteAltitude GPSLongitude GPSLatitude GPSAltitude

Command Line:

exiftool -config C:\Exiftool\drone-dji-v2.config -sep ',' -csv="M:\Midsouth360\Missions\PPK Test Missions\2018-12-22 Snowden PPK\P4P\Snowden-12-22-Exif-drone-dji-v2.csv" -r "M:\Midsouth360\Missions\PPK Test Missions\2018-12-22 Snowden PPK\P4P" -overwrite_original

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Feb 11, 2019 Feb 11, 2019

Copy link to clipboard

Copied

Please try this now.

N.B. The last three fields in the csv are not required as they are duplicates.

//   #target bridge  

    if( BridgeTalk.appName == "bridge" ) {  

    var drone = new MenuElement( "command", "Drone Metadata", "at the end of Tools" ); 

    } 

    drone.onSelect = function () { 

    var csvFile = File.openDialog("Open Comma-delimited File","comma-delimited(*.csv):*.csv;"); 

    if (!csvFile.exists) return;

    var folder = Folder(app.document.thumbnail.path);

    if(!folder.exists){

        log("You are not in a valid folder. Is this a collection?");

        return;

        }

        csvFile.open('r'); 

        var data = csvFile.read().split("\n"); 

        csvFile.close(); 

        var line = new Array(); 

        for(var a in data){ 

            var Q = data.toString(); 

            if(Q.length > 2) line.push(Q); 

            } 

    for(var z in line){ 

    var p=line.split(','); 

    droneMetadata(trim(p[0]),trim(p[1]),trim(p[2]),trim(p[3]),trim(p[4]),trim(p[5]),trim(p[6]),trim(p[7]));

    } 

    alert("Batch complete\nDrone Error Log on Desktop\n If any errors occured"); 

    function trim(s){ 

        return s.replace(/^\s+|\s+$/g,''); 

        } 

    function log(message){ 

    var errorLog = File(Folder.desktop + "/Drone Error Log.txt"); 

    errorLog.open("a"); 

    errorLog.writeln(message); 

    errorLog.close(); 

      } 

  function ddToDms(lat, lng) {

   var lat = lat;

   var lng = lng;

   var latResult, lngResult, dmsResult;

   lat = parseFloat(lat); 

   lng = parseFloat(lng);

   latResult = getDms(lat);

   latResult += (lat >= 0)? 'N' : 'S';

   lngResult = getDms(lng);

   lngResult += (lng >= 0)? 'E' : 'W';

   dmsResult = [latResult, lngResult];

   return dmsResult;

};

function getDms(val) {

   var valDeg, valMin, valSec, result;

   val = Math.abs(val);

   valDeg = Math.floor(val);

   result = valDeg + ",";

   valMin = Math.floor((val - valDeg) * 60);

   result += valMin + ".";

   valSec = Math.round((val - valDeg - valMin / 60) * 3600 * 1000);

   result += valSec.toString().match(/^\d{4}/);

   return result;

};

    function droneHeight(H){

        return Math.floor(H*1000).toString() + "/1000";

        }

    function droneMetadata(th,Flag,StdLon,StdLat,StdHgt,Lat,Lon,Hgt){ 

    var file = new File(new Folder(folder)+ '/' + trim(th)); 

    if(!file.exists){ 

        log("File does not exist! " + trim(th)); 

        return; 

        } 

    var thumb = new Thumbnail(file); 

    md = thumb.synchronousMetadata; 

    if(thumb.hasMetadata){ 

    md.namespace = "http://www.dji.com/drone-dji/1.0/"; 

md.RtkFlag = ''; 

md.RtkFlag =  Flag; 

md.RtkStdLon = ''; 

md.RtkStdLon = StdLon; 

md.RtkStdLat = ''; 

md.RtkStdLat = StdLat; 

md.RtkStdHgt = ''; 

md.RtkStdHgt = StdHgt; 

md.GpsLatitude = ''; 

md.GpsLatitude = Lat; 

md.GpsLongitude = ''; 

md.GpsLongitude = Lon; 

md.AbsoluteAltitude = ''; 

md.AbsoluteAltitude = Hgt;

var LatLon = ddToDms(Lon,Lat);

md.namespace = "http://ns.adobe.com/exif/1.0/";

md.GPSLatitude = ''; 

md.GPSLatitude = LatLon[0]; 

md.GPSLongitude = ''; 

md.GPSLongitude = LatLon[1]; 

md.GPSAltitude = ''; 

md.GPSAltitude = droneHeight(Hgt); 

    }else{ 

        log("Cannot write metadata to " + decodeURI(thumb.spec)); 

        } 

    }; 

    }; 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines