All Collections
Misc
Guides leveraging external tools
Renaming photos with Sheets and batch scripting
Renaming photos with Sheets and batch scripting
Use Google Sheets to create a Windows .bat or Mac/Linux .sh script to rename your exported Fulcrum photos.
Joe Larson avatar
Written by Joe Larson
Updated over a week ago

Introduction

This is a guide to an alternative, non-programmatic method to renaming exported photos from Fulcrum. It uses freely available tools and should work for users across all operating system platforms. While this guide uses Google Sheets, the process is likely similar for Microsoft Office Excel and LibreOffice Calc.

Getting Started

1. To begin, we will need a CSV export from Fulcrum including photos. Import your CSV into a blank Sheet.

2. If your Fulcrum app contains multiple photos per photo-field, you will need to prepare the Sheet accordingly. If not, please skip this step. 

Use Sheets' Split text to columns functionality to create separate columns for each photo ID (both Excel and Calc have similar functionality). You may have to do this more than once, if you have more than one photo-field in your app.

3. Insert columns to the right of your photo-field columns. Give them a name in the first row header; you can use the corresponding columns' name and append the suffix _rename to it, like below.

4. Now we will use a formula that will build a new filename for the photos by referencing a couple other columns in our exported data. This particular formula is specific for a demonstration project, but hopefully it can serve as a model for you to achieve your own similar task.

In plain language, the formula is:

  • If photo-field 1 is empty, leave rename-field cell as blank.

  • If not empty, start building the rename-filename from the created_at column.

  • Include a custom ID from the cgid column. If it's blank, insert the text undefined instead.

  • Also include a custom suffix _Post1.jpg. Post1 would be my first photo after the project work was completed, Post2 would be the second.

  • Example: 20161005_2211_73084_Post1.jpg 

=IF(ISBLANK(fulcrum-photo-1),"",(CONCATENATE(SUBSTITUTE(MID($created-at,1,10),"-",""),"_",SUBSTITUTE(MID($created-at,12,5),":",""),IF(ISBLANK($cgid),"_undefined",CONCATENATE("_",$cgid)),"_Post1.jpg")))

Below, I have already applied the formula to my first rename-field column and am applying it to my second column (changing Post1 to Post2). I did a quick test on a few rows - looked good, so I pasted it into the rest of the column. I also checked to make sure my custom ID field was inserted when appropriate. 

5. This step is where we will build our rename commands. Depending on the operating system of your computer - you will use ren for Windows OR mv for Mac/Linux. I will use mv  in this example. Again, we will use a formula to check for empty values and to build our command.

=CONCATENATE(IF(ISBLANK($fulcrum-photo-1),"",CONCATENATE("mv ",($fulcrum-photo-1),".jpg ")),$fulcrum-photo-1-rename, CHAR(10) ,IF(ISBLANK($fulcrum-photo-2),"",CONCATENATE("mv ",($fulcrum-photo-2),".jpg ")),$fulcrum-photo-2-rename)

In plain language, the formula is:

  • If photo-field 1 is empty, leave cell as blank.

  • If not empty, suffix the photo-ID with .jpg , add a space in between, then the rename-filename, and finally insert a line break CHAR(10) .

  • Repeat as necessary, exclude line break from final column.

6. Next we will copy the rename commands into a text editor for cleanup. Copy the cells containing your commands from your column, except the first row header.

Paste this into a text editor such as Sublime, Atom, or Notepad++ (something that includes Find & Replace with support for RegEx) and save your the file, example: rename-photos.sh (Mac/Linux) or rename-photos.bat (Windows).

7. Let's cleanup your script. We are going to use Find & Replace with RegEx to tidy up each line containing the rename commands. There will be 3 parts:

  1. Remove the double-quotes at the beginning of each line by finding ^" and replacing it with nothing.

  2. Remove the double-quotes at the end of each line by finding "$ and replacing it with nothing.

  3. Remove blank lines by finding ^(?:[\t ]*(?:\r?\n|\r))+ and replacing it with nothing.

In conclusion, this guide has shown you how to work with spreadsheet formulas to modify filenames and build new commands for a script. Find & Replace was also used (with RegEx) to cleanup text. Hopefully this guide provides an example of an alternative method of building a script for batch processing files.

8. Run the script.
  Windows: To run a batch file (example: rename-photos.bat ) from the Command Prompt, open Command Prompt and enter into the directory containing the file and type the name of the batch file and press enter. To run a batch file from the Windows Desktop, it runs like any other executable file by double-clicking the file. However, because a batch file runs in a command prompt, it immediately exits when completed - so you may only briefly see a black box for a second.

  Mac/Linux: To run a script (example: rename-photos.sh ) from a Mac/Linux terminal, open the terminal and navigate to the directory containing the file and type sh rename-photos.sh.

Did this answer your question?