Retrieve Excel image files

[Paul Mayall]

Hi everybody, I need to have a record of all images I have on Alamy as I have lost track and don't want to upload images I already have on Alamy.

 

Is there a way of retrieving my files via excel, I don't need the images just information.

 

Thank you,

Paul.

[M.Chapman]

4 hours ago, Paul Mayall said:

Hi everybody, I need to have a record of all images I have on Alamy as I have lost track and don't want to upload images I already have on Alamy.

 

Is there a way of retrieving my files via excel, I don't need the images just information.

 

Thank you,

Paul.

Open Alamy Image Manager (AIM). Click on the Blue "Send me my data button".

Alamy will then prepare a file containing data about al your images.

Sometimes it's very quick, sometimes it takes longer.

When the blue button changes to "Download" data it's ready for download (you may need to refresh your AIM page)

Download the data

The data is supplied in a CSV format file which can be opened in Excel

One of the columns in the opened spreadsheet contains the filenames of the files you uploaded to Alamy.

 

Mark

 

 

Edited November 18, 2021 by M.Chapman

[wiskerke]

Is there a simple way of adding the thumbnails to the spreadsheet?

 

wim

[M.Chapman]

9 minutes ago, wiskerke said:

Is there a simple way of adding the thumbnails to the spreadsheet?

 

wim

I suppose a bit of Excel VBA could do it. I've used VBA to load a single jpg into Excel to analyse the colours, but matching and finding all the jpgs with each entry in the Alamy data is not something I know how to do. A bit of Googling might give the answer?

 

Mark

[Allan Bell]

4 minutes ago, M.Chapman said:

I suppose a bit of Excel VBA could do it. I've used VBA to load a single jpg into Excel to analyse the colours, but matching and finding all the jpgs with each entry in the Alamy data is not something I know how to do. A bit of Googling might give the answer?

 

Mark

 

It would be most useful if Alamy could supply the thumbnails with the data on the Excel file. Probable easier than for us to try to alter the file.

 

I did start to add thumbs myself one time but had to give up as it was taking too long.

 

Allan

 

[Harry Harrison]

I'm not about to try and do it, indeed not capable of doing it, but in theory it would be possible to create a Filemaker database with your thumbnails and then import the downloaded csv into that. However that wouldn't work for me as I have duplicate filenames caused by uploading from a variety of similar cameras so the original filename would not work as the key. I would have a similar problem to fix manually if I started to use the Alamy Lightroom Bridge plugin but from that point on I'd be OK because then the unique Alamy ID can be used as the key and that is downloaded for every subsequent upload.

[wiskerke]

The problem is the downloading of the thumbnails I guess. One by one is a bit tedious. Doable for me, but prohibitive for people with a huge collection.

The thumbs would have the Alamy ref # so that's solved.

 

wim

[sb photos]

2 hours ago, Harry Harrison said:

I'm not about to try and do it, indeed not capable of doing it, but in theory it would be possible to create a Filemaker database with your thumbnails and then import the downloaded csv into that. However that wouldn't work for me as I have duplicate filenames caused by uploading from a variety of similar cameras so the original filename would not work as the key. I would have a similar problem to fix manually if I started to use the Alamy Lightroom Bridge plugin but from that point on I'd be OK because then the unique Alamy ID can be used as the key and that is downloaded for every subsequent upload.

Re multiple similar camera’s, could you not within the camera’s menus change the file names prefix to match the camera it originated from? I do this with 3 Nikon’s, their file names are prefixed CA1, CA2 and CA3 so I know exactly which camera generated the files. A friend and Alamy contributor from long ago used to track all his images sold direct or through agencies in a FileMaker database once the film was digitised. He carried on using when he went digital with Canon. It held thumbnails and all he needed. It ran on an old eMac then a 20” iMac G5. 

[Harry Harrison]

9 minutes ago, sb photos said:

Re multiple similar camera’s, could you not within the camera’s menus change the file names prefix to match the camera it originated from? I do this with 3 Nikon’s, their file names are prefixed CA1, CA2 and CA3 so I know exactly which camera generated the files. A friend and Alamy contributor from long ago used to track all his images sold direct or through agencies in a FileMaker database once the film was digitised. He carried on using when he went digital with Canon. It held thumbnails and all he needed. It ran on an old eMac then a 20” iMac G5. 

Yes I could but it's never really mattered to me Steve, though as I mentioned if I went to Alamy Lightroom Bridge I would probably be kicking myself. I use Lightroom and Lightroom uses a combination of filename, file size and date shot (I think) to test for uniqueness so duplicate filenames don't cause me any problems. 

 

Good to hear about your friend doing his Filemaker database, it's powerful once you've learnt a bit about it but it's not exactly intuitive. I think the problem for Alamy including thumbnails might be that you'd be talking about a lot more processing and larger download files, csvs wouldn't work any more.

[Harry Harrison]

50 minutes ago, wiskerke said:

The problem is the downloading of the thumbnails I guess. One by one is a bit tedious. Doable for me, but prohibitive for people with a huge collection.

The thumbs would have the Alamy ref # so that's solved.

Right, yes, I was thinking about people matching the data to the files already on their computer, pre-upload. Somehow can't see Alamy facilitating this since they still haven't got around to changing the 'may take 24hrs' next to the csv download.

[Vincent Lowe]

I did this with IMatch.  It has a 'CSV Import Module' which enabled me to import the Alamy refs and add them to my IMatch database, which already had the thumbnails of course - over 20K files at the time.  Not quite that simple as there was some errors in the CSV file caused by some spurious missing delimiter commas.  Fortunately the IMatch importer stops when it sees an error so nothing gets screwed up.  In the end I did it in batches until I narrowed it down to isolate the errors.  Even so it only took a few hours spread over a couple of days.

 

Below is a screen shot of the IMatch file display.  You'll see I've got the submission reference number and date, then the Alamy image reference number.  Below that exposure and time from the image exif data.  Below that is displayed the total number of keywords, automatically calculated by IMatch, then just a note to remind me whether or not I've done the supertags.  On the last line the 'Caption Limit' changes to OVER! if I've entered more than 150 characters and finally the 'Additional Info' is a duplicate of what is entered into that same optional field in Image Manager.  In IMatch it's entered into an 'Annotation' which is attached to the file.

 

Edited November 18, 2021 by Vincent Lowe
Spelling error

[wiskerke]

For me there's an easy way out also: I do have the full size images with their correct filenames. So it's relatively easy to generate my own thumbnails and import those because one of the fields/cells already has that filename, just not the Alamy id, but my own. There will be the occasional hiccup like 2 or 3 re-visited editions of the same file, but totally workable.

 

wim

[M.Chapman]

2 hours ago, wiskerke said:

The problem is the downloading of the thumbnails I guess. One by one is a bit tedious. Doable for me, but prohibitive for people with a huge collection.

The thumbs would have the Alamy ref # so that's solved.

 

wim

Mmm... I hadn't thought of accessing the thumbnails on Alamy (I was thinking of having to search for them on my hard-disk).

 

Looking at the URLs of the Alamy thumbnails (on customer search page results) it looks easy to generate the full URL of each thumbnail from the Alamy Image ref and the image caption, which could be done in Excel. So that maybe gives another way forward. Add a new column to the downloaded Excel spreadsheet and populate it with the automatically generated thumbnail URLs. Then either, create some HTML (in another column in Excel) to retrieve the images using a browser? or do it in Excel somehow? Alternatively it should be easy to create a "report" in HTML which would show all ones images and Alamy refs and original filenames etc. by opening the HTML (generated from Excel) in a web-browser.

 

Mark

[wiskerke]

1 hour ago, M.Chapman said:

Mmm... I hadn't thought of accessing the thumbnails on Alamy (I was thinking of having to search for them on my hard-disk).

 

Looking at the URLs of the Alamy thumbnails (on customer search page results) it looks easy to generate the full URL of each thumbnail from the Alamy Image ref and the image caption, which could be done in Excel. So that maybe gives another way forward. Add a new column to the downloaded Excel spreadsheet and populate it with the automatically generated thumbnail URLs. Then either, create some HTML (in another column in Excel) to retrieve the images using a browser? or do it in Excel somehow? Alternatively it should be easy to create a "report" in HTML which would show all ones images and Alamy refs and original filenames etc. by opening the HTML (generated from Excel) in a web-browser.

 

Mark

 

Haha you may end up with a sort of AIM page without the editing. 😁

But yeah something like that has crossed my mind also.

That AIM gave me an idea though. In Firefox there are these very handy tools. So in AIM I scrolled down a bit and clicked download all in the Media tab in Page Info. That gave me 553 slightly large thumbnails of around 450x350 it seems.

Certainly worth exploring some more!

 

wim

 

[M.Chapman]

I downloaded my Alamy data CSV and opened in Excel. Then did some global search and replace on the Caption column to remove any of the non-alphanumeric characters (e.g. !@£$%^&*()-_+={}[]:;"'|\~`<>/) I'd used in my captions, and replaced double-spaces with single spaces (repeat until all gone) and then replaced the remaining single spaces with "-". Then added a new Excel column containing the following form of equation (example is from row 2) to generate the URLs of the Alamy thumbnails.

 

=CONCATENATE("https://C1.alamy.com/thumbs/",B2,"/",LOWER(C2),"-",B2,".jpg")

 

NB. Column B contains the Alamy Image Ref and Column C contains the modified captions.

 

Here's an example of the URL generated by the Excel concatenate command.

 

https://C1.alamy.com/thumbs/2H4MNEJ/blaven-blabheinn-bla-bheinn-and-clach-glas-mountains-in-the-black-cuillins-of-skye-with-allt-na-dunaiche-river-in-foreground-skye-scotland-uk-2H4MNEJ.jpg

 

Only minor wrinkle is where I had left a space on the end of my caption, after the concatenation command I ended up with "--" so I had to global replace "--" with "-" as a final tweak.

 

The URLs seem OK, (although I haven't checked very many).

 

Now it just needs a way to get Excel to load the images from each URL into the adjacent cells.... Looks like a bit of VBA or Kutoools can do this, quite a few examples on-line

 

https://www.extendoffice.com/documents/excel/4212-excel-insert-image-from-url.html

https://superuser.com/questions/940861/how-can-i-display-a-url-as-an-image-in-an-excel-cell

https://techcommunity.microsoft.com/t5/excel/convert-image-url-to-actual-image-in-excel/m-p/309020

 

Alternatively, it looks like Google Sheets has a command to do it directly (no VBA needed). See bottom of page.

https://stackoverflow.com/questions/6312710/how-to-get-images-to-appear-in-excel-given-image-url

 

Mark

 

Edited November 18, 2021 by M.Chapman

[Harry Harrison]

I can see how useful this could be but I'd never thought about doing it and I don't use Excel enough to know how to go about it. However in Filemaker it is extremely easy to open the csv directly from Filemaker and create a basic database. Then create a new field to concatenate "www.alamy.com/" with the ImageRef value, then create a button on the layout to open that URL to go to the image, if you feel the need to check it of course, obviously you need to be online. In fact what I actually have is a 'master' Filemaker file that has various stuff to test for caption length, number of keywords & supertags etc. and so I just purge it and import the new csv into that, and now, thanks to this thread, I can also go directly to the image.

 

And I thought I'd wasted my day, phew, just managed to get something done in time. I can now watch the tennis on Amazon Prime with a clear conscience.

Edited November 18, 2021 by Harry Harrison