After HDD crash: assigning recovered photos to Lightroom catalog entries

I wrote this entry after I read a question in the Adobe Lightroom forum. The main problem is, that after a HDD crash the names of the recovered files (e.g. file0001.jpg, file0002.cr2,…) do not match the entries in the Lightroom database. I found an easy way to reassign the Lightroom database values to the file names.

The basic idea

The basic idea is:

Rename all your recovered files to some unique name based on exif data. I suggest something like: 20140725-101324
Export all filenames and needed exif data from the Lightroom into a text files (csv)
Use some tool (e.g. OpenOffice) to create a script file that renames and moves the recovered files to the original Lightroom names and location

There are some pitfalls in this process, but the basic idea can easily adapted to your needs.

Needed Tools

To follow this tip, you need additional software:

exiftool by Phil Harvey
ListView for Lightroom by John Beardsworth
OpenOffice (or an other spread sheet program)

The situation

Here are two screenshots: one of Lightroom with “missing photo” icons and

one from my recovery folder.

As you can see, all files in the recovery folder have arbitrary names are distributed over different folders.

Preparation

Make a backup of your exiting Lightroom catalog and a backup copy off all recovered images. Do the following steps only on these backups! NEVER WORK ON YOUR RECOVERED DRIVE YOU MIGHT LOOSE ALL YOUR FILES.

Merging the files

The renaming process is much easier when all files are stored in one directory. Therefore move all recovered file into one directory. I expect that all files are stored in one single directory called d:\hdd_recover without any subdirectories.

Renaming the files

Download and install exiftool.

Open a command prompt and go to topmost folder where all your recovered files are stored. In my case this is D:\hdd_recover. Enter the following line in your command line

exiftool "-FileName<CreateDate" -d "%Y%m%d-%H%M%S.%%e" *

This command will rename all files (photos and movies) based on the data when the file was taken. The result may look like this:

Error: 'restore02/20110325-164322.jpg' already exists - restore02/file6114.jpg 2 directories scanned 32 image files updated 1 files weren't updated due to errors

In my case, the same file was recovered twice. We will discuss errors at the end of this article.

Exporting a file list from Lightroom

Download and install the ListView Plugin.

Open the catalog in grid view and select all your missing files (if possible). Open the ListView Plugin using “Smaller Thumbnails,…” menu entry:

Export the ListView using the “spreadsheet” icons. I use OpenOffice but any other spreadsheet application should work. I tried this process with more than 6000 images and it took less than two minutes.

Creating two batch files

The spreadsheet icon directly opens the the application. Save the spread sheet as “List View.ods” first.

LRecoverMove

For the first script we only need two columns of the export – the “Path” and “Date Time”. Delete all other columns in the first step:

Next step is to change the order of the columns (“Date Time” first, “Path” second) using copy & paste. Now we modify the format of the date (pattern YYYYMMDD-HHMMSS):

Now you have to save the created spreadsheet as a text file (.csv) (“File->Save as”). Activate the “Save cell content as show” checkbox to save your custom date format in the text file and accept all other options.

Close the file (ignore save message) and open the exported .csv file in OpenOffice (confirm all questions on import).

Now, we are ready to build our script. Go to the first empty column (C3 in my case) and enter the following formula (on mac replace move with mv):

=CONCATENATE("move """;A3;".jpg"" """;B3;"""")

Please, take care of the number of quotes and spaces! If you want a quote in string in OpenOffice you have to write two quotes!

May be your ‘“’ will be replace by some other quotes. In OpenOffice you can overwrite this setting in the “Tools->AutoCorrect Options->Custom Quotes” settings by removing the check mark before the “Replace” option.

Copy the formula to the remaining empty rows.

Save the document again as text file, close the document and reopen it. Copy the contents of column C into a text editor (using copy & paste) (This took a long time on my machine with OpenOffice 3.2). Here is an example:

move "20110325-162800.jpg" "C:\Users\THG\Desktop\110325_Kindercafe\20140716_220709.jpg" move "20110325-163753.jpg" "C:\Users\THG\Desktop\110325_Kindercafe\20110325_163753.jpg" move "20110325-173555.jpg" "C:\Users\THG\Desktop\110325_Kindercafe\20110325_173555.jpg"

As a Windows user write

@echo off

to the first line and save the file as LRecoverMove.bat.

Mac users write

#!/bin/sh

to the first line and save the file as LRecoverMove.sh.

You might want to change the target location of the move process if the target is your broken drive! Use “Find & Replace” in your text editor to change the target location (e.g. replace “c:\Users\THG\Desktop” with “D:\rescued”). Save the file again.

This is the first script. It will move all recovered files back to there original place. But there is still one problem. The move process will fail, if the target directory does not exists. Therefore we have to create the target directories first!

LRecoverHierarchy

This script will be created in the same way as LRecoverMove script. If not already open, open the exported .csv file again. Go to the first empty column (D3) and enter for windows the formula:

=LEFT(B4;SEARCH("\\[^\\]*$"; B4)-1)

and for Mac:

=LEFT(B4;SEARCH("/[^/]*$"; B4)-1)

The result should be the path to the file without the file name itself (e.g. “C:\Users\THG\Desktop\110325_Kindercafe\20110325_173836.jpg” becomes “C:\Users\THG\Desktop\110325_Kindercafe”). Expand this formula to all other D rows.

Finally we build the script in E3 with the formula:

=CONCATENATE("md """;D3;"""")

for Windows and

=CONCATENATE("mkdir –p """;D3;"""")

for Mac. Expand this formula to all other E rows. (Ignore the fact that there are a lot of duplicate entries.)

Save the document again as text file, close the document and reopen it. Copy the contents of column E into a text editor (using copy & paste) (This took a long time on my machine with OpenOffice 3.2). As a Windows user write

@echo off

to the first line and save the file as LRecoverHierarchy.bat.

Mac users write

#!/bin/sh

to the first line and save the file as LRecoverHierarchy.sh.

If you changed the location of the target in the LRecoverMove script, you must also change it in this script. Use “Find & Replace” in your text editor to change the target location (e.g. replace “c:\Users\THG\Desktop” with “D:\rescued”). Save the file again.

Starting the recovery

Warning! We are moving the data back to there original place. If this drive is broken, we overwrite data which my be used by some other recovery program to find additional data! If the file system structure is not intact on this drive, we even might damage the file system completely!

Open a command prompt and go to topmost folder where all your recovered files are stored. In my case this is D:\hdd_recover. Enter the following line in your command line on Windows

path_to_your_script\LRecoverHierarchy.bat

or on Mac

sh path_to_your_script\LRecoverHierarchy.sh

This will create the folder hierarchy on your hard disk as it was before your crash. This will not delete any existing directory.

Now start the LRecoverMove script. Enter the following line in your command line on Windows

path_to_your_script\LRecoverMove.bat

or on Mac

sh path_to_your_script\LRecoverMove.sh

This will move the files back to there original place an rename them.

Here is a screenshot of my Lightroom after I started the script:

As you can see, I was able to recover all of my images!

If you change the target location in your scripts, you have to tell Lightroom where Lightroom finds the new folders. Select the folder and open the context menu. Select “Find Missing Folder…” and change to location to your recovered target (e.g. “D:\rescued”). Your photos and all edits are online again!

Clean up

After execution of the LRecoverMove script only files not in the Lightroom catalog or not correctly renamed are left over in your recovery directory. All other files are move to there original location.

You should keep these files and assign them by hand or find the reason why they can not be assigned. The next section my help you to find a way to also reassign these files.

Remarks

Why these tools?

After I first read the question in the Adobe forum I had a basic idea how to solve the problems. This idea include using database editor and some “cool” sql statements. After that, some awk / sed magic and everything is done! You can’t follow me? Really? You’re right! For most users such a solution would not solve the problem.

Instead of using a sql statement I use the great “List View” plugin. Sorry, but I did not find a free plugin that allows me to export the needed data.

Exiftool is my selection for renaming because I use it since years. There are other tools that support rename of files based on there exif data. Some may contain an GUI, but only a view are available for Mac and Windows at the same time.

I picked up OpenOffice as a tool because I found an old Version (3.2) on my PC. Latest version of OpenOffice or LibreOffice should work, too. OpenOffice is available on Mac and Windows. I did not try Mac Numbers, but it may also work. Of course, there are a number of other tools that allow you to concatenate strings.

Pitfalls

I can not image all the things that might be a problem, but here is a small list and possible solutions:

Multiple pictures taken in the same second

Exiftool writes an error like this:

Error: 'restore02/20110325-164322.jpg' already exists - restore02/file6114.jpg 2 directories scanned 32 image files updated 1 files weren't updated due to errors

There are a number of reasons why this error my occur:

Your recovery program found the same image multiple times on your hard disk and recovered all of them. Open all of them, some may be corrupt (but the preview show in the file browser is all right)!
Your catalog contains images from the same event taken with different cameras. Therefore some images may taken in the same second.
You are a fan of HDR, focus stacking, panorama shooting or sports and therefore you have taken the “same” images in the same second.

Not all of these problems can be fixed automatically. Here a some thoughts about possible solutions:

This can not be fixed automatically. You have to check all files by hand in a photo editing program. Here is a basic idea what you can do: Create a new temporary Lightroom catalog. Import all rescued files (the renamed and the unchanged ones) and create 1-to-1 previews. Lightroom will inform you, if files are broken. Now create a filter on the file name (in my case: select all file starting with file) to select all files that could not be renamed. Mark them red. Clear the filter and sort in grid view on “Capture time”. Now you can easily find the duplicate images (red) and one other. Compare the images and delete the unwanted images (from disk!).
You might want to add an additional parameter to the renaming process, that identifies the camera (e.g. model or serial number). Call exiftool like this:
exiftool "-FileName<${CreateDate}-${model}.%e" -d "%Y%m%d-%H%M%S" *
or
exiftool "-FileName<${CreateDate}-${serial}.%e" -d "%Y%m%d-%H%M%S" *
Of course, you must add this information also to the “List View” and take care of it when you create the LRecoverMove script.
The solution to this, is the same as for 2. Add some additional data to the renaming process, this command will add Date, Aperture, Exposure, Focal Length, ISO and the distance to the subject to your file names:
exiftool "-FileName<${CreateDate}-${Fnumber}-${Exposure}-${FocalLength}-${Iso}-${SubjectDistance}.%e" –d "%Y%m%d-%H%M%S" *
This is an extreme example, you should use less fields because not all supported on all cameras.
Remark: You can not use the shutter speed because it contains a dash (“/”) which will produce incorrect filenames!

RAW + JPEG

If you shoot RAW and JPEG and import both in Lightroom only the RAW is “visible” in the catalog (depending on your preferences). Using “List View” will only export a list with the RAW files and the LRecoverMove script will only move the RAW files.

Solution is simple. Copy the LRecoverMove script, edit the copy and replace your RAW extension (e.g. CR2, NEF) with (.JPG). Save the copy and execute it. This will move the JPEG files to the same location as the corresponding RAW files.

Time differences

The creation date in the Lightroom catalog my differ from the CreateDate exif tag in the file. This is often true for raw files because Lightroom by default does not write back adjustments to to raw file. In Lightroom the “DateTimeOriginal” is the modified Lightroom time and DateTime the exif time. May be you need to use the “DateTimeOriginal” Lightroom time for some files and create an adapted LRecoverMove script.

For some of my images I found out that Lightroom used the incorrect time because one needed tag was missing. Lightroom used the creation time or modified time of the file (system) instead of exif data. I do not know how to solve this problem.

Final discussion

I was able to recover all of my files in my test scenario. I real life you have to cope with some problems based on the recovery process. I tried to discuss some of them. If you have problems with some files you can still perform the recovery process for the rest of the files. You can try to rescue the rest of the files when you need them.

Mein Lightroom Tag - Der Blog rund um Lightroom und Bildbearbeitung

Freitag, 1. August 2014