5 Replies Latest reply on Dec 18, 2016 9:07 AM by dariuszk32546812

    Can catalog details such as keywords and exif data be exported to Microsoft access database or Excel?

    llbeck02

      I Have a large database of photos and key data that was created with discontinued Ulead Photoimpact Album software. An advantage of PI Album databases is the ability to export and import to MS Access or Excel.  If Lightroom catalog has the ability to move data to and from the more common databases it will help me greatly.  I don't want to re enter the thousands of entries.

        • 2. Re: Can catalog details such as keywords and exif data be exported to Microsoft access database or Excel?
          llbeck02 Level 1

          GREAT!  That's enough to convince me to. buy the program.

           

          will the paid version of the plugin allow importing tne data back to Lightroom or does the manipulated database have to stay resident in e.g., Excel?

          • 3. Re: Can catalog details such as keywords and exif data be exported to Microsoft access database or Excel?
            john beardsworth Adobe Community Professional & MVP

            The next version has an Excel add-on that writes back to LR. It's only for those with good Excel skills though. See List View 1.80 lets Excel send text to Lightroom

             

            By the way, there is also an ODBC driver for SQLite3, so you can hook Access directly up to a LR catalogue, and there are SQLite plugins for browsers (Firefox has an addon). LR's database is undocumented though.

            • 4. Re: Can catalog details such as keywords and exif data be exported to Microsoft access database or Excel?
              john beardsworth Adobe Community Professional & MVP

              Also look up LrTransporter as it may be a better choice for your initial migration. ListView is more for getting data out, though now it can also send data into LR.

               

              So although LR itself doesn't have text export/import, there are ways to do it.

              • 5. Re: Can catalog details such as keywords and exif data be exported to Microsoft access database or Excel?
                dariuszk32546812

                Today I was able to link tables from a copy of "Lightroom Catalog.lrcat" file ("Lightroom Catalog - Copy.lrcat") to Microsoft Access database (32-bit) using 32-bit version of the driver created by Christian Werner:

                http://www.ch-werner.de/sqliteodbc/ (SQLite ODBC Driver)

                I don't have any experience in this field so you can repeat this on your own risk, using a COPY of your "Lightroom Catalog.lrcat" file. Steps:

                • Install the driver (SQLite 2 Drivers option only)
                • Set up ODBC data source:
                  Control Panel:

                  Search: ODBC Data Sources
                  or:
                  Control Panel\System and Security\Administrative Tools

                  ODBC Data Sources (32-bit):
                  Set up ODBC data sources (32-bit) - User DSN - Add -
                  Create New Data Source - SQLite 3 ODBC Driver - Finish -
                  SQLite3 ODBC DSN Configuration:
                  Data Source Name: Lightroom Catalog
                  Database Name:    C:\Users\user name\Pictures\Lightroom\Lightroom Catalog - Copy.lrcat
                  No WCHAR option checked !*
                  - OK
                • Link the ODBC data source to the Microsoft Access database:
                  Microsoft Access database:
                  External Data - ODBC Database - Link the data source by creating a linked table - OK -
                  Select Data Source:
                  Machine Data Source - SQLite3 Datasource - OK -
                  SQLite3 ODBC Driver Connect:
                  Data Source Name: SQLite3 Datasource (perhaps more unique name would be better)
                  Database Name:    C:\Users\user name\Pictures\Lightroom\Lightroom Catalog - Copy.lrcat
                  - OK
                  Link Tables ...

                This database requires the "Lightroom Catalog - Copy.lrcat" file, because tables are connected only. To copy (export) all tables to the Access database choose:

                Import the source data into a new table in the current database

                instead of:

                Link the data source by creating a linked table.

                Some external links:

                https://ericgazoni.wordpress.com/2011/07/26/converting-sqlite-database-to-access/" Converting an SQLite database to Access | Eric Gazoni's Blog

                http://sqlite.awardspace.info/syntax/sqliteodbc.htm SQLite with Microsoft Access

                * No WCHAR option checked - because very large Memo (Long Text) fields would not be updated. If you created a linked table with No WCHAR unchecked, delete all ODBC linked tables and create them again with No WCHAR option checked (http://stackoverflow.com/questions/41099934/microsoft-access-cant-update-large-memo-long-t ext-fields-on-a-linked-sqlite3 ).

                 

                The "Lightroom Catalog - Copy.lrcat" file can be browsed also for example in the DB Browser for SQLite:

                http://sqlitebrowser.org/

                 

                Edited later:

                Below are few example EXPERIMENTAL queries for Microsoft Access database containing tables linked to "Lightroom Catalog - Copy.lrcat" file. They seem to work, but test them using a COPY of Lightroom database just in case, You are using them on your own risk.

                 

                Keywords:

                SELECT AgLibraryFile.id_local, AgLibraryKeywordImage.image, AgLibraryFile.originalFilename, AgLibraryKeyword.name, AgLibraryRootFolder.absolutePath, AgLibraryFolder.pathFromRoot, [AgLibraryRootFolder].[absolutePath] & [AgLibraryFolder].[pathFromRoot] AS absolutePath_pathFromRoot

                FROM ((((AgLibraryKeywordImage LEFT JOIN AgLibraryKeyword ON AgLibraryKeywordImage.tag = AgLibraryKeyword.id_local) LEFT JOIN Adobe_images ON AgLibraryKeywordImage.image = Adobe_images.id_local) LEFT JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local) LEFT JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local) LEFT JOIN AgLibraryRootFolder ON AgLibraryFolder.rootFolder = AgLibraryRootFolder.id_local

                ORDER BY AgLibraryFile.id_local, AgLibraryKeyword.name;

                 

                It seems that field values in the second query can be edited if recordset type in it's properties is set to Dynaset (Inconsistent Updates).

                 

                Exif_Data:

                SELECT AgLibraryFile.id_local, AgHarvestedExifMetadata.image, AgLibraryFile.originalFilename, Adobe_images.aspectRatioCache, Adobe_images.bitDepth, Adobe_images.captureTime, Adobe_images.colorLabels, Adobe_images.fileFormat, Adobe_images.fileHeight, Adobe_images.fileWidth, Adobe_images.orientation, AgHarvestedExifMetadata.aperture, AgInternedExifCameraModel.value, AgHarvestedExifMetadata.dateDay, AgHarvestedExifMetadata.dateMonth, AgHarvestedExifMetadata.dateYear, AgHarvestedExifMetadata.flashFired, AgHarvestedExifMetadata.focalLength, AgHarvestedExifMetadata.gpsLatitude, AgHarvestedExifMetadata.gpsLongitude, AgHarvestedExifMetadata.isoSpeedRating, AgInternedExifLens.value, AgHarvestedExifMetadata.shutterSpeed, Adobe_AdditionalMetadata.xmp, Adobe_imageProperties.propertiesString, AgLibraryRootFolder.absolutePath, AgLibraryFolder.pathFromRoot, [AgLibraryRootFolder].[absolutePath] & [AgLibraryFolder].[pathFromRoot] AS absolutePath_pathFromRoot

                FROM (((((((AgLibraryFile LEFT JOIN Adobe_images ON AgLibraryFile.id_local = Adobe_images.rootFile) LEFT JOIN AgHarvestedExifMetadata ON Adobe_images.id_local = AgHarvestedExifMetadata.image) LEFT JOIN Adobe_imageProperties ON Adobe_images.id_local = Adobe_imageProperties.image) LEFT JOIN Adobe_AdditionalMetadata ON Adobe_images.id_local = Adobe_AdditionalMetadata.image) LEFT JOIN AgInternedExifCameraModel ON AgHarvestedExifMetadata.cameraModelRef = AgInternedExifCameraModel.id_local) LEFT JOIN AgInternedExifLens ON AgHarvestedExifMetadata.lensRef = AgInternedExifLens.id_local) LEFT JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local) LEFT JOIN AgLibraryRootFolder ON AgLibraryFolder.rootFolder = AgLibraryRootFolder.id_local

                ORDER BY AgLibraryFile.id_local;

                 

                The shutter speed is in strange units, i don't know at this point also how to convert some of dates, because that is not VBA numeric format.

                 

                Most of parameters in the query below are in the "Text" field.

                DevelopSettings_Virtual_Copies:

                SELECT Adobe_imageDevelopSettings.image, AgLibraryFile.originalFilename, Adobe_images.copyName, Adobe_imageDevelopSettings.croppedHeight, Adobe_imageDevelopSettings.croppedWidth, Adobe_imageDevelopSettings.fileHeight, Adobe_imageDevelopSettings.fileWidth, Adobe_imageDevelopSettings.grayscale, Adobe_imageDevelopSettings.processVersion, Adobe_imageDevelopSettings.settingsID, Adobe_imageDevelopSettings.Text, Adobe_imageDevelopSettings.validatedForVersion, Adobe_imageDevelopSettings.whiteBalance, [AgLibraryRootFolder].[absolutePath] & [AgLibraryFolder].[pathFromRoot] AS absolutePath_pathFromRoot

                FROM (((Adobe_images LEFT JOIN Adobe_imageDevelopSettings ON Adobe_images.id_local = Adobe_imageDevelopSettings.image) LEFT JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local) LEFT JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local) LEFT JOIN AgLibraryRootFolder ON AgLibraryFolder.rootFolder = AgLibraryRootFolder.id_local

                WHERE Adobe_images.copyName Not Like "" And Not Adobe_images.copyName Is Null

                ORDER BY AgLibraryFile.originalFilename, Adobe_images.copyName;

                 

                Develop settings for original images - only WHERE different:

                WHERE Adobe_images.copyName Like "" Or Adobe_images.copyName Is Null

                 

                If you want to join Memo fields (Long Text), create an intermediate query with names of such fields converted by CStr() function, for example

                your custom field: CStr([AgLibraryKeyword].[name]) or

                your custom field: CStr([AgLibraryRootFolder].[absolutePath] & [AgLibraryFolder].[pathFromRoot])

                but not

                your custom field: [AgLibraryKeyword].[name] or

                your custom field:[AgLibraryRootFolder].[absolutePath] & [AgLibraryFolder].[pathFromRoot]

                because tables could not be linked through Memo fields. Moreover such quries with fields converted with CStr() are very slow in case of thousands records (maybe because they are not indexed), so it is better to create temporary tables containing data earlier converted by CStr() needed to run such queries.

                 

                In case of keywords the AgLibraryKeywordImage is linking table between AgLibraryKeyword and AdobeImages tables, it contains image IDs and keyword IDs.

                Recently I uploaded or updated thousands of records with keywords and geographical coordinates, using queries (other ones than posted in this message). Before that old keywords were deleted and new keywords were uploaded from the txt file using Lightroom's menu. Detailed description of all steps would take a lot of space. And editing xmp code in the xmp field of the Adobe_AdditionalMetadata table using VBA is more or less needed ...

                Data uploading or modifying in .lrcat files like the "Lightroom Catalog.lrcat" using applications other than Lightroom (or its SDK) is not only risky (can corrupt such file if you did it improperly), but you also probably would not get any support from Adobe in such case:

                Data modifing in the Lightroom Catalog.lrcat file and the Lightroom license

                 

                Edited later:

                First two queries were corrected. It seems that relation between the image ID and file ID is defined in the AdobeImages table - id_local and rootFile fields respectively, rootFile appears to be equal to id_local of the AgLibraryFile.

                These queries can be partially run in the DB Browser for SQLite (Execute SQL field), not only in Access. They are partially compatible with the SQLite syntax because of not using RIGHT JOIN:

                https://www.sqlite.org/omitted.html SQL Features That SQLite Does Not Implement

                but perhaps "LEFT JOIN" should be replaced by "LEFT OUTER JOIN" and maybe something else changed. And "&" should be replaced by "||" (string concatenation) to see file paths.