Bulk Editing Records

[Vincent_Tang]

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:

Choose Dropbox or Google Drive for attachment storage Product Suggestions

I would love for my attachments to get sued in my Dropbox. This would allow me to use Airtable as a digital asset manager

Or even renaming uploaded files (in bulk though)

Rename Uploaded Files Product Suggestions

I think it would be great to be able to update the names of files once they’re uploaded; this is especially needed when linking images or files from online instead of uploading since you have no control on the naming structure for those external files. I’ve recently found that searching a base includes what’s in the filename and this is becoming problematic since I often link to images on the web that have a lot of text in the filename that I would prefer not to be there. I think the ability t…

Or even download bulk image assets (which I had posted recommended Extreme picture finder downloader)

Ability to View Uploaded files/mass download attachments Product Suggestions

I would love to have a place where I can manage all the files that are attached to my base. Similarly, I could then attach the same document/image to multiple records without having to re-upload every time.

Or downloading an entire snapshot of data

Download snapshots? Product Suggestions

I’m finding that Airtable does so many things right, and I want to use it in my nonprofit for client/case/volunteer management, but I’m hesitant to throw all of our eggs in that basket without the ability to locally back up the data—at least once in awhile—including attachments. It would be very valuable to us to be able to download the snapshots, and then restore from them later, if needed. (Note: I’m assuming that snapshots include attachments…that would be key)

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

[SOLVED] Ability to Design a Page for Printing Product Suggestions

Im not sure where Airtable is headed. But id be happy to print a customer a nice Paper with all the Products he ordered. I guess id like to make different form views like in Filemaker.

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…)

---

---

What exactly is the solution…?

Basically, your going to have the following data in your airtable

• Col 1: PrimaryKey
• Col 2: ONE image link attachment

Here’s a data sample of it

Airtable

Grid view - Airtable

Explore the "Grid" view on Airtable.

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

Problem with current software out there to do this (you can skip this)

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

• A bulk renamer
• A bulk downloader

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:

1. Either with python
2. Excel VBA

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

• Asks if you want to run the macro and for a subfolder name
• Iterated through each row
• Extracted out the airtable download link
• Fixed any image naming problems (e.g. if the image had in name)
• Downloaded each image link
• Created a windows .bat file to do all the renaming [this is for windows only sorry :frowning: ]
• Showed whether the file downloaded successfully
• Duplicated the new image names into a subfolder
• Show whether the images successfully renamed into subfolder in command prompt

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

• Col A: Original Column 1 in airtable
• Col B: Airtables Image link
• Col C: Fixed formatting of the airtable’s image link
• Col 😧 Batch file commands for duplicating image
• Col E: Whether the image downloaded successfully or not

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

---

---

Cool where can I get the script and how do I run it?

GitHub

GitHub - vincentntang/AirtableExcelVBAmacro: 🔨 Macro for bulk downloading +...

🔨 Macro for bulk downloading + renaming images off a database - GitHub - vincentntang/AirtableExcelVBAmacro: 🔨 Macro for bulk downloading + renaming images off a database

Go my github page where I wrote macro. I put the latest iteration there

The file is called “DataStager.VBS”

1. Copy the contents of it
2. Download and Open your excel file from Airtable if you haven’t (don’t copypaste cells, use the builtin -download CSV button)
3. Press ALT+F11
4. Insert code into a module
5. Run the submodule airtableCleaner()

ALTERNATIVELY

1. just download my macro enabled workbook (don’t worry there’s no viruses on it)
2. Paste data in the first sheet (exactly the data you’d get from download as CSV from airtable)
3. Home → view → view macro → airtableCleaner() → run

Dropbox

AirtableBulkDownloaderRenamer.xlsm

Shared with Dropbox

^ download link above

Image links where I can use this script?

You can use this script on any image link that doesn’t require a proxy (e.g. any website that highly discourages webscraping)

Basically

• Anything on imgur.com works (which uses AmazonS3) works
• Anything hosted by AmazonS3 works
• Anything on reddit would work as well
• Anything on airtable.com (which uses AmazonS3) works
• Flickr, GoogleImages, etc

Applications of this excel macro

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.

• Ecommerce FTP staging and uploading
• Bulk uploading to ANKI flash cards for studying
• Digitally managing assets (Digital Photography, photoshop, etc)
• Catalog design using AdobeIndesign + Database integrations
• Customized mailing templates using Microsoft word, etc
• Customized excel file using http://excel-image-assistant.com/ to port in the images after
• Bulk import contacts + image links into an email client
• ERP (Enterprise resource planning) , PoS (point of sales system), etc
• Push image data into an actual database (MySQL) for storage efficiency
• and much more…

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

Support for newer features / Issues?

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

Other useful scripts

Since this goes from (airtable → excel), you can just go the other way (excel→airtable) with my BULK IMAGE UPLOADER here

(BULK IMAGE UPLOADER) KB+Mouse Macro to reupload your ImageURLS / LocalFilePath Images to Airtable Show and tell

There wasn’t a bulk image uploader with airtable so I just made my own autohotkey macro to do this. There’s a substantial amount of users on this forum requesting a feature such as this, myself included, but for now this macro will do Basically, I have a bunch of imgur.com URL’s already and wanted to reupload it into airtable to get the image thumbnail preview This is what it looks like in action: (I am not pressing any buttons here asides the “Play Button”, this is macro doing all the work) …

In conclusion…

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

---

---

TL ; DR (Too Long , Didn’t Read)

this is a bulk downloader and renamer

On your airtable, have the following data format

• Column 1 = new image name to rename to
• Column 2 = ONE image attachment link

airtable.com → find your table → “Download as CSV”

Download this excel macro enabled workbook below

Dropbox

AirtableBulkDownloaderRenamer.xlsm

Shared with Dropbox

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.

[Shelly_Fearer]

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?

[Vincent_Tang]

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

• column 1, per cell = the mp3 name you want to use
• column 2, per cell = one mp3 file

If not I’ll look into adding mp3 support

[Phil_Lee]

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.

[Vincent_Tang]

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

Phil_Lee:

Or remove the 21 characters in front?

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

[Phil_Lee]

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!

[Vincent_Tang]

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

• Whenever you uploaded an image to airtable that was something like name (stuff).png with paranthesis in the name, the macro would fail
• Image names like image.image.png failed as well
• If you had any spaces in the image name uploaded, it would be encoded with a %20 that didn’t play nicely with command prompt

Its 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

GitHub

vincentntang/AirtableExcelVBAmacro

Contribute to vincentntang/AirtableExcelVBAmacro development by creating an account on GitHub.

Here’s the new less bugprone file :).

Dropbox

AirtableBulkDownloaderRenamer V2.xlsm

Shared with Dropbox

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:

github.com

vincentntang/AirtableExcelVBAmacro/blob/master/DataStager.vbs

Option Explicit

' https://stackoverflow.com/questions/31359682/with-excel-vba-save-web-image-to-disk/31360105#31360105, URL download to file
#If VBA7 And Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" ( _
        ByVal pCaller As LongPtr, _
        ByVal szURL As String, _
        ByVal szFileName As String, _
        ByVal dwReserved As LongPtr, _
        ByVal lpfnCB As LongPtr _
      ) As Long
    Private Declare PtrSafe Function DeleteUrlCacheEntry Lib "Wininet.dll" _
      Alias "DeleteUrlCacheEntryA" ( _
        ByVal lpszUrlName As String _
      ) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" ( _
        ByVal pCaller As Long, _

This file has been truncated. show original

[Joh_Mabe]

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?

[Dallas_Jones]

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.

[Sal_Ohcin]

Any update on new ways to download/backup images when there are multiple images in a field?