psedbtool

Photoshop Elements Database Tool, by John R. Ellis

psedbtool does not currently work with Photoshop Elements 9,and it’s unlikely I will invest any time making it work, since I’ve migrated to Lightroom. It appears that Adobe has made few changes to the PSE 9 Organizer, and the changes needed to support PSE 9 may be very simple; but based on experience with past versions, it may take many hours to verify that it works correctly. If you’d like a copy of psedbtool’s source (1800 lines of Perl), I’ll be glad to send it; and if you want to take over the maintenance, please let me know.

psedbtool is a free utility for use with Adobe Photoshop Elements (PSE) 6, 7, and 8 (Windows only).  With psedbtool  you can:

-          Detect problems with the locations of files recorded in PSE’s catalog.

-          Extract the catalog contents into text files readable by Excel and other tools.

-          Write the metadata of cataloged photos and files more reliably than with PSE.

This helps you maintain your PSE catalog, work around PSE bugs, use third-party tools with your catalog, and migrate your files and metadata into other programs.

Downloading, Installing, and Running
Detecting Problems with File Locations
Extracting Catalog Contents
Writing File Metadata
Command Syntax and Options
Bugs and Suggestions
Version History

Downloading, Installing, and Running

Download psedbtool (version 1.08)

1.      Click the link above and save the file to your desktop.

2.      Double-click the file you just downloaded to run it.  Click “Unzip” to extract the contents to c:\psedbtool.

3.      In Windows Explorer, navigate to c:\psedbtool and double-click the file run.bat. This will run psedbtool with the -printVolumeTable option and open the results in Notepad.  If someone is helping you on the Adobe user-to-user forums or on Elements Village, copy and paste the contents to the message thread.

Detecting Problems with File Locations

psedbtool detects two kinds of problems with file locations recorded in the catalog:

-          “Missing files” that aren’t where PSE thinks they should be (most likely because you moved them using Windows Explorer instead of PSE).

-          Inconsistencies in the catalog’s volume table caused by PSE bugs.

To get a more detailed analysis, use the -printVolumeTable option:

            c:\psedbtool\psedbtool -printVolumeTable

psedbtool will show the total number of missing files, information about current Windows drives, and detailed information about each “volume” in the PSE catalog.  A volume is a hard drive, flash drive, CD/DVD, or network drive from which you’ve imported files into the catalog. The ‑printVolumeTable option prints warnings for any volume inconsistencies.

When your catalog has volume-table inconsistencies, there are a number of possible symptoms:

-          PSE reports many of your files as missing (with a small “?” on the corner of the thumbnails in the Organizer).

-          In the Properties palette, the file location begins with an incorrect drive letter.

-          In Folder Location view, the folder paths begin with an incorrect drive letter.

-          In Folder Location view, the left-hand folder pane shows an incorrect drive letter.

-          When you try to edit, open, move, or share a file that has an incorrect drive letter, a dialog box appears with the message "Searching for missing file."

-          If you allow Photoshop Elements to continue searching for the missing file, then the program may appear to successfully reconnect the file; however, you still cannot edit or use the file, and its drive letter remains incorrect.

-          If you try to reconnect the file manually (by choosing File > Reconnect or by clicking Browse in the “Searching for missing file” dialog box), then the program returns the error message “The file already exists in the catalog” or “old file was not connected to new file”.

-          PSE lets you import a file on a network drive that’s already in the catalog, creating a duplicate thumbnail of that file in the catalog.

Some of these issues are described in Adobe tech note kb404560.

Volume Warnings

If there are problems with a volume, the -printVolumeTable option will print one or more of these warnings, described in detail below:

Two drives on this computer, drive1 and drive2, have the same volume serial number
Drive in catalog missing
Wrong serial number in catalog
Wrong drive letter in catalog
Wrong drive type in catalog
Duplicate drives in catalog
Files in the wrong volume

Currently, psedbtool doesn’t try to fix problems in the volume table, though it might in a later version.  If you encounter these warnings, read below for possible fixes and workarounds. Then if you’re still not sure what to do, post a message on the Adobe Photoshop Elements Technical Issues user-to-user forum, including the output from psedbtool.

Two drives on this computer, drive1 and drive2, have the same volume serial number vsn.  PSE will behave unpredictably.

PSE 6 and 7 use a drive’s “volume serial number” rather than drive letters to uniquely identify drives. Normally these numbers are different for each drive and CD/DVD, but if you use a cloning utility to copy drive contents, if you split up a RAID volume into separate drives, or if you use the SUBST command to create a virtual drive, you can end up with two drives with the same serial number.  In that case, PSE will get horribly confused and exhibit some of the symptoms described above.

To fix this, you can change one of the drive’s serial numbers.   Or you can reformat one of the drives, which will cause Windows to reassign a new serial number (be sure to back up any data on the drive first!).   For virtual drives, you’ll just have to delete them.

Drive in catalog missing

There is currently no drive on the computer with this volume’s drive letter or serial number.  This may have occurred because you’ve disconnected the drive from the computer, or it may be you’re trying to move the catalog and files to a new drive or computer.    See below for how to move files to new computers or drives.

Wrong serial number in catalog

The volume serial number recorded in the catalog for this volume doesn’t exist on this computer. PSE is instead accessing the volume using the drive letter that was associated with that volume when you first imported files from it. This may or may not be correct, depending on the situation.

If “Total files missing” for the volume is a large number, you’ve definitely got a problem. Perhaps you’ve moved your catalog to a new computer or reconfigured the drives in your current computer.  See below for how to move files to new computers or drives.

If “Total files missing” for the volume is 0, then there’s probably not a problem. Perhaps you’ve moved your catalog and files to a new drive or computer, preserving the drive letter and folder structure of your files from the old drive or computer.  For example, your files previously resided on drive D.  You’ve added a new drive to your computer, moved all the files from D to the new drive, disconnected the old drive, and called the new drive D.   This will continue to work fine, unless you later try to connect the old drive to the computer using a different drive letter.  At that point, PSE will start looking on the old drive (because its serial number matches the serial number in the catalog’s volume table), and PSE will show all the files as missing.

Wrong drive letter in catalog

PSE has found this volume on your computer using the volume serial number. But the drive letter recorded in the catalog’s volume table doesn’t match the current drive letter that Windows has associated with that volume serial number.  There are two situations that could cause this:

1.      Either you or Windows has changed the drive letter of the volume’s drive since you first imported files from that drive. PSE then fails to update its volume table with the new drive letter.  Often this bug is harmless, since PSE will identify the drive by its volume serial number, but it can cause some problems:

a.       Folder Location view will show the old (incorrect) drive letter in the left-hand folder pane. Not only can this be confusing, but in rare situations with PSE 6, it can lead you to move files to the wrong location and trip over another PSE bug that will permanently delete files (the bug is fixed in PSE 7).

b.      The File > Reconnect command can get confused and will refuse to reconnect to files that formerly resided on that volume, giving the message “old file was not connected to new file”.

c.       When writing files to a CD/DVD drive, PSE may get confused by the wrong drive letter in its volume table and refuse to write to the CD/DVD, reporting a permissions problem. (I haven’t been able to verify this.)

d.      It at least one instance, I’ve observed that the Move command gets very confused, moving files to one drive but recording another drive as the new location, resulting in them being marked as “missing” by PSE.

2.      You’ve added a new drive to your system, moved all your photos from the old to the new drive, assigned the old drive’s letter to the new drive, and left both drives connected to the computer.  PSE then fails to update its volume table with the new drive letters. PSE reports all of the files as missing, since it’s using the volume’s serial number to look on the old (wrong) drive.  If you try to reconnect the files, you get the message “old file was not connected to new file”.  See 1.d above for another possible symptom.

For either situation, the easiest fix is to reassign drive letters in Windows so that the drives have the original drive letters recorded in the catalog.  See this Microsoft knowledgebase article (the article is specific to Windows XP, though the procedure on Vista is very similar).   Then, for situation 2 in which you’ve moved files, you can reconnect the files using File > Reconnect.

Wrong drive type in catalog

PSE has recorded a type (builtin, removable, readonly) for this volume’s drive that doesn’t match the type reported by Windows for the same volume serial number. I’m not sure how this occurs, but it’s probably a bug in PSE. 

If PSE mistakenly thinks a hard drive is “removable”, then the effects of this bug are minor. In the left-hand folder pane of Folder Location view, PSE will show the drive and all its folders under “Offline Media”, rather than under the drive’s drive letter. “Offline Media” is a misnomer – it should really be called “Removable Media”, since the files aren’t necessarily offline (disconnected from the computer).

Duplicate drives in catalog

There are two drives in the volume table with the same drive letter or, in the case of network drives, with the same UNC path.

When upgrading to a new version of PSE, bugs in the catalog conversion apparently can add duplicate drives to the catalog. 

Bugs in how PSE handles network drives can add duplicate network volumes to the catalog that differ only in case (e.g. \\myserver\share and \\MYSERVER\share). This can result from a specific sequence of reconnecting files residing on the network drive and then importing more files from the drive using drag-and-drop to the Organizer.  One symptom is that PSE will let you import a file from a network drive a second time, creating a duplicate thumbnail. There may be other, more severe symptoms – I’m not sure.

In either case, the only known workaround is to use a SQLite tool to modify the catalog database file (requiring technical expertise).

Files in the wrong volume

The indicated volume has cataloged files that are in the catalog folder or subfolders, and the volume is not marked as “database relative”.  For example, this warning will be triggered if the catalog folder is c:\catalogs\mycatalog, the catalog contains a photo c:\catalogs\mycatalog\pics\p.jpg, and the photo’s volume is not marked as “database relative”.

In PSE 7 and later, this can cause at least two symptoms: 1) For photos in the catalog folder or in subfolders, the Editor won’t be able to save in version sets; and 2) Using Folder Location to move the folders will fail, leaving disconnected files. This situation can arise if you’ve restored a catalog from backup in PSE 6 and then converted the catalog to a later version. 

If you get this warning and you’re running PSE 6, then beware that when you convert to PSE 7, you’ll trigger the symptoms above.

The workaround is to create a new folder somewhere else such that it doesn’t contain the photos. Continuing the example above, use Windows Explorer to create c:\catalogs\mycatalog2. Move catalog.pse7db, thumb.5.cache, syncdb, and projects from the old catalog folder into the new one.  Then double-click catalog.pse7db to open it in the Organizer from its new location.

Change a Drive’s Volume Serial Number

PSE uses the volume serial number as the primary mechanism for identifying drives, and only if it can’t find a drive with the desired number will it use drive letters.  If you have two drives with the same number, or if try to assign a new drive letter to a drive that used to be a C drive, it may be necessary to change the volume serial number of a drive.   Here’s how to do that:

1.      Download the “volumeid.exe” utility from Microsoft and place it in c:\windows\system32.

2.      Start a command prompt.  In Vista, run the command prompt as administrator by doing Start > All Programs > Accessories, right-clicking Command Prompt, and selecting Run As Administrator.  On XP, run the command prompt from an account that is a member of Administrators (note that this won’t work on Vista). 

3.      Type the following command:

volumeid letter: xxxx-xxxx

where letter is the drive you want to change, and xxxx-xxxx is the new serial number. You can pick any serial number that’s not used by any of your drives.

4.      Reboot.

5.      Verify that the drive has the new serial number by typing this command at the command prompt:

vol letter:

Moving Files to New Drives and Computers

When you move files to a new location or computer using a tool other than PSE (such as Windows Explorer or a third-party backup utility), PSE can get very confused, reporting the files as missing and causing psedbtool to report volume warnings.  If your catalog’s volume table has inconsistencies caused by PSE bugs, the File > Reconnect command may not be able to reconnect the missing files.

In general, it’s best to use PSE to move folders and files, so that PSE can keep its catalog properly updated with the current locations of files.   Here are some options for moving files:

1.      Move folders using PSE’s Display > Folder Location view. In the left-hand folder pane, you can drag and drop folders just as you can in Windows Explorer.  PSE will only expose folders in the folder pane if they contain files imported into the catalog, so you may have to import a dummy photo from a folder to get it to show up.

If you’ve already moved files using another tool, often the easiest thing to do is to use that tool to move them back to their original location, and then use PSE to move them to the desired new locations.  If you’ve moved a large number of files, you may find it handy to use psedbtool’s -csv option to extract the original locations of the missing files into Excel.

2.      If you’re moving to a new computer, use PSE’s Backup/Restore command.  See Adobe tech note kb402894 for details.  Note that some people have reported that Backup/Restore won’t restore slide shows.

3.      Use the File > Reconnect command to reconnect files that you’ve moved using another tool (such as Windows Explorer).   Before attempting this, make sure the volume table has no warnings shown by psedbtool.   Often the File > Reconnect command works quickly and smoothly.   But if you have any problems or its going too slowly, make sure you’ve addressed any volume-table warnings, and then read this FAQ for tips on how to reconnect large numbers of files.

Extracting Catalog Contents

With psedbtool, you can extract most of the contents of a catalog into a set of text CSV (comma-separated values) files that can be read by Excel and other tools:

c:\psedbtool\psedbtool -csv file

This generates four CSV files:

file­.csv

Contains one row per file in the catalog. The columns include the file’s full filename, whether it’s missing or offline, and all of the attributes of the file recorded in the catalog.  Attributes include the file’s date/time, keyword tags, caption, notes, star rating, GPS/map location, and many others.   See the option ‑newlineEscape for the handling of newlines in the notes attribute.

If the file is in a version set, the column Version Set Top gives the filename that’s at the top of the version set and the column Version Set Position gives this file’s position in the set (1 = top of the set).   Similarly, if a file is in a stack, the column Stack Top gives the filename of the top of the stack and the column Stack Position gives this file’s position in the stack.

All of the keyword tags and categories applied to the file appear in the column Tags, with tags and categories separated by the “^” character. All of the albums containing the file appear in the column Albums, with each album separated by the “^” character. See the option -newlineEscape for changing that separator.

file­_tags.csv

Contains one row for each keyword tag applied to each filename.  The Tag column gives the simple name of the tag, e.g. “San Francisco”, and the Qualified Tag column gives the full hierarchical name of the tag, e.g. “Places|United States|California|San Francisco”.

file­_albums.csv

Contains one row for each file in each album.   The Album column gives the simple name of the album, the Qualified Album column gives the hierarchical name of the album, e.g. “Holidays|Christmas”, and Position gives the position of the file within the album. Smart albums are not included.

file_tag_defs.csv

Contains one row per keyword tag and category/subcategory. Columns include the keyword tag’s name, qualified name, parent’s name, GPS/map location, and notes. See the option newlineEscape for the handling of newlines in the notes attribute.

file_album_defs.csv

Contains one row per album and album group.   Columns include the album’s name, qualified name, and parent album group. Smart albums are not included. 

If your tags, captions, or notes contain other than ANSI characters, use the -unicode option to write them correctly to the CSV files.

See the option -tagSeparator for changing the “|” character used in qualified keyword tag and album names.

To load the CSV files into Excel, don’t open the files directly with the Open command or by double-clicking on the CSV file – Excel will incorrectly interpret some of the columns as Excel-format dates or floating-point numbers.

Instead, create a new worksheet and use the Text Import Wizard to load the file.  In Excel 2007, use the Data > From Text command to open the file and start the wizard; in Excel 2003, use the Data > Import External Data > Import Data to open the file and start the wizard.   In the wizard, select Delimited and select comma as the Delimiter.  For “Column data format”, select Text, and in the “Data preview”, select all of the columns by clicking on the first column, scrolling to the last column, and shift-clicking the last column.

Writing File Metadata

The PSE command File > Write Keyword Tag and Properties Info attempts to write a file’s metadata with the date/time, keyword tags, caption, notes, star rating, and GPS/map location recorded in the catalog. However, the command is quite buggy and has a number of limitations.  This makes it hard to use other tools with PSE or migrate your files from PSE into another program.

Use psedbtool’s -writeMetadata option to write the catalog metadata into the files more reliably:

c:\psedbtool\psedbtool -writeMetadata

To preview the changes psedbtool would make without actually making them, use the ‑verifyMetadata option:

            c:\psedbtool\psedbtool -verifyMetadata

Before modifying a given file, psedbtool makes a backup copy of the file named file­_old (or file_old1, file_old2, etc.).   (The option -noBackup suppresses these backups.)

By default, psedbtool writes the metadata for raw files whose formats it recognizes into so-called “XMP sidecars”.   For example, for the Nikon raw file p05.nef, psedbtool would write the metadata into p05.xmp, creating it if necessary.  Use the option ‑noSideCarsForRaw to write the metadata into the raw files themselves rather than sidecars.

For files that don’t support metadata (e.g. BMP) and for files whose formats aren’t understood by psedbtool, psedbtool always writes sidecars.

By default, psedbtool writes the simple keyword tag name (e.g. “San Francisco”) into the metadata.  Use the option -qualifiedKeywords to write the fully qualified keyword tag name instead (e.g. “Places|California|San Francisco”). This is useful for interoperating with other programs that understand hierarchical keyword tags, such as Lightroom or Windows Live Photo Gallery.  For the latter, use the -tagSeparator option to set the separator to “/” instead of “|”.  (There is no industry standard for hierarchical keyword tags.)

psedbtool uses the ExifTool library to manipulate file metadata. ExifTool is widely recognized as one of the most robust tools for manipulating metadata. See the ExifTool documentation for the file formats it recognizes.

The following metadata file tags are used to store catalog metadata:

Catalog Metadata

Metadata File Tags

Date/time

EXIF:DateTimeOriginal, XMP:DateTimeOriginal

Keyword tags

IPTC:Keywords, XMP:Subject

Caption

IPTC:Caption-Abstract, EXIF:ImageDescription, XMP:Description

Notes

XMP:Notes

Star Rating

EXIF:Rating, XMP:Rating

GPS/map location

EXIF:Longitude, EXIF:LongitdueRef,

EXIF:Latitude, EXIF:LatitudeRef,

XMP:Longitude, XMP:Latitude

 

Some of the problems with PSE’s File > Write Keyword Tag and Properties Info command include:

-          When writing keyword tags, it never deletes old keywords from the file – it just appends new ones.  So if you rename a keyword or remove a keyword from a file, that change won’t get written to the file’s metadata.

-          For date/times before 2007, it uses the incorrect rules for US Daylight Savings Time, resulting in times that are off by an hour for a few weeks in the spring and fall.

-          When writing EXIF:DateTimeOriginal, sometimes it writes the time in UTC rather than in local time, causing other tools to show times that are off by many hours.

-          It doesn’t always write IPTC:Caption-Abstract, IPTC:Keywords, or EXIF:Rating, causing older tools that don’t understand the XMP versions of those tags to get the wrong values for captions, keywords, and ratings.

-          It often doesn’t update sidecars for Nikon NEF raw files.

-          It doesn’t update PDF files.

-          It doesn’t write sidecars for formats like BMP that don’t support metadata.

-          It doesn’t write GPS/map locations to some formats, including TIFFs produced by Nikon and Epson scanners.

Command Syntax and Options

psedbtool [options] [catalogfile]

catalogfile

By default, psedbtool reads the current PSE catalog, the one that the Organizer opens when it starts. You can specify a different catalog by providing the full file path of the file (catalog.psedb for PSE 6, catalog.pse7db for PSE 7, or catalog.pse8db for PSE 8).  See Adobe tech note kb404990 for possible locations of your catalog. Be sure to enclose the file path in double quotes if it contains spaces (as it usually does).

-help

Prints a summary of psedbtool options.

-printVolumeTable

Prints the catalog volume table and current Windows drives. See Detecting Problems with File Locations for details.

-csv file

Extracts the catalog contents into the files file.csv, file_tags.csv, file_albums.csv, file_tag_defs.csv, and file_album_defs.csv. See Extracting Catalog Contents for details.

-unicode

Writes the csv files in Windows Unicode format (16-bit little-endian). Use this format if your filenames or metadata use characters other than ANSI (US ASCII). 

-noUnicode    (default)

Writes the csv files as 8-bit ANSI text.

-tagSeparator s    (defaults to “|_”)

The first character of s separates hierarchical names in qualified keyword tag and album names in the CSV files generated by the -csv option and in file metadata when the ‑qualifiedKeywords option is selected. The second character of s replaces occurrences of the first character.

-newlineEscape s    (defaults to “^=”)

Newlines in keyword tag notes and album notes are replaced by the first character of s. The second character of s replaces occurrences of the first character.

-verifyMetadata

Compares the catalog metadata with the metadata stored in files and prints the differences, showing the changes that would be made by the option ‑writeMetadata. See Writing File Metadata for details.

-writeMetadata

Updates the files’ metadata with metadata from the catalog. See Writing File Metadata for details.

-backup    (default)

The original version of a file modified by the -writeMetadata option will be renamed as file­_old, file­_old1, etc.

-noBackup   

Original versions of files modified by the -writeMetadata option won’t be saved. Be sure you have your own backups!

-sideCarsForRaw    (default)

Writes metadata to XMP sidecars for raw files whose format is recognized by psedbtool.

-noSideCarsForRaw

Writes metadata into the raw file itself rather than a sidecar if its format is recognized by psedbtool.

-qualifiedKeywords

Writes fully qualified keyword tags into file metadata, e.g. “Places|California|San Francisco” instead of “San Francisco”.

-ignoreMinorErrors

Allows writing metadata to a file even if there are minor errors in the file’s existing metadata.

-noIgnoreMinorErrors    (default)

Prevents writing of metadata to a file if there are minor errors in the file’s existing metadata.

-level n    (default 2)

n = 1 causes least verbose output, n = 3 the most verbose.

Bugs and Suggestions

Please email suspected bugs, suggestions, or any other feedback to ellis-psedbtool at johnrellis.com.

Version History

Version 1.01 – 2008/11/03

-          Initial release.

Version 1.02 – 2008/11/07

-          Minor changes to the documentation.

Version 1.03 – 2008/12/27

-          Fixed bug with file times whose seconds are larger than 59.5, e.g. 59.51.

Version 1.04 – 2009/03/02

-          Added run.bat to make it easy for people not experienced with the command prompt to run it.

Version 1.05 – 2009/05/10

-          Unicode characters in captions and notes are correctly written to files’ metadata. Note that PSE will write multi-byte UTF-8 Unicode characters into EXIF and IPTC fields, in violation of the industry standards.  Psedbtool does likewise.  It is likely that other tools will not correctly recognize those Unicode characters.

-          Using the -unicode option with -csv causes the CSV files to be written in Unicode rather than 8-bit ANSI, allowing full Unicode characters in filenames, tags, captions, and notes to be written.

-          Filenames containing characters in the Latin-1 Supplement (decimal value between 128 and 255) are now correctly handled. Previously, they were considered missing from the catalog. The Latin-1 Supplement provides the most common additional characters for European languages (e.g. German umlauts).  Unfortunately, full Unicode filenames still aren’t handled properly. psedbtool is implemented using Perl, which doesn’t fully support Unicode filenames in a platform-independent way, and the Exiftool library underlying psedbtool only uses platform-independent Perl features.

-          Psedbtool no longer crashes with “out of memory” due to corrupted MakerNotes in files.

-          Using the -nobackup option with -writeMetadata prevents the original versions of files being renamed as file_old.

-          The –ignoreMinorErrors option allows writing metadata to a file even if there are minor errors in the file’s existing metadata.

-          Eliminated -181/-91 from appearing as the longitude/latitude of tags that hadn’t been placed on the map.

-          Added a check for network volumes with the same network paths (another bug in PSE’s volume handling).

-          Packaged as a Winzip .exe self-extracting to c:\psedbtool, making installation easier for novices.

Version 1.06 – 2009/05/21

-          Fixed minor bug with writing of CSV files where the Unicode Byte Order Marker was getting written even when -unicode was not given. The CSV files were still readable by Excel, but some other programs might have gotten confused.

Version 1.07 – 2009/08/14

-          Suppressed repeated “Windows – No Disk” dialog boxes for CD/DVD and floppy drives not currently containing a disk.

-          Newlines in captions are correctly escaped in the CSV file.

-          Minor changes to the documentation.

-          Changed the warning “Duplicate network drives in catalog” to “Duplicate drives in catalog” (the bug with duplicate drives can occur with types other than network drives).

-          Added the check for “Files in the wrong volume”.

Version 1.08 – 2009/10/21

-          Now looks for PSE 8 catalogs first.

-          Minor changes to the documentation to indicate compatibility with PSE 8.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Copyright 2008 John R. Ellis. All rights reserved.