30 Replies Latest reply on Oct 29, 2015 10:10 AM by photo-enthusiast

    Unlogged Missing Photos After Import From Aperture

    ClubmanPlus850

      Hi!

       

      I have just made the switch from Aperture to Lightroom, and have use the 1.1 version of the Aperture import plugin.

       

      In my Aperture Library I have, according to the Library -> Photos: 11105 Photos, however after importing to Lightroom, I have only 10967 photos. I have checked the import log, and there were 4 items which failed to import - 3 were .mpo files (panoramas from an xPeria) and 1 was a .gif file. This leaves a deficit of 133 photos that I can't account for.

       

      Is there any way to compare the aperture library to the lightroom library to see what is missing?

        • 1. Re: Unlogged Missing Photos After Import From Aperture
          photo-enthusiast Level 1

          Depends on how you are using the plugin.  If you're copying all files, then you can more easily compare.  And are you choosing to copy previews?

           

          First of all, not all graphic files will copy into the library.  Lightroom doesn't support them.  But the plug-in will set them aside in a separate sub directory of your target directory.  Or you could convert them beforehand.

           

          Remember, "referenced" masters in your Aperture library reside outside your library, and any "managed" masters reside in the Library package under "masters".

           

          If you copy all files, then all masters, and previews, end up in your destination directory.  So you'll need to sort through them.  If you leave masters in place, then it copies all files that are managed, and leaves the referenced masters in place.  Or if you have a mix, it will do both.

           

          Also remember that JPEG files could be masters, and all previews are masters.  But Adobe made it easier on us.  Aperture previews have an extension of _Aperture_preview.jpg.

           

          That said, you should have a 1:1 count between your "masters" folder and your destination, if you copy them, and you didn't copy previews.

           

          I'm using a tool called Araxis, which does a compare and shows what files were not copied.  Personally, I would find a compare tool if you want to make sure they all make it.  In my case, all Masters came across, but not all my previews!

           

          If this doesn't make sense, post whether you have all referenced images, all managed images, or both.  And if you want to copy the masters, or leave them in place (in the case of referenced files.)

          • 2. Re: Unlogged Missing Photos After Import From Aperture
            ClubmanPlus850 Level 1

            Thanks so much for the answer!

             

            I apologise for the tardy reply, I've been away on business - I will try and answer your questions as best as I can.

             

            1. as far as I know, I just copied all the files. I don't think I have any previews - I hadn't edited any photos in Aperture, merely used it as a storage location and easily viewing/cataloging

            2. I want all my images to be managed

             

            so, in summary, I believe I have all managed images, and I want to copy all masters...the terminology seems to be tripping me up slightly.

             

            I guess, it's best to say, I would periodically import photos from my iPhone and occasionally my DSLR or Point and Shoot (when I took them out with me). I started to organise photos by date, but didn't get very far. I had not done any modifications to any photos, either inside Aperture or in Photoshop or other tools. So, that being said, once i moved to Lightroom I fully expected everything (except the unsupported file types) to come across.

             

            So it sounds like my expectation was correct, but something in the plugin went wrong. Would you recommend Araxis to check and compare? apart from checking the import, it seems like it might be able to do duplicate detection as well* - for €99 is it worth the investment?

             

            cheers

             

            * I realise there is duplicate detection in both Lightroom and Aperture, but no one has been able to tell me the algorithm they use, and I don't want to lose images that either software thinks are duplicates but have minor diferences. It seems that Araxis does byte-level comparisons

            • 3. Re: Unlogged Missing Photos After Import From Aperture
              photo-enthusiast Level 1

              If you only have masters, you should be okay.  As far as I can tell, all my masters made it across.  But it seems you do have missing photos.

               

              There are a couple of ways to do this.  1) Relocate your master images to a location outside your library and make them "referenced".  Create a smart album and exclude all managed images to make sure you relocated all of them.  Perform the Lightroom migration.  It may not migrate and load all the images in the Catalog, but that's okay.  Based on your description, you don't have metadata or previews to worry about.  Use Lightroom to then scan the repository to pick up any stragglers.

               

              2) You could purchase Araxis.  I'm planning on doing a compare once they get the issues ironed out (hopefully).  €99 is steep!  I'd find an alternative.  For reference, Araxis Find Duplicate Files is $9.99 in the app store.

              • 4. Re: Unlogged Missing Photos After Import From Aperture
                ClubmanPlus850 Level 1

                Some two months later and I found the time to look at this again! OK, it's 2am, but still, thats progress!

                 

                I saw that Lightroom 5.7 was released and it has the aperture import plugin built in. Subsequently I downloaded and installed it. Renamed my Lightroom Masters folder in Finder, deleted the missing folders and collections, and then set Lightroom to reimport.

                 

                Since the last import i had not changed anything, and the same 11105 photos were shown as being in Aperture. After a few hours importing, Lightroom now gives me.... 11126 photos! (plus the same 4 incorrect format photos from the first import for a grand total of 11130 photos. This is the same number the plugin told me it had found in Aperture. So, that's all good - I can only guess that there is 25 photos in aperture which don't count towards it's photo count? previews perhaps?  is there anyway to check this?

                • 5. Re: Unlogged Missing Photos After Import From Aperture
                  photo-enthusiast Level 1

                  The Apple Photos announcement must have spurred you to action!

                   

                  If you can't single out these photos using Lightroom, which I suspect is the case, you'll need to use third-party utilities to compare. Another alternative is DIFF.

                   

                  Compare directories via diff - Mac OS X Hints

                   

                  You can see if the imported photos are Aperture previews if the file ends with _Aperture_preview.jpg  So IMG_3929.JPG or IMG_3929.RAW would become IMG_3929_Aperture_preview.jpg

                   

                  Remember, previews will not be copied if they were not automatically or manually created before the migration, and may not exist for non-edited images or images.

                   

                  That said, I was missing over 2/3 of the previews I created.  Adobe still hasn't addressed that concern based on my most recent tests.  However, it should copy all your masters.

                   

                  See if you can see a pattern in the files that weren't copied.  Good to know what the problem is, but with only 25 missing photos, it may be easier just to handle them manually.

                  • 6. Re: Unlogged Missing Photos After Import From Aperture
                    ClubmanPlus850 Level 1

                    Yep! and finally managed to get a half hour with no other chores to be done!

                     

                    Thanks for the diff tip, I actually knew about diff, but didn't know I could view the Aperture library directly in the terminal.

                     

                    I'll have a play around with it, it seems that the directory structures are all different, but should be able to use just the file names at least.

                    • 7. Re: Unlogged Missing Photos After Import From Aperture
                      photo-enthusiast Level 1

                      Be careful comparing file names only, you may have images with the same name.  But I believe DIFF will compare the file hash, which is what you'll want to ensure you have the same images in both locations.

                      • 8. Re: Unlogged Missing Photos After Import From Aperture
                        ClubmanPlus850 Level 1

                        Hi again photo-enthusiast,

                         

                        im having trouble getting this all to match up - does anything look strange to you in this?

                         

                        Here is a screen grab from Aperture - as you can see, Aperture thinks there are 11105 items:

                        aperture search .png

                        Apterture Photo Count.png

                        Here is the output from my terminal search:

                         

                        $ find Aperture\ Library.aplibrary/Masters/ -type f | wc -l

                           11138

                         

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.*' | sed 's|.*\.||' | sort -u

                        3gp

                        JPG

                        MOV

                        MPO

                        PNG

                        XMP

                        gif

                        jpeg

                        jpg

                        mov

                        mp4

                        png

                         

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.3gp' | wc -l

                               4

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.JPG' | wc -l

                            9343

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.MOV' | wc -l

                             243

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.MPO' | wc -l

                               3

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.PNG' | wc -l

                             269

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.XMP' | wc -l

                               8

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.gif' | wc -l

                               1

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.jpeg' | wc -l

                               2

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.jpg' | wc -l

                            1170

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.mov' | wc -l

                              27

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.mp4' | wc -l

                              25

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.png' | wc -l

                              43

                         

                         

                        Total: 11138

                        discrepancy : 33

                         

                        Managed : 11105

                        Referenced : 0

                         

                        # in terminal         # in aperture (when searching for .<extension> i.e. .3gp)

                        3gp: 4                  4

                        jpg: 10513           10488

                        mov: 270             270

                        mpo: 3                 3

                        png: 312              312

                        xmp: 8                 0  

                        gif: 1                    1

                        jpeg: 2                 2

                        mp4: 25               25

                         

                        So, the missing photos/items are: 8 xmp files (no surprise there, though no idea where these xmp files came from) and there is a discrepancy of 25 for the jpg : thus the discrepancy of 33 is accounted for, though still no idea why Aperture is not showing 25 jpg files that are available in the file system.

                         

                        $ find Aperture\ Library.aplibrary/Masters/ -type f -name '*.jpg' -o -name '*.JPG' | wc -l

                           10513


                        examining the filenames in the terminal, I see no reason why 25 jpg files should be missing inside Aperture. Even looking for duplicates ( with (1) in the filename) or small files, doesn't seem to help...


                        Now Lightroom has 11126 + 4 missing files : 11130 total files + 8 XMP files == 11138 (the number of files found in Master)


                        OK! So Lightroom has imported all the Aperture Masters it's just missing the 4x 3gp files (which the importer reported).... so I think that means the import was a success! But Aperture is doing something strange... OK! off the Apple forums I think!


                        I will probably do a checksum diff at some point, just to be sure, though


                        Thanks again!

                        • 9. Re: Unlogged Missing Photos After Import From Aperture
                          photo-enthusiast Level 1

                          Thanks for reporting back.  Great analysis and deductive reasoning.

                           

                          First off, it appears Lightroom has all your masters, which is critically important.  Good to know.

                           

                          Not sure what to think about Aperture and reporting of JPG files.  I've seen this before in my own libraries, not able to reconcile the counts.  I usually do this when I find a corrupt image, trying to see if there are more.  90% of the time, Aperture can't create a thumbnail correctly, despite using repair library and regenerate thumbnail commands.  So I export the master, reimport the master, then apply edits and metadata from the corrupt image.

                           

                          Perhaps these are RAW+JPEG imports?  In the same view with all files, filter on "File Type" for RAW+JPEG.

                           

                          These "images" will count as one image in the Aperture count.

                           

                          I know back on Dec 4th you mentioned you don't edit many photos.  By chance did you choose to import your Aperture Previews?  This is where I've found the biggest issue in my tests.  As one of the few in the forum that has the technical capacity to analyze the results, I'm curious to see your result in copying previews--if you had any.

                          • 10. Re: Unlogged Missing Photos After Import From Aperture
                            ClubmanPlus850 Level 1

                            The file difference aren't RAW+JPEG unfortunately.

                             

                            I'm not 100% sure if the extra JPGs on disk are previews, my settings are to use camera previews - however when looking at the Aperture library structure on disk, Previews are a separate folder (which contains 7138 images), thus my conclusion here is that the missing 25 JPGs aren't previews.

                             

                            I have, however found something that might be it, though I can't explain it!

                             

                            When I look in the iCloud section under shared in Aperture, I have 4 streams.

                            - Family

                            - Family

                            - Family

                            - Screensaver


                            I'm not sure where "Family" has come from, and why I have three streams called Family. However, these all contain 0 images. The Screensaver stream I did create, and shared to iCloud so I have the same screensaver on all my AppleTVs. Now, the curious thing here is that Screensaver has 22 images in it.


                            So 3xFamily + 22 images in Screensaver (in a roundabout way) is 25 items. Could this be where the extra 25 JPGs are on disk? It would be strange, but it's the only lead I've turned up tonight.


                            I have been doing shell script experiments to try and find the differences, but I have found the ability to export metadata from Aperture doesn't help terribly much (perhaps in the light of day I can find a better way to parse it).


                            not much further down the path tonight. but tomorrow is another day

                            • 11. Re: Unlogged Missing Photos After Import From Aperture
                              photo-enthusiast Level 1

                              Sounds plausible.  Isolating those images is the only way to know.

                               

                              I had several minor problems with iCloud albums and photo stream, and eventually just turned them off.

                              • 12. Re: Unlogged Missing Photos After Import From Aperture
                                ClubmanPlus850 Level 1

                                been sick for a couple of days, but the metadata is a total bust. really annoying and difficult to deal with. Instead I'll attack at the source and grab the SQLite database file

                                • 13. Re: Unlogged Missing Photos After Import From Aperture
                                  photo-enthusiast Level 1

                                  Let me know what method you plan to use with the SQLite DB.

                                   

                                  Glad you're feeling better!

                                  • 14. Re: Unlogged Missing Photos After Import From Aperture
                                    ClubmanPlus850 Level 1

                                    cheers! winter flu with kids in day care... fun!

                                     

                                    In my usual fashion it's been nearly a month since I have had a chance to play with this. Actually I was very tempted to throw it all in seeing as Lightroom appears to have every file from Aperture (and then some!) but Im just too stubborn to let this go now (and far too curious!)

                                     

                                    But the summary thus far is that using the (latest) Lightroom Import Plugin for Aperture, all my images have been imported. Well, at least the counts match up when you examine the filesystem. This pretty much means that the Lightroom Import Plugin is walking the Aperture library for images and then importing them. There might be some more magic for handling other features, but this is the basis. If Lightroom finds a file it can't handle, then it just moves it to a special folder. This means I have a fairly high confidence that the import was successful - so the tools I used here will serve as a useful reference for anyone else wanting to check their import.

                                     

                                    I have two remaining issues then:

                                    1) Aperture's GUI does not agree with the filesystem - 25 files are in the Library on disk which don't appear in the GUI

                                    2) Is every image from Aperture in Lightroom?

                                     

                                    In the second case, an examination of each file and it's checksum will confirm this. But it would seem highly likely, in any case.

                                     

                                    The first question though is more difficult. As reported last time I will start looking at the SQL Database. Fortunately as a professional DBA, this shouldn't present too much of a challenge. I have found a reverse-engineered schema diagram for the Aperture DB, which should help. The actual SQL DB I will export to my Work system where I have a few better tools to use. My process will be to generate a list of all the images in the DB, this *should* be a total of 11105. Then I can walk the Aperture directory and compare the the input list. This should result in 25 jpg images in the filesystem that are not in the DB. Well, that's the plan anyway! I will of course report back.

                                     

                                    The long of it is, I can now import more pictures to my Lightroom library without fear of breaking my research, which is what I have been concerned about.


                                    So! Until the next report (which given my update schedule, you can expect in 6-12 months... though lets hope for sometime next week when I'm back at work)

                                    • 15. Re: Unlogged Missing Photos After Import From Aperture
                                      ClubmanPlus850 Level 1

                                      Right! So an interesting day pulling apart the Aperture SQL DB!

                                       

                                      *WARNING* this is a long post, but it describes my process for anyone wanting to repeat, and also helps to explain how Aperture works a little bit. Lastly, it should be enough info for someone who knows this better than I to point out my mistakes.

                                       

                                      *WARNING 2* This post contains SQL, you probably can't break anything too bad (so I'm told), but if you break your DB, I'm not taking responsibility! (also SQL Is boring, so you probably want to stop reading here)

                                       

                                      Firstly, the schema was a lot different than I was expecting (and actually there were many more DBs than I expected) based on my reading on the web before hand. (actually a few write-ups were incomplete due to the authors moving to Lightroom...Deja Vu indeed!)

                                       

                                      Ok, so the DB file I was most interested in was Library.apdb - seemed like a likely start. Based on my earlier investigations I was expecting to see 11105 images somewhere in a table, and then being able to cross-reference the file system, find the missing 25 items.

                                       

                                      First of all, if you're playing along at home, you can see a dump of the DB schema attached (Dropbox - Library Schema.txt).

                                      Obviously the place to start was RKMaster - a likely holding place for all master images: Unfortunately select count(*) from RKMaster; == 11130 entries! Damnit! So, there is actually 11130 master files, which matches the file system (i have read that if you delete the DB, Aperture will re-build it from the Filesystem information). Well, this good in one way, it confirms that there is 11130 files, and that everything (that could be migrated) was migrated to Lightroom. But, still! RKMaster != Aperture GUI! What is going on?

                                       

                                      My working theory then is that the 25 "missing" images might be previous versions of images I have edited. Now, to the best of my knowledge, I have not edited any images within Aperture. But there is a table there, RKImageAdjustments, lets have a look there...

                                      select count(*) from RKImageAdjustments -> 26 rows.. that's pretty close. We are looking for 25 images, and here is 26 - perhaps the extra one is an image that has been edited twice? So, Aperture is only showing the latest version of each image, and keeping all versions on disk... make sense.

                                       

                                      sqlite> select count(*) from RKMaster where fileCreationDate != fileModificationDate;

                                      98


                                      ok, so 98 images where the fileCreationDate and fileModificationDate are different.. no help there (probably images I have rotated)

                                       

                                      assuming the column "modelID" is the unique index given to each file in Aperture,

                                      select count(distinct modelID) from RKImageAdjustment;

                                      26

                                      each adjustment appears to operating on separate images.


                                      Lets compare the adjustments to the masters:

                                       

                                      select imagePath from RKMaster INNER JOIN RKImageAdjustment ON RKMaster.modelID = RKImageAdjustment.modelID;

                                      2014/04/11/20140411-222634/IMG_0480.JPG

                                      2014/04/30/20140430-212059/IMG_0880.JPG

                                      2014/06/28/20140628-223322/IMG_4134.JPG

                                      2014/07/10/20140710-232559/IMG_4545.JPG


                                      Ok, now I'm confused! There are apparently only four images in the Adjustments table that are also in the masters table!


                                      digging for more information:

                                       

                                      sqlite> select imagePath, RKMaster.modelID from RKMaster INNER JOIN RKImageAdjustment ON RKMaster.modelID = RKImageAdjustment.modelID;

                                      2014/04/11/20140411-222634/IMG_0480.JPG|502

                                      2014/04/30/20140430-212059/IMG_0880.JPG|1081

                                      2014/06/28/20140628-223322/IMG_4134.JPG|14691

                                      2014/07/10/20140710-232559/IMG_4545.JPG|15963


                                      and then even more:


                                       

                                      sqlite> select modelID, uuid, name, versionUuid from RKImageAdjustment where modelID=502 or modelID=1081 or modelID=14691 or modelID=14691 or modelID=15963;

                                      502,c8GilDfgQVyEsv3Ok4HgiQ,DGiOSEditsOperation,QjstyKaSSWGR5pjiAH6OwQ

                                      1081,JhmDjoPAQPumx38%+hAbkg,RKCropOperation,p+dQjox1TiWntXcK0BqUyw

                                      14691,yj%jGcdHTjW4Eci6fILwqw,DGiOSEditsOperation,d+GIt+7%Qp2V%j5yBVxYgg

                                      15963,vXOqdl5kTWC5weA5u5Jo4A,DGiOSEditsOperation,fvRzLSZaTISPLbGPKXQ5Wg


                                      looks like some of the edits are from my iPhone (DGiOSEditsOperation).. that sounds about right.

                                       

                                      sqlite> select modelID, uuid, name, versionUuid from RKImageAdjustment where modelID!=502 or modelID!=1081 or modelID!=14691 or modelID!=14691 or modelID!=15963;

                                      this lists a lot of info, and I can then look for the different uuid's in Master, but there are no matches. So my interpretation of the columns is wrong, or my working assumption is wrong.


                                      So! At this stage, I am no closer to solving my question. I was hoping the database would reflect the GUI, but apparently not. I have some more digging to do - if I get time I might try and reverse engineer a data model, but I doubt I will have the time right now. My other option is to try and find a better way to parse the output of the GUI... The problem is my standard Unix tools can't deal with either the file size or the line size. I might have to write a perl or python script to parse it instead.


                                      On a side note, looking in the RKAttachment table reveals the 8 XMP files. For the grand total of 11138 files originally located on disk.


                                      I can fairly well conclude then that the migration to Lightroom was successful. And the steps I've documented here will allow you to follow and verify your own migration. However, the info I found in the DB will allow me to build a script to do a verification check, so that might be my next step as well.


                                      So, if anyone can shed some light on my database queries and thinking, it would be appreciated- my gut says that it is the adjustments that is the missing files, but I just can't find a way to prove it..... yet!


                                      P.S two updates in two days... I'm not feeling at all well!


                                      Notes:

                                      1) The library.apdb file is in the Aperture Library Package, in the Database/apdb folder

                                      2) I used sqlite3 command line utility, which is available on your Mac in the Terminal (tip from the coach to exit, type .exit) use as

                                      cd <Aperture Library Path>/database/apdb

                                      sqlite3 Library.apdb

                                       

                                      to export the metadata for your library, select all the images. then File -> export -> metadata : ill report back with the best way to parse this file as soon as I work it out myself!


                                      • 16. Re: Unlogged Missing Photos After Import From Aperture
                                        ClubmanPlus850 Level 1

                                        *WARNING* Once agin, this is a VERY long post! And this contains not only SQL, but heaps of command line fun!

                                         

                                        TLDR Summary: Aperture is storing duplicates on disk (and referencing them in the DB) but hiding them in the GUI. Exactly how it does this, I'm not sure yet. And how to clean it up, I'm not sure either. But if you would like to know how I proved it, read on!

                                         

                                        An update on handling metadata exported from Aperture. Once you have a file, if you try to view it in the terminal, perhaps like this:

                                         

                                        $ less ApertureMetadataExtendedExport.txt

                                        "ApertureMetadataExtendedExport.txt" may be a binary file.  See it anyway?


                                        you will get that error. Turns out I was wrong, it's not (only?) due to the size of the file / line length; it's actually the file type Aperture creates:

                                         

                                        $ file ApertureMetadataExtendedExport.txt

                                        ApertureMetadataExtendedExport.txt: Little-endian UTF-16 Unicode text, with very long lines

                                         

                                        The key bit being "Little-endian UTF-16", that is what is causing the shell to think it's binary. The little endian is not surprising, after all it's an X86_64 platform. The UTF-16 though is not able to be handled by the shell. So it has to be converted. There are command line utils, but Text Wrangler does the job nicely.

                                         

                                        After conversion (to Unicode UTF-8):

                                         

                                        $ file ApertureMetadataExtendedExport.txt

                                        ApertureMetadataExtendedExport.txt: ASCII text, with very long lines


                                        and

                                         

                                        $ less ApertureMetadataExtendedExport.txt

                                        Version Name    Title   Urgency Categories      Suppl. Categories       Keywords        Instructions    Date Created    Contact Creator Contact Job Title       City    State/Province  Country Job Identifier  Headline        Provider        Source  Copyright Notice        Caption Caption Writer  Rating  IPTC Subject Code       Usage Terms     Intellectual Genre      IPTC Scene      Location        ISO Country Code        Contact Address Contact City    Contact State/Providence        Contact Postal Code     Contact Country Contact Phone   Contact Email   Contact Website Label   Latitude        Longitude       Altitude        AltitudeRef


                                        So, there you have it! That's what you have access to when exporting the metadata. Helpful? Well, at first glance I didn't think so - as the "Version Name" field is just "IMG_2104", no extension, no path etc. So if we have multiple images called "IMG_2104" we can't tell them apart (unless you have a few other fields to look at - and even then just comparing to the File System entries wouldn't be possible). But! In my last post, I mentioned that the Aperture SQLite DB (Library.apdb, the RKMasters table in particular) contained 11130 entries, and if you looked at the Schema, you would have noticed that there was a column called "originalVersionName" which should match! So, in theory, I can now create a small script to compare metadata with database and find my missing 25 files!


                                        First of all, I need to add that, when exporting metadata in Aperture, you need to select all the photos! ... and it will take some time! In my case TextWrangler managed to handle the 11108 line file without any problems. And even better, after converting, I was able to view the file with less. This is a BIG step on my last attempt.


                                        At this point it is worth pointing out that the file is tab-delimited (csv would be easier, of course) but we should be able to work with it anyway.


                                        To extract the version name (first column) we can use awk:

                                        $ cat ApertureMetadataExtendedExport.txt | awk -F'\t' '{print $1}' > ApertureMetadataVersionNames.txt

                                        and we can compare the line counts of both input and output to ensure we got everything:

                                         

                                        $ wc -l ApertureMetadataExtendedExport.txt

                                           11106 ApertureMetadataExtendedExport.txt

                                        $ wc -l ApertureMetadataVersionNames.txt

                                           11106 ApertureMetadataVersionNames.txt


                                        So far, so good! You might have noticed that the line count is 11106, not 11105, the input file has the header as I printed earlier. So we need to remove the first line. I just use vi for that.


                                        Lastly, the file needs to be sorted, so we can ensure we are looking in the same order when comparing the metadata version names with the DB version names.

                                         

                                        $ cat ApertureMetadataVersionNames.txt | sort > ApertureMetadataVersionNamesSorted.txt


                                        To get the Version Names from the DB, fire up sqlite3:

                                         

                                        $ sqlite3 Library.apdb


                                        sqlite> .output ApertureDBMasterVersionNames.txt

                                        sqlite> select originalVersionName from RKMaster;

                                        sqlite> .exit


                                        Checking the line count in the DB Output:

                                         

                                        $ wc -l ApertureDBMasterVersionNames.txt

                                           11130 ApertureDBMasterVersionNames.txt


                                        Brilliant! 11130 lines as expected. Then sort as we did before:

                                         

                                        $ cat ApertureDBMasterVersionNames.txt | sort > ApertureDBMasterVersionNamesSorted.txt


                                        So, now, in theory, running a diff on both files, should reveal the 25 missing files.... I must admit, I'm rather excited at this point!


                                         

                                        $ diff ApertureDBMasterVersionNamesSorted.txt ApertureMetadataVersionNamesSorted.txt


                                        IT WORKED! The output is a list of changes you need to make to the second input file to make it look the same as the first. Essentially, this will (in my case) show the Version Names that are missing in Aperture that are present on the File System.


                                        So, a line like this:

                                        1280,1281d1279

                                        < IMG_0144

                                        < IMG_0144


                                        basically just means, that there are IMG_0144 appears twice more in the DB than in the Metadata. Note: this is specific for the way I ordered the input files to diff; although you will get the same basic output if you reversed the input files to diff, the interpretation is obviously reversed) as shown here: (note in the first output, we have 'd' for deleted, and in the second output it's 'a' for added)

                                         

                                        1279a1280,1281

                                        > IMG_0144

                                        > IMG_0144


                                        In anycase, looking through my output and counting, I indeed have 25 images to investigate. The problem here is we just have a version name, fortunately in my output, most are unique with just a couple of duplicates. This leads me to believe that my "missing" files are actually Aperture handling duplicates (though why it's hiding them I'm not sure). I could, in my DB dump look at the path etc as well and that might help, but as it's just 25 cases, I will instead get a FS dump, and grep for the version name. This will give me all the files on the FS that match. I can then look at each and see what's happening.


                                        Dumping a list of master files from the FS: (execute from within the Masters directory of your Aperture library)

                                        $ find . -type f > ApertureFSMasters.txt

                                         

                                        This will be a list including path (relative to Master) which is exactly what we want. Then grep for each version name. For example:

                                         

                                        $ grep IMG_0144 ApertureFSMasters.txt

                                        ./2014/04/11/20140411-222634/IMG_0144.JPG

                                        ./2014/04/23/20140423-070845/IMG_0144 (1).jpg

                                        ./2014/04/23/20140423-070845/IMG_0144.jpg

                                        ./2014/06/28/20140628-215220/IMG_0144.JPG


                                        Here is a solid bit of information! On the FS i have 4 files called IMG_0144, yet if I look in the GUI (or metadata dump) I only have 2.

                                         

                                        $ grep IMG_0144 ApertureMetadataVersionNamesSorted.txt

                                        IMG_0144

                                        IMG_0144


                                        So, there is two files already!


                                        The path preceding the image in the FS dump, is the date of import. So I can see that two were imported at the same time, and two separately. The two that show up in the GUI have import sessions of 2014-06-28 @ 09:52:20 PM and 2014-04-11 @ 10:26:34 PM. That means that the first and last are the two files that show in the GUI, the middle two do not.... Why are they not in the GUI (yet are in the DB) and why do they have the exact same import date/time? I have no answer to that yet!


                                        I used open <filename> from the terminal prompt to view each file, and 3 out of my 4 are identical, and the fourth different.


                                        So, lastly, with a little command line fu, we can make a useful script to tell us what we want to know:

                                         

                                        #! /bin/bash

                                         

                                        grep $1 ApertureFSMasters.txt | sed 's|\.|Masters|' | awk '{print "<full path to Aperture Library folder>"$0}' | \

                                        while read line; do

                                          openssl sha1 "$line"

                                        done


                                        replace the <full path to Aperture Library folder> with the full path to you Aperture Library Folder, perhaps /volumes/some_disk_name/some_username/Pictures/.... etc. Then chmod 755 the script, and execute ./<scriptname> <version name> so something like

                                        $ ./calculateSHA.sh IMG_0144


                                        What we're doing here is taking in the version name we want to find (for example IMG_0144), and we are looking for it in the FS dump list. Remember that file contains image files relative to the Aperture Library Master path, which look something like "./YYYY/MM/DD/YYYYMMDD-HHMMSS/<FILENAME>" - we use sed to replace the "./" part with "Masters". Then we pipe it to awk, and insert the full path to aperture before the file name, the end result is a line which contains the absolute path to an image. There are several other ways to solve this, such as generating the FS dump from the root dir. You could also combine the awk into the sed (or the sed into the awk).. but this works. Each line is then passed, one at a time, to the openssl program to calculate the sha-1 checksum for that image. If a SHA-1 matches, then those files are identical (yes, there is a small chance of a collision in SHA-1, but it's unlikely!).


                                        So, at the end of all this, you can see exactly whats going on. And in my case, Aperture is storing duplicates on disk, and not showing them in the GUI. To be honest, I don't actually know how to clean this up now! So if anyone has any ideas. Please let me know I can't just delete the files on disk, as they are referenced in the DB. I guess it doesn't make too much difference, but my personality requires me to clean this up (at the very least to provide closure on this thread).


                                        The final point to make here is that, since Lightroom also has 11126 images (11130 less 4 non-compatible files). Then it has taken all the duplicates in the import.


                                        Well, that was a fun journey, and I learned a lot about Aperture in the process. And yes, I know this is a Lightroom forum and maybe this info would be better on the Aperture forum, I will probably update it there too. But there is some tie back to the Lightroom importer to let people know whats happening internally. (I guess I should update my earlier post, where I assumed the Lightroom Aperture import plugin was using the FS only, it *could* be using the DB as well (and probably is, so it can get more metadata))


                                        UPDATE: I jumped the gun a bit here, and based my conclusion on limited data. I have finished calculating the SHA-1 for all my missing versions. As well as comparing the counts in the GUI, to the counts in the FS. For the most part, where the GUI count is lower than the FS count, there is a clear duplicate (two files with the same SHA-1). However I have a few cases, where the FS count is higher, and all the images on disk have different SHA-1's! Picking one at random from my list; I have 3 images in the GUI called IMG_0843. On disk I have 4 files all with different SHA-1's. Viewing the actual images, 2 look the same, and the other 2 are different. So that matches 3 "unique" images.


                                        Using Preview to inspect the exif data for the images which look the same:

                                        image 1:

                                        Pixel X Dimension: 1 536

                                        Pixel Y Dimension: 2 048

                                         

                                        image 2:

                                        Pixel X Dimension: 3 264

                                        Pixel Y Dimension: 2 448

                                         

                                        (image 2 also has an extra Regions dictionary in the exit)

                                         

                                        So! These two images are not identical (we knew that from the SHA-1), but they are similar (content is the same - resolution is the same) yet Aperture is treating these as duplicates it seems.. that's not good! does this mean that if I resize an image for the web, and keep both, that Aperture won't show me both? (at least it keeps both on disk though, I guess...)

                                         

                                        The resolution of image 1, is suspiciously like the resolutions that were uploaded to (the original version of) iCloud Photos on the iPhone (one of the reasons I never used it). And indeed, the photo I chose at random here, is one that I have in an iCloud stored album (I have created a screensaver synced to iCloud, to use on my various Mac's and AppleTVs). Examining the data for the cloud version of the image, shows the resolution to be 1536x2048. The screensaver contains 22 images - I theorised earlier that these might be the missing images, perhaps I was right after all? Yet another avenue to explore.

                                         

                                        Ok. I dumped the screensaver metadata, converted it to UTF-8, grabbed the version names, and sorted them (just like before). Then compared them to the output of the diff command. Yep! the 22 screensaver images match to 22 / 25 missing images. The other 3, appear to be exact duplicates (same SHA-1) of images already in the library. That almost solves it! So then, can I conclude that Lightroom has imported my iCloud Screensaver as normal photos of lower res? In which case, it would likely do it for any shared photo source in Aperture, and perhaps it would be wise to turn that feature off before importing to Lightroom?

                                        • 17. Re: Unlogged Missing Photos After Import From Aperture
                                          photo-enthusiast Level 1

                                          Thanks for the update, and especially, all the detail.  I just returned from a 10-day out of town trip.  Was able to read through your posts, but am not yet in a position to try our your analysis to see what results I come up with.  Still using Aperture for now.  Might have made the switch by now if Adobe support provided *any* support at all.

                                           

                                          Especially interesting is the Aperture database insights, as well as analysis for duplicates.

                                          • 18. Re: Unlogged Missing Photos After Import From Aperture
                                            ClubmanPlus850 Level 1

                                            Welcome back! Wasn't sure if anyone was still following along!

                                            I have some scripts in the works to do some of this automatically. Plus I am working to correlate my duplicate entries and verify the 25 *missing* images

                                            • 19. Re: Unlogged Missing Photos After Import From Aperture
                                              ClubmanPlus850 Level 1

                                              There is an update coming really soon! and some scripts too... then I might be able to put this to bed

                                              • 20. Re: Unlogged Missing Photos After Import From Aperture
                                                photo-enthusiast Level 1

                                                That would be great.  I'm still trying to figure out why the migration isn't working for me.

                                                • 21. Re: Unlogged Missing Photos After Import From Aperture
                                                  davefeldman

                                                  Possibly related: I think the Aperture importer might only be importing one preview for a given Aperture version name. That is, if I have a bunch of versions named "out on a hike" or something, and I've asked the plug-in to import the previews, it kinda looks like only one of those "out on a hike" previews makes it into Lightroom. I don't know if that problem extends to masters as well—at least for the most part master filenames will tend to be unique, though now I'm worried.

                                                   

                                                  Assuming I'm correct with this theory, I wonder if there's an easy way to go in and ensure version names are unique across an entire Aperture library?

                                                  • 22. Re: Unlogged Missing Photos After Import From Aperture
                                                    photo-enthusiast Level 1

                                                    Hmm.  This may be explainable on the aperture side.  Forgive me breaking into detail just to make sure we're on the same page.  You have a master, and you've created version(s) of each master and renamed "out on a hike".  Versions are typically created from a master for different cropping ratios (8x10, 4x6), have a color or black and white image, etc.  These versions are typically maintained in a stack.

                                                     

                                                    Previews are JPEG images generated at a specific resolution as specified in the Aperture preview settings.  Previews may be generated automatically, or manually.  There is a one-to-one OR one-to-none relationship between versions and previews.  I'm guessing you have manually or automatically created previews for the first stack image, but not for the rest.  It's not easy to see if you have created a preview for an image.  So I'd suggest you select all the images you are looking to test, select the command to expand all stacks, generate previews, then try your test again.  Please be aware that this may create smaller or larger previews than what you had before.

                                                     

                                                    In theory, you should have these previews in Lightroom if you select the option to migrate.  But perhaps this is a problem!  I'll keep that in mind next time I run a test.

                                                     

                                                    You can ensure version names are unique, at a cost.  You could relocate masters and rename the files, but you'd lose continuity on backups and other references.  Not sure this would have an effect.  I can't seem to find a correlation between what previews are copied, and which ones are not.  It's complicated by the fact that the plug-in only copies previews of raw files, NOT "previews" of JPEGs or TIFFs.  That's a good thing, but makes a post-migration checkup more difficult.  You can work around this, but it takes time.

                                                    • 23. Re: Unlogged Missing Photos After Import From Aperture
                                                      davefeldman Level 1

                                                      Here's some more info on the duplicate-naming issue. I did some experiments with a little test library, so I'm fairly confident about the following. In all cases I'm importing full-size previews to get my adjustments.

                                                      • Originals with duplicate filenames come over just fine: Lightroom just appends something to the filename.
                                                      • Previews for versions with duplicate names do not come over fine. You get one preview imported per version name.
                                                      • Oddly, if you've got two versions of an original, you'll get two imports of that original (without adjustments). If you've made adjustments, you'll get those as a third image, unless both versions have the same name, in which case you won't get the adjustments.

                                                      Seems like the most compact thing to do to get around this is to batch-append an index to all adjusted photos in Aperture. However, I'm now getting paranoid about the importer, so I think I'm just gonna append an index to everything in my Aperture library to enforce complete uniqueness. We'll see how that goes.

                                                      • 24. Re: Unlogged Missing Photos After Import From Aperture
                                                        photo-enthusiast Level 1

                                                        Interesting discussion.  First off, are you importing referenced images, managed images, or both.  I've seen a difference in behavior in both.

                                                         

                                                        Originals with duplicate filenames come over just fine: Lightroom just appends something to the filename.

                                                        When you say "originals", are you talking about masters?  If so, do you see a difference between raw files and JPEG masters?  What does it append to a file name?  Not sure I've noticed that Lightroom appends something to the file name of master files, just previews.

                                                         

                                                        Or do you mean preview of the same-named, single version of the master file, which seems to me your next example is a description of.

                                                        Previews for versions with duplicate names do not come over fine. You get one preview imported per version name.

                                                        Is this an example of what you're describing?:  One single master raw image.  You create a version (both stacked together), and you rename the version by removing " - Version X" off the version name.  Two versions, one master, same version name for both.  OR are you also seeing this with two master files with the same version name.  We typically see duplicate names when shooting two cameras, lots of images, or choose to renumber your images starting with 0001 each time you insert a new card.

                                                         

                                                        • Oddly, if you've got two versions of an original, you'll get two imports of that original (without adjustments). If you've made adjustments, you'll get those as a third image, unless both versions have the same name, in which case you won't get the adjustments.

                                                        Two versions of one original?  This is different from your last description?  Or perhaps you're saying one master with two versions resutls in an import of two masters and one preview with adjustments.  Should be one master with two previews with adjustments.

                                                         

                                                        Would it be possible to attach a screen shot or video of what you're seeing?  QuickTime Player (natively installed on OS X) can record video.  [Cmd][Shift]4 can take localized screen shots.

                                                         


                                                        Seems like the most compact thing to do to get around this is to batch-append an index to all adjusted photos in Aperture. However, I'm now getting paranoid about the importer, so I think I'm just gonna append an index to everything in my Aperture library to enforce complete uniqueness. We'll see how that goes.

                                                        All I can say is THANK YOU!  Thought I was going insane, but I might still.

                                                         

                                                        Figured I was either doing something incorrectly or had a corruption issue.  NO ONE else has noticed bad behavior with copying previews.  I've been searching and posting for months.  Two related bug reports were ignored by Adobe.

                                                         

                                                        One option is to export your metadata to CSV.  You could search for duplicates in a spreadsheet and make manual changes.  Could even create a smart album to help if you can find patterns to the behavior.

                                                         

                                                        Once I better understand what you're seeing, I might be able to go for another test round and help out with suggestions.

                                                        • 25. Re: Unlogged Missing Photos After Import From Aperture
                                                          davefeldman Level 1
                                                          Originals with duplicate filenames come over just fine: Lightroom just appends something to the filename.

                                                          When you say "originals", are you talking about masters?  If so, do you see a difference between raw files and JPEG masters?  What does it append to a file name?  Not sure I've noticed that Lightroom appends something to the file name of master files, just previews.

                                                          Sorry, I was using Aperture's terminology; will stick with Lightroom's going forward.

                                                           

                                                          So as an example: suppose (in Aperture) I import a photo named IMG_1234.JPG. Then I import a different one with the same name. Then I run the LR importer. I'll end up with photos named something like IMG_1234_123.JPG and IMG_1234_456.JPG. Same for raw (CR2) files.

                                                           

                                                          Previews for versions with duplicate names do not come over fine. You get one preview imported per version name.

                                                          Is this an example of what you're describing?:  One single master raw image.  You create a version (both stacked together), and you rename the version by removing " - Version X" off the version name.  Two versions, one master, same version name for both.  OR are you also seeing this with two master files with the same version name.  We typically see duplicate names when shooting two cameras, lots of images, or choose to renumber your images starting with 0001 each time you insert a new card.

                                                           

                                                          This is primarily about multiple photos (masters) that I've given the same version name. So let's say I import 10 photos from a hike, make some adjustments, and name them all (i.e., version name) "hike". Then I run the importer. All their masters will be imported (with the original filenames), and all the masters will show up in the "Adjusted in Aperture" collection that's created, but only one of them will have a separate JPEG preview with its adjustments. (If I named five of them "hike" and five "after the hike" I would then get two previews, one out of each group.)

                                                           

                                                          • Oddly, if you've got two versions of an original, you'll get two imports of that original (without adjustments). If you've made adjustments, you'll get those as a third image, unless both versions have the same name, in which case you won't get the adjustments.

                                                          Two versions of one original?  This is different from your last description?  Or perhaps you're saying one master with two versions resutls in an import of two masters and one preview with adjustments.  Should be one master with two previews with adjustments.

                                                          So say I have IMG_1234.JPG again. If I make some adjustments to it, then run the importer (and there are no other duplicate-related issues), I'll have both the original file and a JPEG preview with the adjustments. But say, in Aperture, I manually create an additional version off the master (that is, I right-click, and select one of the new-version options in the menu). Post-import, I will now have three images in Lightroom: the master, a second copy of the master, and a preview. But if both versions in Aperture have the same version name, I'll just get the two copies of the original in Lightroom. (Presumably if I've got adjustments to both versions and the names are different, I'll end up with four—two masters and two previews—but I don't think I tested that.)

                                                           

                                                          One option is to export your metadata to CSV.  You could search for duplicates in a spreadsheet and make manual changes.  Could even create a smart album to help if you can find patterns to the behavior.

                                                           

                                                          Can you explain more about this? What it is, how to do it? TBH, I don't need perfection in all of this, I'd just like 99% of my originals and the majority of my adjustments to make it over in some reasonable form.

                                                          • 26. Re: Unlogged Missing Photos After Import From Aperture
                                                            photo-enthusiast Level 1

                                                            davefeldman wrote:

                                                             

                                                            Can you explain more about this? What it is, how to do it? TBH, I don't need perfection in all of this, I'd just like 99% of my originals and the majority of my adjustments to make it over in some reasonable form.

                                                            Looks like I need to run tests and compare with your description.

                                                             

                                                            I don't have Aperture with me now, but you select all the images you need, which may include expanding the stacks, selecting the images, then "File", "Export", "Metadata". 

                                                            • 27. Re: Unlogged Missing Photos After Import From Aperture
                                                              ClubmanPlus850 Level 1

                                                              What I am working on (well at least I was before my Mac decided to crash four times every hour, with no known source of the fault found yet...) is to look at the checksums of the files - so compare SHA1 and MD5 for instance; this will determine if two files are actually identical.

                                                               

                                                              With this, it is then trivial, if not a lengthy process, to check that each image from Aperture is in Lightroom.

                                                               

                                                              This will work for absolute duplicates; Another addition I would like to add is to examine file data, like the EXIF tags, maybe even histogram comparison, as then we can tell if the subjects are similar, but the file's been resized.

                                                               

                                                              (better press reply before this thing dies on me again!)

                                                              • 28. Re: Unlogged Missing Photos After Import From Aperture
                                                                photo-enthusiast Level 1

                                                                Hope you get your system issues worked out.

                                                                 

                                                                I ended up exporting from managed to referenced.  First, I collapsed all my nested folders to one folder in Aperture, then exported using a custom folder format FOLDER/PROJECT, where the folder was the year, or YEAR-JAPAN, where I had several nested projects.  Fast forward after conversion, I then re-nested the folder structure.

                                                                 

                                                                Having exported all the photos from managed to referenced, I then used all the normal gymnastics within Aperture to make sure all the images were exported and "online".  About 15 were not, so I needed to resolve those discrepancies, which included duplicate references, primarily.  Once exported, I generated previews.  You're not dealing with previews, so I'll spare you the even lengthier discussion about how I needed to account for them.  Given all the files are external, it's now easier to determine what files were not imported into Lightroom.  I didn't have a discrepancy.

                                                                 

                                                                DO make sure you do not have duplicate keywords!  I thought I had, but due to the nested nature of keywords, I did have some duplicate references such as "portrait", where I had used it to flag the original photo orientation, as well as Aperture's stock keyword.  Makes a mess of things later.

                                                                 

                                                                Despite tests, even running through the entire library previously, I ended up running out of memory AND having issues with GPS.  I ended up using HoudahGeo to create sidecar files with GPS from Aperture data before import.  After trying everything else, I used an obscure suggestion to run the import on a build of OS X 10.9.4 instead of 10.10.3.  That seemed to do the trick.

                                                                 

                                                                Lots to clean up later, but I may just leave it as is.  Wish that faces were exportable now that Lightroom supports faces, but I won't hold my breath.  Seems Aperture plug-in development at Adobe is concluded.  Have tens of thousands of assignments in Aperture.

                                                                Also sad to lose settings, but at least I was able to keyword each and every setting before exporting.

                                                                 

                                                                Nearly all the work must be done in Aperture before transition.  Just shows you how flexible and versatile Aperture is even today.

                                                                 

                                                                Still curious as to how you're going to handle your conversion, so please keep posting!

                                                                • 29. Re: Unlogged Missing Photos After Import From Aperture
                                                                  ClubmanPlus850 Level 1

                                                                  Wow! i think this is a new record for me in gaps between posting...!

                                                                   

                                                                  in the meatime ive been laid off from work, moved house, gone on parental leave, broken my back and screwed my mac!

                                                                   

                                                                  I am DEFINITELY! going to finish somehow (I have a few more applications where a similar tool will be useful). It's just a matter of when I can find the time. Though I am scared I won't have a data set to test on, as I will probably reinstall my Mac from ground up in a few days, and won't bother installing Aperture again - might need to build a virtual machine for testing.

                                                                   

                                                                  I'm going to mark this conversation/question as answered, as we both seem to have solved our problems. When I have a utility working, i'll post an update here.

                                                                  • 30. Re: Unlogged Missing Photos After Import From Aperture
                                                                    photo-enthusiast Level 1

                                                                    So sorry to hear.  Quite a few life event changes in such a short period of time.  Sometimes, a disorganized, partially processed photo library is a headache, but there are other events that take precedence, sometimes!  Best wishes as you continue to sort things out.