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?
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.
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.
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:
Search: ODBC Data Sources
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 !*
- 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
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
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:
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.
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).
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.
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])
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:
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.