Add Text to Pictures in Batch dynamically from Excel and CSV files

I guess that’s familiar to you: You have finished a shooting, edited your pictures with Lightroom and before publishing the samples in the internet, you are going to resize and rename the photos and watermark them. You hopefully use bulkWaterMark therefore, because putting your photos into the app to add text to pictures in batch is a no-brainer without any effort. This is probably the most typical use case of bulkWaterMark.

Like a serial letter of watermarks in pictures

But bulkWaterMark can do a lot more for you than just inserting the same static text in every picture again and again. Of course, you probably have already seen our tutorials on the blog about inserting dynamic text in photos. Until now we have just given you hints for using image metadata in watermarks. Today I would like to show you how to get completely external data depending on the photo you are going to watermark into your protected pictures. But what do I mean with external data? I’m thinking of an Excel Workbook as data source, similar to the serial letter feature of Microsoft Word that takes names and addresses from an Excel spreadsheet.

You all know the serial letter feature of Word. Dynamic We are just doing the same in bulkWaterMark – instead of inserting the dynamic data into a letter, we are putting the data directly into a photo you are going to watermark.

Excel!? I want to add dynamic text into pictures!

But Excel is a spreadsheet program!? Why should I use a Microsoft Office app for putting data into watermarks?

It simple: By using bulkWaterMark Data Expressions, you can read from Excel Workbooks, search for a particular row and return any cell data of that found row that can be used as text for your watermark. Because most people like and understand Excel, we thought that it is a nice idea to use Excel as primary data source for so-called text data watermarks. As an alternative, you can also use CSV files if you want a more open source data format.

Examples of use

Many of our customers are already using this features. The most typical use case for is explained in our sample below, but I would like to give so some really creative examples of use, that we would not have thought of when developing the features.

Cardealers are using Data Expressions for creating photos for used car exchange websites. Pictures of offered cars are getting watermarked not only with the car dealer’s logo, but also with specific car data like its price or mileage.

Another customer has implemented a watermark template that generates a personal deck of Happy Family cards out of a bunch of images. This was achieved by using the image border layer of bulkWaterMark. The bottom of the border contains the occupation types that are fetched from an Excel spreadsheet.

Add text to pictures from Excel cells

Assume that we have thousands of pictures and a likewise long list of data that includes annotations for each photo. In our tutorial sample I have a list of filenames that contains the name of  the photographer and the copyright holder of each photo. This is how it looks like:

Image Copyright
Our “database” of copyright holders

The first column contains the name of each file we want to watermark, called “Filename”. We need this column to identify which image belongs to each row of data. The next column are the data columns and contain the name of the photographer (“Photographer”) and the name of the copyright owner (“Copyright”).

Our database and structure is defined – time to launch bulkWaterMark and create a new watermark template in the Profile Editor. First we are adding a new text layer and switch to the Expression Editor:

Insert Text Watermark and open the Expression Editor
Insert Text Watermark and open the Expression Editor

Now it’s getting interesting: We have to choose our data source now, which is the previously introduced Excel worksheet. Therefore, click onto the gear icon next to the Data Expression Context to configure the data source.

Setting a data source for our watermark
Setting a data source for our watermark

After defining a file (you can either use an Excel or CSV file), bulkWaterMark loads a raw preview of the data and displays the available sheets (Excel only) and columns within the data file. Our sample Excel file consists of a single sheet called “ImageCopyrights” that includes the previously shown data the we want to stamp onto our pictures. Afterwards I’m confirming my data source settings and we are nearly finished with our tutorial.

The only thing missing is the Expression that defines how the text of the watermark is getting composed. Therefore we are typing the following Expression into the Editor:

Our dynamic Data Expression for getting the Excel data into the watermark
Our dynamic Data Expression for getting the Excel data into the watermark

As keen bulkWaterMark tutorial (and online help!) reader you likely know what this text above means. But for the sake of completeness I would like to explain what those brackets and cryptic names are for:

The text till “Photo by ” is not an Expression – this will be written into the watermark directly. The interesting part starts when the first bracket “{” sets in and the magic begins: The function “GetColumnValue” of the “Data” context gets called. This function requires to parameters:

The first parameter (green) expects the name of the column that contains the image filename. This column gets searched for the the filename and is column A called “Filename” in our Excel file.

The second parameter (blue) is the value, that we are searching within column “Filename”. We are putting another, nested Expression result as parameter: {OriginalImage.FileName} returns the filename of the current image we are going to watermark.

Parameter #3 (orange) defines the column we want to actually use in our text watermark. The data for the text watermark should be taken from column C “Photographer”.

This is how it looks like in bulkWaterMark:

Add text to pictures from Excel
Composing the data with the Data Expression

And this is how a watermarked photo looks like:

Add text to pictures from Excel sample
Done! Mission “add dynamic data into pictures” accomplished

In our new Expression Editor you can also set test parameters for testing dynamic expressions easily by switching to the tab “Test Parameter” and adding a few image files. You can now select any of the added test images by a single click and depending on the selection, bulkWaterMark sets the property {OriginalImage.FileName} to the chosen image filename.

Testing the Data Expression with a test image
Testing the Data Expression with a test image

Conclusion

The new Expression Editor and Data Expression feature improve the handling of dynamic text watermarks a lot. Data Expressions might look complicated on the first sight, but after a few tries you will quickly batch watermark thousands of photos with external and individual data in no time. I think the effort already pays off when you want to watermark a few dozens of images with dynamic data.

If you need help with integrating your Excel or CSV data in your watermark profile do not hesitate to contact our support or just post above this article. Our presented example is included in our samples library that comes with a fresh bulkWaterMark installation.

Cheers,

Phil