How to export a text list of items in a Lightroom collection?

Question

I use Lightroom for managing my photos and a custom piece of software to run my website. In order to have my collections published, I need to have a file with the names of the items in the collection.

Up to a few weeks ago I used a paid plugin from the past, that was able to create a full gallery using Adobe Flash. I quickly dropped the use of Flash in my galleries, but I have used the plugin until recently because it produced an XML file with the list of items. Unfortunately, the plugin doesn't work any longer with Lightroom 6.

I'm not aware of any other plugin doing the same (XML is not a requirement: any text format is ok; I don't ever need the path of the items, just their name + copy name).

To be clear: I just need a text file with the list of items - for what concerns photos to be published, I use the standard features of Lightroom.

PS I need that the items are exported in the same order as they appear in the collection.

Answer 1

You might be able to exploit the fact that a LR catalog is actually a SQLite database. I've written ruby scripts in the past to generate statistics on my photos. The sort of data you're after probably isn't much more difficult to produce.

Mac OS ships with the sqlite command line tools. If command line interfaces aren't your thing, there are several different SQLite GUIs in the Mac App Store.

See this for a (older, but still valid) quick intro to pulling the data.

Edit

I cracked open my LR CC catalog and mocked up a quick test of my theory. I ended up with the following query:

SELECT lf.idx_filename
FROM AgLibraryCollection AS c,
  AgLibraryCollectionImage AS ci,
  Adobe_images AS i,
  AgLibraryFile AS lf

WHERE c.id_local = ci.collection
  AND i.id_local = ci.image
  AND lf.id_local = i.rootFile
  AND c.name = 'Collection Name'

The easiest way to execute this is to install SQLite Free - Datum from the Mac App store, open your Lightroom catalog (make sure LR is closed) and stick the query in the SQL tab. A more complex (but easier to repeat) method would be to stick the query in a text file, and run the following in Terminal:

sqlite3 /path/to/lightroom_catalog.lrcat < query.txt

Answer 2

As an update on the original answer, here is a modified sqlite3 query to get both the absolute path and the file name of the images in the collection:

SELECT lrf.absolutePath, lfo.pathFromRoot, lf.idx_filename
FROM AgLibraryCollection AS c,
  AgLibraryCollectionImage AS ci,
  Adobe_images AS i,
  AgLibraryFile AS lf,
  AgLibraryFolder AS lfo,
  AgLibraryRootFolder AS lrf

WHERE c.id_local = ci.collection
  AND i.id_local = ci.image
  AND lf.id_local = i.rootFile
  AND c.name = 'Collection Name'
  AND lfo.id_local = lf.folder
  AND lrf.id_local = lfo.rootFolder

Then remove the | separators from the output to get the full path to the images in the collection.

Answer 3

I'm posting the solution I developed starting from the hint by speshak. In the end I did it with a bash script. It exports all the web galleries in my catalog and prepares some files according to my CMS. There's some stuff that is specific for my needs, but I think the script could be useful to others anyway.

There's still a problem with sorting. I was unable to sort the images as they are in the collection - probably because there's no info in the database as an explicit index, and Lightroom perhaps sorts results in function of the properties of the collection. I still used sorting by file name, which works for all my collections, but one.

#!/bin/bash

#
# Exports web galleries from a Lightroom 6.x database and prepares files with gallery contents.
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
# an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations under the License.
#
# $Id: ExportGalleries.sh,v ce1c71b3125a 2015/07/04 19:38:11 fabrizio $
#

TARGET_PATH="$HOME/Personal/WebSites/StoppingDown.net/structure"
CATALOG="$HOME/Personal/Photography/Lightroom/Fabrizio Lightroom Catalog/Fabrizio Lightroom Catalog.lrcat"



#
# Input         $1      a string
# Return                the string, trimmed
#
function trim
        {
                echo "$1" | sed 's/^ *//' | sed 's/ *$//'
        }

#
# Input         $1      a SQL query
# Return                the result of the query separated by spaces
#
function query
        {
                trim "`sqlite3 "$CATALOG" "$1" | tr '\n' ' '`"
        }

#
# Input         $1      the id of a collection
# Return                the name of the collection
#
function getCollectionName
        {
                query "SELECT name FROM AgLibraryCollection WHERE AgLibraryCollection.id_local = $1;"
        }

#
# Input         $1      the id of a collection
# Return                the id of the parent collection
#
function getCollectionParent
        {
                if [ "$1" != "" ] ; then
                        query "SELECT parent FROM AgLibraryCollection WHERE AgLibraryCollection.id_local = $1;"
                        fi
        }

#
# Input         $1      the id of a collection
# Return                the file name + copy name of the images contained in the collection
#
# FIXME: problems with sorting: ci.positionInCollection is null for many items; i.captureTime doesn't work as expected.
#
function getImageNamesInCollection
        {
                echo `query "SELECT lf.baseName,i.copyName FROM AgLibraryCollection AS c,
                                                                AgLibraryCollectionImage AS ci,
                                                                Adobe_images AS i,
                                                                AgLibraryFile AS lf
                                                           WHERE c.id_local = ci.collection
                                                                AND i.id_local = ci.image
                                                                AND lf.id_local = i.rootFile
                                                                AND c.id_local = $1
                                                           ORDER BY lf.baseName;"` | sed 's/|//g'
##                                                           ORDER BY i.captureTime;"` | sed 's/|//g'
##                                                           ORDER BY ci.positionInCollection;"` | sed 's/|//g'
        }

#
# Return                the ids of the collections that are a web gallery, separated by spaces
#
function getGalleryIds
        {
                query "SELECT DISTINCT id_local FROM AgLibraryCollection WHERE creationId='com.adobe.ag.webGallery';"
        }

#
# Input         $1      the id of a collection
#
function exportCollection
        {
                local PARENT=$(getCollectionParent $1)
                local GRAND_PARENT=$(getCollectionParent $PARENT)
                local GRAND_GRAND_PARENT=$(getCollectionParent $GRAND_PARENT)
                local NAME="$(getCollectionName $1)"
                local GALLERY_PATH=""
                local EXPOSED_URI=""

                # Diary
                if [ "$GRAND_GRAND_PARENT" == "2918721" ] ; then
                        local YEAR=$(getCollectionName $GRAND_PARENT)
                        local MONTH=`echo "$(getCollectionName $PARENT)" | sed -e 's/\..*$//g'`
                        local DAY=`echo "$(getCollectionName $1)" | sed -e 's/\..*$//g'`
                        local PATH_NAME="$YEAR/$MONTH/$DAY"
                        GALLERY_PATH="$TARGET_PATH/Diary/$PATH_NAME"

                # Lenses
                elif [ "$GRAND_PARENT" == "9495573" ] ; then
                        local LENS_NAME=$(getCollectionName $PARENT)
                        GALLERY_PATH=`echo "$TARGET_PATH/Themes/Lens/$LENS_NAME/$NAME" | sed 's/ /+/g'`

                # Visual Colour Check
                elif [ "$NAME" == "Visual Color Check" ]; then
                        GALLERY_PATH="$TARGET_PATH/Themes/VisualColourCheck"

                # Splash Slideshow
                elif [ "$NAME" == "Splash Slideshow" ]; then
                        GALLERY_PATH="$TARGET_PATH"
                        fi

                if [ "$GALLERY_PATH" == "" ] ; then
                        echo "Ignoring $GRAND_GRAND_PARENT/$GRAND_PARENT/$PARENT ($NAME)..."
                else
                        local IMAGES="$GALLERY_PATH/images.xml"
                        echo "Exporting $IMAGES ($NAME / $1)..."

                        if [ ! -d "$GALLERY_PATH" ]; then
                                echo "   Creating new gallery..."
                                mkdir -p $GALLERY_PATH
                                local DATE=`date -u "+%Y-%m-%dT%H:%M:%S.000+00:00"`
                                cat << EOF > "$GALLERY_PATH/Properties.xml"
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<properties version="1.0">
    <property name="creationDateTime">
        <value>$DATE</value>
    </property>
    <property name="latestModificationDateTime">
        <value>$DATE</value>
    </property>
    <property name="exposedUri">
        <value>$EXPOSED_URI</value>
    </property>
</properties>
EOF
                                fi

                        echo '<?xml version="1.0" encoding="UTF-8"?>'                  > $IMAGES
                        echo '<gallery>'                                              >> $IMAGES
                        echo '    <album>'                                            >> $IMAGES

                        for i in $(getImageNamesInCollection $1); do
                                echo "        <img src=\"$i.jpg\"/>"                  >> $IMAGES
                                done

                        echo '    </album>'                                           >> $IMAGES
                        echo '</gallery>'                                             >> $IMAGES
                        fi
        }

function exportGalleries
        {
                for i in $(getGalleryIds); do
                        exportCollection $i
                        done
        }

exportGalleries

Answer 4

This other approach to avoid corruption of the database checks the existence of a lock file.

LOCK_FILE="$CATALOG.lock"

if [ -f "$LOCK_FILE" ]; then
    >&2 echo "Please quit Lightroom before running this script."
    exit 1
    fi

Answer 5

I don't know of a plugin that does this, but on a Mac I would do it as follows (maybe a Windows machine does the same, don't know):

1. Open TextEdit.app in addition to Lightroom
2. Select all photos in the collection
3. Drag them to the TextEdit.app window, now you have the pathname of the dragged photos in a text file.
4. Do some search + replace to get rid of the path and only keep the filename

A regex to match the path but not the filename would be ^/.*/.

Note: Using MacOS High Sierra with TextEdit.app when doing this now copies the images to TextEdit instead of the paths.