Jun 07, 2017 12:35 PM
I know a lot of you including myself on the forums wish there was a way to attach dropbox image links so you can maintain your digital assets locally such as this post:
Or even renaming uploaded files (in bulk though)
Or even download bulk image assets (which I had posted recommended Extreme picture finder downloader)
Or downloading an entire snapshot of data
Or do advanced image batch processing locally on whatever program you use for editing (Photoshop, ifranview, etc) while using airtable for asset staging
Or using something like http://excel-image-assistant.com/ to create spreadsheets with embedded images / more custom flexibility in printing forms
Well this is a solution for all of those problems! And much more! Because we’re going to bring the power of Excel VBA and windows API to do the work for us (Sorry Mac users this may not apply…)
Basically, your going to have the following data in your airtable
Here’s a data sample of it
Then you run a macro. The end result to look like this on your computer:
Where one row of data in airtable = one picture
And each picture is based off the name in the first column
Before I get into the excel VBA macro, I should highlight what things I tried out before hand and why they don’t work
To do this properly, you need to have two different things
I tested out practically almost every software program out there for bulk renaming (Bulk rename utility, Advanced Renamer, Renamer) and bulk downloading (bulk image downloader, ExtremePictureFinder) but the problem is the following:
Say we go back to the original data. With pictures of dogs and stuff. Say you downloaded the CSV for that sample data
The data looks like this in excel
If you look at column B, that URL (HTTPS) is the image server where we’re going to download our image assets
BUT, with any of those above programs (bulk renaming / downloading) is that it doesn’t really let you save duplicated copies, because what’s going to happen is on your computer you will have 3 files with the same name, making renaming VERY difficult if not impossible.
Not only that you have to download , setup / run multiple programs which is a lot of work
Trust me here in that I’ve tested this out thoroughly. There’s only 2 ways to accomlpish this problem:
Most people won’t have python installed on their computer, myself included, but everyone knows how to use excel. Getting access to excel VBA and running a macro isn’t all that difficult, and I will explain how
#The solution
What does the solution even look like?
That all it is. It literally did the following all in 10 seconds
And this is the result!
When you look at the data you’ll see the following folder information:
When you look at the excel CSV file after, you’ll see the following columns of data
You’ll also see a file called “newcurl.bat” . Its just a random default name for a windows batch file I gave it. It does all the duplicating + renaming of assets. It basically has all of column D’s files in there
Go my github page where I wrote macro. I put the latest iteration there
The file is called “DataStager.VBS”
ALT+F11
airtableCleaner()
ALTERNATIVELY
^ download link above
You can use this script on any image link that doesn’t require a proxy (e.g. any website that highly discourages webscraping)
Basically
If you have a program that only integrates with an excel file (which is mostly everything) and not touch airtables API, well then this is for you
Downloading and renaming all your image assets could be useful in a number of ways.
Those are just some of the examples on why you would want to use airtable to stage data + image assets, and then bulk process download later to integrate in whatever platform you want
You can use it to cross collaborate with remote teams this way as well, each person can easily download all image assets that they need, etc
I wrote the excel VBA code rather poorly here (But it works) and there’s still lots of room for improvement. I put it all on the issues section in github
you can also recommend features that you want (like multi image support for one cell)
Mac users unfortunately this will probably not working and I don’t have any intentions of making a MacOS equivalent (run bootcamp for windows instead)
A list of improvements that I might look into found here: Issues · vincentntang/AirtableExcelVBAmacro · GitHub
Examples include PDF download support, multiple image URLs per cell support, proxy support, etc
Since this goes from (airtable → excel), you can just go the other way (excel→airtable) with my BULK IMAGE UPLOADER here
Using both this excel VBA macro + the other macro in the link, you have full control over reproducing any airtable you want and having a solid backup of all your assets
Not only by having the ability for your data to go both ways 100%, you can tap into powerful ways to transform data using excel, excel VBA, and excel plugins
Not to mention the ability to bulk process images as well (EXIF data remover, Resize images, apply backgrounds, watermark, etc) and then reupload it → use airtables API to push it into a wordpress site for instance.
The applications of using both macros I laid out here, + Airtable’s API gives pretty much the flexibility of doing anything you want really
this is a bulk downloader and renamer
On your airtable, have the following data format
airtable.com → find your table → “Download as CSV”
Download this excel macro enabled workbook below
Paste the contents of the airtable’s CSV into sheet 1 of the “AirtableBulkDownloaderRenamer.xlsm” workbook
On excel ribbon menu → Home → view → view macro → airtableCleaner() → run
You should now have all your image assets downloaded and renamed.
Sep 19, 2017 12:19 PM
Thanks for typing all that out! Looks promising, but I need this feature for audio.
Does this only work with image files, or can it work for .mp3s too?
Sep 20, 2017 06:54 AM
Nope I only had it set for single image files in one cell. Although it might still work with PDFs and mp3s, I can’t say for sure since the URL image download part with the excel VBA macro is rather complex (compared to something like python)
do you think you can give it a try and let me know? (download mp3 files)
where
If not I’ll look into adding mp3 support
Mar 13, 2018 10:07 PM
Is there a way to not rename the file?
I have the filename precisely the way that I need it, however when I download from the link and not from my air table view there are about 21 characters in front of the name. The renaming has definitely helped in this regard, but is there a way to have it renamed the original name. Or remove the 21 characters in front?
Thanks and thank you for this as it has saved me a bunch of time.
Mar 15, 2018 12:16 PM
The problem I had with airtable’s file naming convention is sometimes I would just download an image off google. Then dump it straight into airtable. Half my images uploaded to airtable were named “download.jpg” or “image.jpg”, or even “image(1).jpg”.
So when I downloaded the CSV off airtable, it would look like this:
You could use the original name instead as well. The only issue is the above screenshot, all my originalfile names are “image.png” though when uploaded to airtable. You could do a bit of excel cleanup to produce something like this instead below (select column B → data → text-to-columns → filter by “space”) and (find paranthesis replace with nothing)
Ideally, before you run the script though, column A should be all unique values. That’s why I suggest using the primaryKey in the table (first column in an airtable’s table) as column A. None of my original image image file names are unique half the time so this is why I don’t use the original filename during renaming. Rather I just toss it out
Do you have a screenshot of what your excel csv file looked like when you downloaded it off airtable? Also, what the folder of images looked like after running the script. I’m wondering what these 21 characters in front are
Mar 15, 2018 06:31 PM
Ahhh,I totally forgot about text to columns. That would solve it to pull out the original file name to run your script.
This is great cause I was using the title as the new file name, but sometimes there were duplicates of the title so it caused issues.
As the filenames uploaded to Airtable are all unique. This method solves the download problem.
the 21 characters I was speaking of was the Airtable identifier in front of the file name.
As Text to columns pulled out the original file name, I don’t need to go the 21 character route.
Thanks a lot for your help!
Apr 08, 2018 03:39 PM
I had to use my macroscript again and I noticed a great number of bugs in it that I didn’t catch earlier.
I made a Version2 of the macro script
The problem in my original script was the following
name (stuff).png
with paranthesis in the name, the macro would failimage.image.png
failed as well%20
that didn’t play nicely with command promptIts still written pretty ugly but it works with less bugs now :slightly_smiling_face: .
You can read about my bugfixes on my github page, its issue 14 and 15
Here’s the new less bugprone file :).
Also if you here’s the source file if your worried about viruses and want to copy it into your workbook yourself :slightly_smiling_face:
Jan 03, 2019 05:58 PM
thank you for the file, it works wonders, but I had problems with downloads from googleusercontent like this:
YL_O-SVgD_qrf3yheGLBDMNSI16aeNq-T3hIPGeBz4Wqy_EIIzleGu-19e1azOgF4sQ5CPplA9drZc_krGGFSYUoGvz63HnXuQ=w1920-h1080
it won’t rename (meaning it also keeps with no extension)… I also was unable to rename to .jpg… I dont see the same graphic interface you see, where is import CSV?
Sep 04, 2019 01:01 PM
I can’t get the RENAMER part of the file to work. I’ve tried both versions a couple times and each time the file is named however it is in Airtable. I’ve circled examples of how they are named once they are downloaded in the picture.
Also, the files save in the same folder that the AirtableBulkDownloaderRenamer V2 file is saved, not the Batch1 subfolder that VBA creates. I don’t know if that is part of the problem.
Other info: Most attachments are photos taken in the attachment field of the airtable app. Very few other uploads.
Thank you for your work on the file. If it works, it’ll save me so much time.
Mar 01, 2020 02:42 PM
Any update on new ways to download/backup images when there are multiple images in a field?
Mar 09, 2020 09:25 AM
To fix the issue with images no longer renaming properly and being put in the new folder directory. Try replacing this code:
'Image downloader to source folder
Call dlStaplesImages
'Make the batch files using row data col D
Call ExportRangetoBatch
With this new code:
'Image downloader to source folder
Call dlStaplesImages
'Fix Column D urls
Columns("D:D").Select
Selection.Replace What:=".attachments*/*/*/", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Make the batch files using row data col D
Call ExportRangetoBatch
That fixes the issue for me. I didn’t dig into it deeply but maybe the download urls changed slightly. The urls in the batch file no longer match Column D so I pulled out the offending part of the url before doing the rename.
Jul 05, 2020 08:49 AM
For less technical people, we’ve built an extension that allows you to download all your attachments as a one zip file with a few clicks.
Jan 22, 2021 04:56 AM
I was getting errors with the VBA solution (both v1 and v2) so I’ve put together a simple python script to download all attachments.
The name in the first column is used as a name for corresponding attachment. File type of attachment is preserved. The names in the first column should to be unique otherwise they will get overwritten if it’s the same file type.
Your db:
Name | attachment
Bob | 4as3fd523sf.jpg
John | screenclip.png
Script downloads these files:
Bob.jpg
John.png
^^Feel free to fork and make changes
Feb 02, 2022 04:08 PM
Hi, thank you for the guide, I try to use it for a little different purpose, but I do have 2 columns with names and url’s. I run the macro, it creates the folder. Says downloaded successfully but the folder is empty. The image links are direct non protected links. Could you guide me on what am I doing wrong :slightly_smiling_face:
Feb 02, 2022 10:31 PM
some time ago i did bulk attachment downloader - it downloads all attachment in a base adding 8-digit code to filename from link which can be used then for uploading, if needed.
Should be saved as .vbs Backup should be renamed to backup.csv and exist in the same folder
Option Explicit
Dim fs,objTextFile,arrString,attach,attachEs,pos,pos2,arrStr,fName,fExt
Dim fnameWithCode,eightDigCode,downloadLink
set fs=CreateObject("Scripting.FileSystemObject")
set objTextFile = fs.OpenTextFile("Backup.csv")
Do while NOT objTextFile.AtEndOfStream
arrStr = split(objTextFile.ReadLine,",")
attachEs=Filter(arrStr,"https://dl.airtable.com")
For each attach in attachEs
pos=InStr(attach,"(https") '// extract filename
fName=Trim(Left(attach,pos-6))
fName=Replace(fName,Chr(34),"_") '// remove bad chare from fname
fName=Replace(fName,".","")
fExt=Mid(attach,pos-5,4)
pos2=InStrRev(attach,"/")
eightDigCode=Mid(attach,pos2-8,8)
fnameWithCode=fName&"("&eightDigCode&")"&fExt
downloadLink=Mid(attach,pos+1,Len(attach)-pos-1)
Call Download(fnameWithCode,downloadLink)
Wscript.Sleep 1000
Next
Loop
objTextFile.Close
set objTextFile = Nothing
set fs = Nothing
Sub Download(filename,link)
dim xHttp: Set xHttp = createobject("Microsoft.XMLHTTP")
dim bStrm: Set bStrm = createobject("Adodb.Stream")
xHttp.Open "GET", link, False
xHttp.Send
with bStrm
.type = 1 '//binary
.open
.write xHttp.responseBody
.savetofile filename, 2 '//overwrite
end with
End Sub
Apr 22, 2022 12:49 PM
This is a terrific idea. However, when attempting to use the macro, it encounters a problem with the subfolder name code. Error says “Run time 52” and the debugger goes to " If Dir(strDir, vbDirectory) = “” Then" It appears to be line 68 in your macro.
I have tried using a simple subfolder name as you suggest, ie, batches and also tried using the full path. Neither works. Both version one and version 2 of your macro get stuck at the same place. I used both my own macro-enabled spreadsheet and your downloaded spreadsheet.
I am using windows 10 (10.0.19044 Build 19044) and Excel build 2203.
Can you provide any suggestions for that line of code?